Hello,

We are trying to run a report based on software metering, but seem to be having issues achieving the results we need with the wizard. We would like to have the report show how many times the metered software was launched, last launch, hours used, and the version for each machine within the last 7 days. Is such a report possible? If so, how would I go about creating such a report?

Any help would be greatly appreciated. Thanks.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

Here's the SQL code that I was able to come up with. The wizard doesn't reveal the fields that you need in order to bring this all together, unfortunately. 

 SELECT COUNT(SMD.ID) as "Launches", 
SUM(SECONDS_USED)/360 as "Time Used (hours)", 
MAX(END) as "Last Used",
SVTS.NAME,
VERSION,
MACHINE.NAME as "Computer",
GROUP_CONCAT(DISTINCT(USER_DATA)) AS "Users"

FROM ORG1.SAM_METER_DATA SMD
JOIN MACHINE on SMD.MACHINE_ID = MACHINE.ID
JOIN SAM_VIEW_TITLED_SOFTWARE SVTS on SMD.TITLED_APPLICATION_ID = SVTS.ID
WHERE SVTS.NAME like "%Acrobat%Professional%"
GROUP BY TITLED_APPLICATION_ID, MACHINE_ID

ORDER BY Launches DESC

Note that I am limiting this to Acrobat Professional only. If you want a different title then change the WHERE SVTS.NAME like "%Acrobat%Professional%" line to the appropriate title's name (for Acrobat Pro the version is between Acrobat and Professional, e.g. Acrobat X Professional, Acrobat XI Professional, so there is a wildcard in the middle of the name, other titles like Photoshop would just need to have the wildcards at the beginning and end).

To not filter this by any title, just remove the entire WHERE line.

Answered 12/11/2013 by: chucksteel
Red Belt

  • For this report does it return total hours used since metering was enabled? also, using FlySpeed to access the Kbox DBs, I dont see a SMD table or a SAM_VIEW_TITLED_SOFTWARE table.

    I'm trying to better understand what data your accessing to get this information. The report generated by this appears to be what I need. This is the closest answer that I have found to my question of "How do I generate a report that shows how much metered software has been used"

    Using the KBOX generated reports seems to only give me the usage for the last seven days.
    I need more data than the last seven days for any business case I present to my manager.
    • SMD is ORG1.SAM_METER_DATA. SAM_VIEW_TITLED_SOFTWARE is a view not a table, in MySQLWorkBench they are listed separately, maybe FlySpeed does the same?

      Yes, this is pulling for the entire database. If you wanted to limit by date you could add the following to the WHERE clause:
      END > DATE_SUB(now(), INTERVAL 1 MONTH)

      That would give you the past month, you can adjust the interval for the report length that you need.
      • Thanks again for this. I'm still using this as a reference.
Please log in to comment
1

That worked perfectly. Thank you for your help sir!

Answered 12/11/2013 by: pregiec
Senior Purple Belt

Please log in to comment
0
Hello,

We upgraded our K1000 to 6.0 not too long ago. With the new update I noticed some of the reports have stopped working. For instance, I had the following metering report set up for Microsoft Office:

SELECT COUNT(SMD.ID) as "Launches", 
SUM(SECONDS_USED)/360 as "Time Used (hours)", 
MAX(END) as "Last Used",
SVTS.NAME,
VERSION,
MACHINE.NAME as "Computer",
GROUP_CONCAT(DISTINCT(USER_DATA)) AS "Users"

FROM ORG1.SAM_METER_DATA SMD
JOIN MACHINE on SMD.MACHINE_ID = MACHINE.ID
JOIN SAM_VIEW_TITLED_SOFTWARE SVTS on SMD.TITLED_APPLICATION_ID = SVTS.ID
WHERE SVTS.NAME like "%office%"
GROUP BY TITLED_APPLICATION_ID, MACHINE_ID

ORDER BY Launches DESC

After the update this gives me a blank report. I noticed If i replaced "office" with Excel or Outlook then the report would run properly. It seems that there is an issue searching for Office. Anyone else experience this or possibly know a work around?

Answered 06/24/2014 by: pregiec
Senior Purple Belt

  • I don't think you can meter a suite of applications like that since metering is based on individual titles.
    • I suppose that does make sense, but it seemed to work before. What exactly is the script searching for with the SVTS.NAME? Is it searching for a process or an item from the software tab?
      • It looks at the SAM_VIEW_TITLED_SOFTWARE table which matches up with the Software Catalog.
Please log in to comment
Answer this question or Comment on this question for clarity