/build/static/layout/Breadcrumb_cap_w.png
11/20/2018 153 views

I am trying to create a software metering report that will show me the unique active users of all of our versions of Visual Studio, excluding server operating systems. This is proving difficult as we have a lot of people that have multiple different versions installed. I can use a device report to narrow down machines that have something Visual Studio installed, but that does not give me any of the metering data. And a metering report will not give me usage data regardless of version. I am guessing this is going to need to be done with some custom SQL but I am at a loss on where to start with that. Does anyone have any insight they could provide to help me figure this out?

Answer Summary:
2 Comments   [ + ] Show comments

Comments

  • Do you have metering enabled for all versions of Visual Studio software in the Software Catalog?
  • Yep. Every entry I could find.

Answer Chosen by the Author

1

We don't meter Visual Studio, but here is my report for Acrobat Professional:

SELECT COUNT(SMD.ID) as 'Launches', SUM(SECONDS_USED)/3600 as 'Time Used (hours)', 
MAX(END) as 'Last Used',
SVTS.NAME,
VERSION,
MACHINE.NAME as 'Computer', MACHINE.OS_NAME,
GROUP_CONCAT(DISTINCT(USER_DATA)) AS 'Users'
FROM ORG1.SAM_METER_DATA SMD
JOIN MACHINE on SMD.MACHINE_ID = MACHINE.ID
JOIN SAM_VIEW_TITLED_SOFTWARE SVTS on SMD.TITLED_APPLICATION_ID = SVTS.ID
WHERE SVTS.NAME like "%Acrobat%Professional%"
AND END > DATE_SUB(now(), INTERVAL 1 MONTH)
GROUP BY TITLED_APPLICATION_ID, MACHINE_ID
ORDER BY Launches DESC

This is limited to the last month.

Answered 11/26/2018 by: chucksteel
Red Belt

  • That is pretty close to what I need. I can modify the application names to do what I need. Thanks. Once last question, Is it possible to modify the interval to report on any system that has not launched that in greater than a month?
    • The interval can be changed, yes. This line:
      AND END > DATE_SUB(now(), INTERVAL 1 MONTH)
      sets the interval, you can change the 1 MONTH to another value. The designation for the kind of interval is always singular and upper case, e.g. 2 MONTH, 45 DAY, 1 YEAR, etc.

      Your question says "not launched" in greater than a month. If you would like a report of machines that have the software installed but hasn't been launched, that would be a different query.
      • I could see that being useful. If you have the time to throw up an example.
      • I don't have a report for that. We use the licensing module to track licenses and it shows you the machines that haven't launched the software in the past 90 days. Even if you don't include the actual license information for a piece of software, you can add a "license" that is associated with the software catalog to get the information you need.
      • No problem. I can get at that information and I do not think management will really want that data very often anyway. Thanks again for all of the help.