K1000 Reporting: Software Metering
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.
-
So if i am looking for all installs of OneNote and last used on all my systems this sql will work? - anthonytji 7 years ago
-
Yes, if you have metering enabled on OneNote and change the query to look for OneNote instead of Acrobat%Professional it should report on the usage. - chucksteel 7 years ago
-
ok what about versions of onenote that are preinstalled with windows10 is there away to view those being used as well? - anthonytji 7 years ago
-
That version of OneNote isn't cataloged by default so you will need to add it to your software catalog first. - chucksteel 7 years ago
Answers (3)
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.
Comments:
-
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. - anonymous_95342 10 years ago-
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. - chucksteel 10 years ago-
Thanks again for this. I'm still using this as a reference. - anonymous_95342 10 years ago
Comments:
-
I don't think you can meter a suite of applications like that since metering is based on individual titles. - chucksteel 10 years ago
-
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? - pregiec 10 years ago
-
It looks at the SAM_VIEW_TITLED_SOFTWARE table which matches up with the Software Catalog. - chucksteel 10 years ago