1. PDQ Deploy & Inventory Help Center
  2. Community
  3. PDQ Inventory
 

Important Notice: On February 29th, this community was put into read-only mode. All existing posts will remain but customers are unable to add new posts or comment on existing. Please feel to join our Community Discord for any questions and discussions.

Answered

Inventory number (Environment Variable) doesn't show

Avatar
martin.johne

Hi,

on all our computer is the inventory number set in the enviroment variable. The syntax for the inventory number is like "06/22222"

I try to create a report about sql but is still empty.

    select 
    Computers.Name as "Computer Name",
	Computers.Description, EnvironmentVariables.Value as "Inventarnummer" from Computers,EnvironmentVariables
    where computers.computerid = environmentvariables.computerid and EnvironmentVariables.Name like "Inventarnummer"

I think the Report has problem with the character "/".

I try also an other enviroments. This stilll works.

can you confirm this?

Kind Regards Martin

0

Comments

4 comments
Date Votes
  • Avatar
    Christian Bacher

    Hi Martin,

    have you tried a simple report instead of a SQL report to get your result?

    It's a very simple report, to simple for complex SQL strings 😉

    ~Chris

    0
  • Avatar
    martin.johne

    Thanks 😃 This works, but that was the first step. My problem is we have 3 Environment Variables. Current we get the result rows like:

    Computername 1  -   Environment Variables 1
Computername 1  -   Environment Variables 2
Computername 1  -   Environment Variables 3
Computername 2  -   Environment Variables 1
Computername 2  -   Environment Variables 2
Computername 2  -   Environment Variables 3

    but i need a tabel form

    Computername 1 - Environment Variables 1 - Environment Variables 2 -  Environment Variables 3
Computername 2 - Environment Variables 1 - Environment Variables 2 -  Environment Variables 3
Computername 3 - Environment Variables 1 - Environment Variables 2 -  Environment Variables 3

    my idea was a inner join.

    Or is there a trick for this problem.

    Kind Regards Martin

    0
  • Avatar
    Colby Bouma

    I think you'll have to do a pivot like this:

    SELECT
      Computers.Name AS "Computer Name"
    , Computers.Description
    -- https://stackoverflow.com/a/3611606
    , MAX(CASE WHEN EnvironmentVariables.Name = 'Inventarnummer1' THEN EnvironmentVariables.Value END) AS "Inventarnummer 1"
    , MAX(CASE WHEN EnvironmentVariables.Name = 'Inventarnummer2' THEN EnvironmentVariables.Value END) AS "Inventarnummer 2"
    , MAX(CASE WHEN EnvironmentVariables.Name = 'Inventarnummer3' THEN EnvironmentVariables.Value END) AS "Inventarnummer 3"
FROM
    Computers
INNER JOIN
    EnvironmentVariables USING (ComputerId)
WHERE
    <ComputerFilter>
GROUP BY Computers.Name
    0
  • Avatar
    martin.johne

    Jeah. Many Thanks. Looks awesome.

    0