/build/static/layout/Breadcrumb_cap_w.png
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
0 Comments   [ + ] Show comments

Comments


All Answers

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)?

Answered 12/08/2016 by: chucksteel
Red Belt

  • 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.
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.
Answered 12/20/2016 by: JasonEgg
Red Belt