/build/static/layout/Breadcrumb_cap_w.png

Custom report on software metering

So I have this report in Kace that I created using the wizard works ok. It shows the workstation, metered software, hours used, and launches.  But the problem is if the software, say "visio" as an example is installed on five workstations, but only used on two workstations it will report those two.  I would like this report to show all the workstations its installed on and the usage, regardless if used or not.  
Here is the wizard converted to SQL:
SELECT M.NAME AS MACHINE_NAME, ROUND(SUM(SAM_METER_DATA.SECONDS_USED) / 3600.0, 3) AS HOURS_USED, SUM(SAM_METER_DATA.LAUNCHES) AS LAUNCHES, IFNULL(SC_SUITES.NAME, SC_APPS.NAME) AS SOFTWARE_NAME, SAM_METER_DATA.VERSION  FROM SAM_METER_DATA  JOIN CATALOG.SAM_CATALOG SC_APPS ON SAM_METER_DATA.TITLED_APPLICATION_ID = SC_APPS.ID JOIN SAM_MACHINE_JT SMJ_APPS ON SMJ_APPS.SAM_CATALOG_ID = SC_APPS.ID AND SAM_METER_DATA.MACHINE_ID = SMJ_APPS.MACHINE_ID JOIN MACHINE M ON M.ID = SAM_METER_DATA.MACHINE_ID LEFT JOIN CATALOG.SAM_CATALOG SC_SUITES ON SC_SUITES.ID = SMJ_APPS.SUITE_ID WHERE (SC_APPS.SAM_TYPE IN ('TITLED_APPLICATION', 'TITLED_SUITE') AND (SC_APPS.SOFTWARE_CATEGORY_ID <> 32)) AND ((IFNULL(SC_SUITES.NAME, SC_APPS.NAME) like '%visio%'))  GROUP BY SAM_METER_DATA.MACHINE_ID , IFNULL(SC_SUITES.ID, SC_APPS.ID) ORDER BY MACHINE_NAME

I'm not sure if its the ifnull statements that is filtering as I'm not an SQL person

0 Comments   [ + ] Show comments

Answers (2)

Posted by: chucksteel 7 years ago
Red Belt
1
Since this report is based on the SAM_METER_DATA table it will only contain records for machines that have entries in that table. For your report you need to start with the MACHINE table, join to the software table to find the machines with the title in question, then join to the metering tables to get usage. The wizard probably won't be able to create something like that but it should be possible. Do you want a report that shows usage for all time or a specific period (i.e. last month)?


Comments:
  • two fold, we want it to show all workstations that have visio, and to also show what workstations have used visio in the last x number of days. I'm not an SQL guru, trying this in toad is making my hair turn grey quicker than it should. - erietech 7 years ago
Posted by: JasonEgg 7 years ago
Red Belt
1
Here is a query for the computers that have used the program 'MY_PROG' in the last X days:
SELECT M.ID, M.NAME, MAX(MD.SERVER_DATE) AS 'Last Used'
FROM MACHINE    M
     JOIN SAM_METER_DATA MD ON MD.MACHINE_ID = M.OS_ID
     JOIN CATALOG.SAM_CATALOG SC ON SC.ID = MD.TITLED_APPLICATION_ID
WHERE     SC.NAME LIKE '%MY_PROG%'
      AND MD.SERVER_DATE > DATE_SUB(NOW(), INTERVAL X DAY)
GROUP BY M.ID
I would suggest using '%Microsoft Visio 2%' instead of '%visio%' in case there is Visio Viewer data in metering.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ