Report software usage - per day basis
Hi guys, I'm not well skilled in writing complex SQL queries so I could use some help with writing custom report that will show me usage of my software on daily basis and per each user or machine...
I've searched here for many reports and tried to modify them but with no success... :/
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
9 years ago
Here's a report that I think is close to what you're looking for:
SELECT count(SMD.ID), date(START), SMD.USER_DATA
FROM SAM_METER_DATA SMD
JOIN SAM_VIEW_TITLED_SOFTWARE SVTS on SMD.TITLED_APPLICATION_ID = SVTS.ID
WHERE SVTS.NAME like "%SPSS%"
GROUP BY SMD.USER_DATA, date(START)
ORDER BY date(START)
Change the %SPSS% to match a software title that you're interested in.
(Edited to clean up line breaks)
Comments:
-
Thank you.
Can you add how many hours that certain app was used per day? - dlistar 9 years ago-
KACE records it as SECONDS_USED so you can add the following to the SELECT statement:
, SUM(SECONDS_USED)/3600 as HOURS_USED
You need to add that after SMD.USER_DATA and before FROM - chucksteel 9 years ago-
Also, I've seen strange information in your report. The thing is, I've enabled metering 2014-03-17 and on that date was K1000 first time powered on. BUT, first date record for metering when I ran your report is 2014-02-16... How is that possible???? - dlistar 9 years ago
-
That is interesting. Do you have a tool like MySQL Workbench that you can look at the SAM_METER_DATA table and see what's in the table? - chucksteel 9 years ago
-
yes, the same record is in that table in Workbench...
Can you add name of wanted application and see "per day" (let say for yesterday) how much I was using excel 2013 ? - dlistar 9 years ago-
That's interesting. I'm not sure how you would have data from before the appliance was powered on, unless it was tested at the factory, maybe. - chucksteel 9 years ago
-
I'm confused too... :/
Please can you answer my last question above, thank you. - dlistar 9 years ago
-
Here's and updated query that includes the application name and the computer it was launched on:
SELECT SVTS.NAME, count(SMD.ID), date(START),
SMD.USER_DATA, MACHINE.NAME as "Computer",
SUM(SECONDS_USED)/3600 as HOURS_USED
FROM SAM_METER_DATA SMD
JOIN SAM_VIEW_TITLED_SOFTWARE SVTS on
SMD.TITLED_APPLICATION_ID = SVTS.ID
JOIN MACHINE on SMD.MACHINE_ID = MACHINE.ID
WHERE SVTS.NAME like "%SPSS%"
GROUP BY SVTS.NAME, SMD.USER_DATA, date(START)
ORDER BY date(START) - chucksteel 9 years ago-
Chuck this is great! - Jbr32 9 years ago
-
Thank you so much, you are great.
I was wondering, can you add clause that will take let say one month interval and then add average usages per day and average duration in minutes per day...? Is that possible? - dlistar 9 years ago -
This is the closest I can get. I'm having trouble adding average number of launches per day.
SELECT SVTS.NAME, date(START), count(SMD.ID) as "Launches", SUM(SECONDS_USED)/60 as "Total Usage", ROUND(AVG(SECONDS_USED)/60, 2) as "Average Minutes"
FROM SAM_METER_DATA SMD
JOIN SAM_VIEW_TITLED_SOFTWARE SVTS on SMD.TITLED_APPLICATION_ID = SVTS.ID
JOIN MACHINE on SMD.MACHINE_ID = MACHINE.ID
WHERE SVTS.NAME like "%SPSS%"
and MONTH(START) = 11
and YEAR(START) = 2013
GROUP BY DATE(START)
ORDER BY DATE(START) - chucksteel 9 years ago
-
Thank you Chuck,this is also a big help. if you eventually find solution please post it... I'm very grateful. - dlistar 9 years ago
