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)
Please log in to answer
Posted by:
chucksteel
8 years ago
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 8 years ago
Posted by:
JasonEgg
8 years ago
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.
