How do I create a Software Metering report that includes the Last Launched Date and other data like the report seen in the details of the software in Software Catalog?
In the Software Catalog, when you go into the details of a software and look at the metered data, you can get the following data:
and
How can I create a report to get the same data? I tried using the wizard to create a report using the topic "Software Catalog - Metering by Machines" and I am only able to get the hours used and launches, but I cannot get the last launched time like above. Does anyone know how I can create this report?
2 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
tuyen
7 years ago
I have found that there is a SERVER_DATE which shows the launch times of the applications.
I have the following SQL query:
SELECT M.NAME AS MACHINE_NAME, ROUND(SUM(SAM_METER_DATA.SECONDS_USED) / 3600.0, 3) AS HOURS_USED, SUM(SAM_METER_DATA.LAUNCHES) AS LAUNCHES, IFNULL(SC_SUITES.NAME, SC_APPS.NAME) AS SOFTWARE_NAME, SAM_METER_DATA.VERSION, MAX(SAM_METER_DATA.SERVER_DATE) as Last_Launch
FROM SAM_METER_DATA
JOIN CATALOG.SAM_CATALOG SC_APPS ON SAM_METER_DATA.TITLED_APPLICATION_ID = SC_APPS.ID JOIN SAM_MACHINE_JT SMJ_APPS ON SMJ_APPS.SAM_CATALOG_ID = SC_APPS.ID AND SAM_METER_DATA.MACHINE_ID = SMJ_APPS.MACHINE_ID
JOIN MACHINE M ON M.ID = SAM_METER_DATA.MACHINE_ID
LEFT JOIN CATALOG.SAM_CATALOG SC_SUITES ON SC_SUITES.ID = SMJ_APPS.SUITE_ID WHERE (SC_APPS.SAM_TYPE IN ('TITLED_APPLICATION', 'TITLED_SUITE') AND (SC_APPS.SOFTWARE_CATEGORY_ID <> 32))
GROUP BY SAM_METER_DATA.MACHINE_ID , IFNULL(SC_SUITES.ID, SC_APPS.ID)
ORDER BY MACHINE_NAME
However, in using MAX(SAM_METER_DATA.SERVER_DATE) as Last_Launch, it is getting the last launch of all metered software. How do I target it so that this Last_Launch is the correct last launch time of the specified application?
Comments:
This is currently what I have:
SELECT M.NAME AS MACHINE_NAME, IFNULL(SC_SUITES.NAME, SC_APPS.NAME) AS SOFTWARE_NAME,
SAM_METER_DATA.VERSION,
SAM_METER_DATA.START,
SAM_METER_DATA.END,
SAM_METER_DATA.USER_DATA,
ROUND(SAM_METER_DATA.SECONDS_USED / 3600.0, 3) AS HOURS_USED,
SAM_METER_DATA.LATEST_ENTRY
FROM
SAM_METER_DATA
JOIN CATALOG.SAM_CATALOG SC_APPS ON SAM_METER_DATA.TITLED_APPLICATION_ID = SC_APPS.ID JOIN SAM_MACHINE_JT SMJ_APPS ON SMJ_APPS.SAM_CATALOG_ID = SC_APPS.ID AND SAM_METER_DATA.MACHINE_ID = SMJ_APPS.MACHINE_ID
JOIN MACHINE M ON M.ID = SAM_METER_DATA.MACHINE_ID
LEFT JOIN CATALOG.SAM_CATALOG SC_SUITES ON SC_SUITES.ID = SMJ_APPS.SUITE_ID WHERE (SC_APPS.SAM_TYPE IN ('TITLED_APPLICATION', 'TITLED_SUITE') AND (SC_APPS.SOFTWARE_CATEGORY_ID <> 32) AND (SC_APPS.NAME LIKE '%Visual Studio%' OR SC_SUITES.NAME LIKE '%Visual Studio%'))
ORDER BY M.NAME, SOFTWARE_NAME, SAM_METER_DATA.START - tuyen 7 years ago