Custom report on software metering
12/07/2016 1809 views
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