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... :/

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

Answers

3

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)

Answered 06/03/2014 by: chucksteel
Red Belt

  • Thank you.
    Can you add how many hours that certain app was used per day?
    • 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
      • 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????
      • This content is currently hidden from public view.
        Reason: Removed by member request
        For more information, visit our FAQ's.
      • 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?
  • 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 ?
    • 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.
      • I'm confused too... :/
        Please can you answer my last question above, thank you.
    • 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)
      • Chuck this is great!
      • 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?
      • 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)
  • Thank you Chuck,this is also a big help. if you eventually find solution please post it... I'm very grateful.
Please log in to comment
Answer this question or Comment on this question for clarity