/build/static/layout/Breadcrumb_cap_w.png

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:

z8BImI.png

and

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
  • Do you still want to report on a specific software title? - chucksteel 7 years ago
  • Yes, I was looking for specific software title. I have came up with the report below, however the Last_Launch date that I put is in correct, I think the Last_Launch should correlate with the last Start Time where Last_Entry is 1. How would I do this?

    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

Answers (1)

Posted by: tuyen 7 years ago
Purple Belt
0
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:
  • If I run this query in my environment, I get different dates for different programs (e.g. Adobe 11 and IE11) - JasonEgg 7 years ago
    • Actually, what I put as Last_Launch is not correct. That is just the server_date data which I think is the last time the metering data was taken. - tuyen 7 years ago
 
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