I am trying to write/find a report to do the following:

Pull the metering data for specific software title from the last 3 months per agent. In this case, I am looking for AutoCAD Architecture 2014 usage (hours used, launches per user). The problem being, using the built-in wizard isn't returning anything but 4x users, which is now down to 2x users. I did find a report that is finally showing me everyone, but I am having problems getting it to only pull the last 3 months. Plus, it looks like this may not be the correct table to pull from?

I am not a SQL guru by any means. I have installed MySQL Workbench and have been "browsing" the database to see what I can see to help me out.

Here is what I have so far:
SELECT COUNT(SMD.ID) as "Launches",
ROUND(SUM(SECONDS_USED)/3600) as "Time Used (hours)",
MAX(END) as "Last Used",
SVTS.NAME,
VERSION,
MACHINE.NAME as "Computer",
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 "%Autocad Architecture 2014%" and

GROUP BY TITLED_APPLICATION_ID, MACHINE_ID

ORDER BY Launches DESC

The problem with this report (or tables? I am pulling from) shows the last date used was back in October. This is incorrect, as it is used everyday by majority of users.

2 Comments   [ + ] Show Comments

Comments

  • That report should be correct. When you look at the software catalog in KACE does it report the data correctly?
  • chucksteel,
    Looking at the software catalog, it does show the correct number of installs and all of the associated versions of the software. The Metering data numbers don't reflect correctly though, which is odd. Here is what I see:

    Software Catalog Metering:
    31-90 Day: 128
    Not Used: 3

    The Metering Report from above:
    Shows all of the installs with last reported metering date of 10/31/2014. It only shows version R19.1.108.0.0

    Software Catalog versions:
    AutoCAD Architecture 2014 - English SP 1 R19.1.108.0.0 2
    AutoCAD Architecture 2014 - English R19.1.108.0.0 2
    Autodesk AutoCAD Architecture 2014 - English R19.1.108.0.0 2
    AutoCAD Architecture 2014 - English 7.5.17.0 8
    Autodesk AutoCAD Architecture 2014 - English 7.5.17.0 8
    AutoCAD Architecture 2014 - English SP 1 7.5.17.0 8
    AutoCAD Architecture 2014 - English 7.5.106.0 121
    Autodesk AutoCAD Architecture 2014 - English 7.5.106.0 121
    AutoCAD Architecture 2014 - English SP 1 7.5.106.0 121

    As you can see, AutoCAD doesn't make it easy, with numerous versions for the same piece of software, depending on "how" you look it at. Why it doesn't report any usage data after 10/31/2014, I don't know. It is still being Metered.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity