Inventory number (Environment Variable) doesn't show

Answered

Follow

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

0

• 

  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

  Comment actions Permalink
• 

  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

  0

  Comment actions Permalink
• 

  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
  

  0

  Comment actions Permalink
• 

  Martin Johne

  • June 18, 2018 06:41

  Jeah. Many Thanks. Looks awesome.

  0

  Comment actions Permalink

Please sign in to leave a comment.