Answered

Inventory number (Environment Variable) doesn't show

Martin Johne

June 04, 2018 06:26

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

Comments

4 comments

Christian Bacher
June 04, 2018 18:13

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

Martin Johne

June 05, 2018 06:15

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

Colby Bouma

June 13, 2018 16:56

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

Martin Johne
June 18, 2018 06:41

Jeah. Many Thanks. Looks awesome.

0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post