/build/static/layout/Breadcrumb_cap_w.png

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

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
Posted 10 years ago 9068 views

Comments

  • Would you kindly add examples with filters for software publishers, suites, and individual applications? - SCIIT 10 years ago
  • Hopefully these will help - I already specified values for the filters, so just adjust these as needed.

    John
    __________________________________

    Metering Per Machine
    * with Suite & Publisher, and filters for:
    Application (SVTS.NAME)
    Suite (SVMDS_SUITES.NAME)
    and Publisher (SVMDS_SUITES.PUBLISHER)

    SELECT M.NAME AS MACHINE,
    SVTS.NAME AS PRODUCT_NAME,
    SAM_METER_DATA.VERSION,
    SVMDS_SUITES.NAME AS SUITE,
    SVMDS_SUITES.PUBLISHER,
    (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
    WHERE SVTS.NAME = 'Excel 2010'
    AND SVMDS_SUITES.NAME = 'Microsoft Office 2010 Professional Plus'
    AND SVMDS_SUITES.PUBLISHER = 'Microsoft Corporation'
    GROUP BY SAM_METER_DATA.TITLED_APPLICATION_ID,SAM_METER_DATA.MACHINE_ID
    ORDER BY M.NAME, PRODUCT_NAME
    __________________________________

    Metering Per Application
    * with Suite & Publisher, and filters for:
    Application (SVTS.NAME)
    Suite (SVMDS_SUITES.NAME)
    and Publisher (SVMDS_SUITES.PUBLISHER)

    SELECT SVTS.NAME AS PRODUCT_NAME,
    SAM_METER_DATA.VERSION,
    SVMDS_SUITES.NAME AS SUITE,
    SVMDS_SUITES.PUBLISHER,
    (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
    WHERE SVTS.NAME = 'Excel 2010'
    AND SVMDS_SUITES.NAME = 'Microsoft Office 2010 Professional Plus'
    AND SVMDS_SUITES.PUBLISHER = 'Microsoft Corporation'
    GROUP BY SAM_METER_DATA.TITLED_APPLICATION_ID
    ORDER BY PRODUCT_NAME - jverbosk 10 years ago
    • Thank you! I think this will do the trick. You're a lifesaver as always! - SCIIT 10 years ago
  • Hi, i tried to twist the the metering per application sql line above to list only microsoft project but it generate nothing. Do you mind to share what could be wrong with it? i added/modified the line to this WHERE SVTS.NAME = 'Project'
    AND SVMDS_SUITES.PUBLISHER = 'Microsoft Corporation'. i didnt include AND SVMDS_SUITES.NAME as i want it to show all project including project 95,98, 2003, 2007 and 2010.

    Full line as below:

    SELECT M.NAME AS MACHINE,
    SVTS.NAME AS PRODUCT_NAME,
    SAM_METER_DATA.VERSION,
    SVMDS_SUITES.NAME AS SUITE,
    SVMDS_SUITES.PUBLISHER,
    (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
    WHERE SVTS.NAME = 'Project'
    AND SVMDS_SUITES.PUBLISHER = 'Microsoft Corporation'
    GROUP BY SAM_METER_DATA.TITLED_APPLICATION_ID,SAM_METER_DATA.MACHINE_ID
    ORDER BY M.NAME, PRODUCT_NAME

    Please shed some light to my issue. Thanks. - ainie aziz 9 years ago
  • Here's a report I used and generated and used on 6.2 for metering Data. It pulls the application name, but not the publisher. But it does pull in the last user logged in and the last time it was launched.


    SELECT
    M.NAME,
    IFNULL(SVMDS_APPS.NAME, SVMDS_SUITES.NAME) AS PRODUCT_NAME,
    ROUND(SUM(SAM_METER_DATA.SECONDS_USED) / 3600.0,
    3) AS HOURS_USED,
    SUM(SAM_METER_DATA.LAUNCHES) AS LAUNCHES,
    MAX(DATE(SAM_METER_DATA.START)) AS LAST_LAUNCHED_DATE,
    MAX(TIME(SAM_METER_DATA.START)) AS LAST_LAUNCHED_TIME,
    M.USER_LOGGED
    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
    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
    JOIN
    MACHINE M ON M.ID = SVMDS_APPS.MACHINE_ID
    OR M.ID = SVMDS_SUITES.MACHINE_ID
    WHERE
    ((IFNULL(SVMDS_APPS.NAME, SVMDS_SUITES.NAME) LIKE '%Reader%'))
    GROUP BY SAM_METER_DATA.MACHINE_ID , IFNULL(SVMDS_APPS.ID, SVMDS_SUITES.ID)
    ORDER BY M.NAME , IFNULL(SVMDS_APPS.NAME, SVMDS_SUITES.NAME) - Dmoore 8 years ago
This post is locked
 
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