K1000 Reports - Reports to List Metering Information for Standalone Applications and Applications within a Suite

Typical disclaimer:  I *really* need to stress that everything below is based on my own research and findings - the information below does not indicate official documentation or support, however some of this may be accurate.

The SQL queries below will hopefully fill in some gaps in the (version 5.5) K1000's built-in metering reports, as well as the metering reports which are possible to create using the report wizard.  These queries are basically tweaks to the reports created by the report wizard.

_________________________________

The report wizard in version 5.5 of the K1000 allows for the creation of metering reports. However, there are limitations to each of the topics within the wizard:


Software Catalog - Metering By Applications

Reports created using this Report Topic list useful metering information for each metered application, but only include standalone applications. Applications within a suite (such as components of Microsoft Office - Excel, Word, etc.), do not appear in this report. Nor is usage broken down per machine - this report simply advises on the total usage (Hours Used, Launches) and the number of machines that were metered.


Software Catalog - Metering By Machines

Reports created using this Report Topic are more comprehensive in regards to including all metered application. However, applications within a suite are only indicated by a version number and are not explicitly identified by title.

_________________________________

SQL Queries

_________________________________

Metering Per Machine

Columns:
Machine Name
Product Name
Product Version
Install Count
Hours Used
Launches
Last Launched

SQL Query:

SELECT M.NAME AS MACHINE,

SVTS.NAME AS PRODUCT_NAME,

SAM_METER_DATA.VERSION,

(SELECT COUNT(DISTINCT MACHINE_ID)

 FROM SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE X

 WHERE X.ID = SVMDS_APPS.ID OR X.ID = SVMDS_SUITES.ID) AS INSTALL_COUNT,

ROUND(SUM(SAM_METER_DATA.SECONDS_USED) / 3600.0, 3) AS HOURS_USED,

SUM(SAM_METER_DATA.LAUNCHES) AS LAUNCHES,

MAX(SAM_METER_DATA.START) AS LAST_LAUNCHED

FROM SAM_METER_DATA

LEFT JOIN SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE SVMDS_APPS

  ON SAM_METER_DATA.TITLED_APPLICATION_ID = SVMDS_APPS.ID

  AND SAM_METER_DATA.MACHINE_ID = SVMDS_APPS.MACHINE_ID

  AND SAM_METER_DATA.VERSION = SVMDS_APPS.VERSION

LEFT JOIN CATALOG.TITLED_SUITE_APPLICATION_JT TSAJT

  ON TSAJT.TITLED_APPLICATION_ID = SAM_METER_DATA.TITLED_APPLICATION_ID

LEFT JOIN SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE SVMDS_SUITES

 ON SVMDS_SUITES.ID = TSAJT.TITLED_SUITE_ID

 AND SAM_METER_DATA.MACHINE_ID = SVMDS_SUITES.MACHINE_ID

LEFT JOIN SAM_VIEW_TITLED_SOFTWARE SVTS

 ON SAM_METER_DATA.TITLED_APPLICATION_ID = SVTS.ID

JOIN MACHINE M ON M.ID = SVMDS_APPS.MACHINE_ID OR M.ID = SVMDS_SUITES.MACHINE_ID

GROUP BY SAM_METER_DATA.TITLED_APPLICATION_ID,SAM_METER_DATA.MACHINE_ID

ORDER BY M.NAME, PRODUCT_NAME

_________________________________

Metering Per Application

Columns:
Product Name
Product Version
Install Count
Total Hours Used
Total Launches

SQL Query:

SELECT SVTS.NAME AS PRODUCT_NAME,

SAM_METER_DATA.VERSION,

(SELECT COUNT(DISTINCT MACHINE_ID) from SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE X

 WHERE X.ID = SVMDS_APPS.ID OR X.ID = SVMDS_SUITES.ID) AS INSTALL_COUNT,

ROUND(SUM(SAM_METER_DATA.SECONDS_USED) / 3600.0, 3) AS TOTAL_HOURS_USED,

SUM(SAM_METER_DATA.LAUNCHES) AS TOTAL_LAUNCHES

FROM SAM_METER_DATA 

LEFT JOIN SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE SVMDS_APPS

  ON SAM_METER_DATA.TITLED_APPLICATION_ID = SVMDS_APPS.ID

  AND SAM_METER_DATA.MACHINE_ID = SVMDS_APPS.MACHINE_ID

  AND SAM_METER_DATA.VERSION = SVMDS_APPS.VERSION

LEFT JOIN CATALOG.TITLED_SUITE_APPLICATION_JT TSAJT

  ON TSAJT.TITLED_APPLICATION_ID = SAM_METER_DATA.TITLED_APPLICATION_ID

LEFT JOIN SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE SVMDS_SUITES

  ON SVMDS_SUITES.ID = TSAJT.TITLED_SUITE_ID

  AND SAM_METER_DATA.MACHINE_ID = SVMDS_SUITES.MACHINE_ID

LEFT JOIN SAM_VIEW_TITLED_SOFTWARE SVTS

 ON SAM_METER_DATA.TITLED_APPLICATION_ID = SVTS.ID

JOIN MACHINE M ON M.ID = SVMDS_APPS.MACHINE_ID OR M.ID = SVMDS_SUITES.MACHINE_ID

GROUP BY SAM_METER_DATA.TITLED_APPLICATION_ID

ORDER BY PRODUCT_NAME