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?
Add your rating:
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:
![z8BImI.png](/uploads/images/130246/z8BImI.png)
![nzvDZf.png](/uploads/images/130246/nzvDZf.png)
![z8BImI.png](/uploads/images/130246/z8BImI.png)
and
![nzvDZf.png](/uploads/images/130246/nzvDZf.png)
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