Our company qualifies for a $15 per computer rebate from our electricity provider, PG&E, for using K1000 power management. In order to apply for the rebate we must submit a report which lists each PG&E account number and the number of computers that are in each account. We have created labels for each site with the naming convention of PGE_<site_name>_<PGE-account-number> (example: PGE_PA_56676543). There are around 20 labels.

 

We need to create a report that will list all of the labels that start with PGE and then list the total number of computers that are in those labels to the right of each label name. Any suggestions on how to create a SQL report to do this task would be greatly appreciated.

 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

Thank you for your response nshah. I’m afraid that a listing of all of the computers that qualify would be too much as it is over 5000 machines. At this point PG&E is requiring simply a total computer count for each of their accounts with us.

Answered 05/31/2012 by: jvincent
Orange Belt

Please log in to comment
1

TRy this..

 

SELECT GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,MACHINE.NAME AS SYSTEM_NAME FROM MACHINE  LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)  LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID  AND LABEL.TYPE <> 'hidden') WHERE (LABEL.NAME like 'PGE%')  GROUP BY MACHINE.ID ORDER BY LABEL_NAME_GROUPED,SYSTEM_NAME

Answered 05/31/2012 by: hmoore
Second Degree Blue Belt

Please log in to comment
1

Worked with Mark G. and Philip C. from KACE support. Philip nailed the SQL query. Awesome job you guys. Thanks!!

The SQL script is below:

 

Select
  DISTINCT LABEL.NAME AS 'Label Name', COUNT(*) AS 'Machine Count'
From
  LABEL Inner Join
  MACHINE_LABEL_JT On MACHINE_LABEL_JT.LABEL_ID = LABEL.ID Inner Join
  MACHINE On MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
Where
  LABEL.NAME like 'PGE%'
Group By
  LABEL.NAME

Answered 06/04/2012 by: jvincent
Orange Belt

Please log in to comment
0

You could do this with the Reporting Wizard if you just need a count. The only thing is the wizard will list all the machines under the label as well as give you the count. 

 

SELECT GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,MACHINE.NAME AS SYSTEM_NAME FROM MACHINE  LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)  LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID  AND LABEL.TYPE <> 'hidden') WHERE (LABEL.NAME like 'PGE%')  GROUP BY MACHINE.ID ORDER BY LABEL_NAME_GROUPED

 

 

Answered 05/30/2012 by: nshah
Red Belt

  • This is just bringing back the name of the machine and the label name.
Please log in to comment
Answer this question or Comment on this question for clarity