/build/static/layout/Breadcrumb_cap_w.png

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.


1 Comment   [ + ] Show comment
  • So if i am looking for all installs of OneNote and last used on all my systems this sql will work? - anthonytji 6 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 6 years ago
      • ok what about versions of onenote that are preinstalled with windows10 is there away to view those being used as well? - anthonytji 6 years ago
      • That version of OneNote isn't cataloged by default so you will need to add it to your software catalog first. - chucksteel 6 years ago

Answers (3)

Answer Summary:
Posted by: chucksteel 10 years ago
Red Belt
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.


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
Posted by: pregiec 10 years ago
Senior Purple Belt
1

That worked perfectly. Thank you for your help sir!

Posted by: pregiec 9 years ago
Senior Purple Belt
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?


Comments:
  • I don't think you can meter a suite of applications like that since metering is based on individual titles. - chucksteel 9 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 9 years ago
      • It looks at the SAM_VIEW_TITLED_SOFTWARE table which matches up with the Software Catalog. - chucksteel 9 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ