K1000 Metered Data for all computers
Hi,
I am running a SQL report, not written by me, to get a list of usage for our metered data but it is including computers that used to have the software installed since the usage is still within the set interval time. I was wondering if there was a way to filter out the computers that do not have it installed. I am not very good with SQL so any help is greatly appreciated.
SQL CODE:
SELECT COUNT(SMD.ID) as "Launches",
SUM(SECONDS_USED)/3600 as "Time",
MACHINE.NAME as "Computer",
MAX(END) as "Last Used",
SVTS.NAME,
VERSION,
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 "%Photoshop CC%" and END > DATE_SUB(now(), INTERVAL 3 MONTH)
GROUP BY TITLED_APPLICATION_ID, MACHINE_ID
ORDER BY Time DESC
1 Comment
[ + ] Show comment
Answers (1)
Please log in to answer
Posted by:
Eray
9 years ago
Hey again, I modified your code to include a join to the Label and MLJT tables. What you'll want to do is make a machine smart label that looks for Software titles and have it filter to CC or whatever program you want. Just change out the label name and program name in this code for whatever you need. I would post a pic of my CC machine label but I haven't made one yet. I'll throw it in the comments once I get it created.
Edit: Had a mistake in the code, corrected it.
SELECT COUNT(SMD.ID) as "Launches",
SUM(SECONDS_USED)/3600 as "Time",
M.NAME as "Computer",
M.USER_FULLNAME AS "Users",
MAX(END) as "Last Used",
SVTS.NAME,
VERSION
FROM ORG1.SAM_METER_DATA SMD
JOIN MACHINE M on SMD.MACHINE_ID = M.ID
JOIN MACHINE_LABEL_JT MLJT on M.ID = MLJT.MACHINE_ID
JOIN LABEL L on L.ID = MLJT.LABEL_ID
JOIN SAM_VIEW_TITLED_SOFTWARE SVTS on SMD.TITLED_APPLICATION_ID = SVTS.ID
WHERE SVTS.NAME like '%Creo%' and L.NAME like '%Machine-Metered-Creo%'
GROUP BY TITLED_APPLICATION_ID, M.ID
ORDER BY NAME
Comments:
-
Hi Thanks for the response. Do you know if there is a way to add an installed date field to the report so we can find out when the software was installed? This is more so for ones that were installed before the smart label would have been applied. - CBT IT 9 years ago
I have just setup metering for our CC products here so I've been working on getting my reports to run just the way you are.
For my environment I have a machine smart label that plugs itself to any machine with it installed. I imagine my solution will be adding a "where" clause to the sql statement to filter any machines that aren't part of that label. I too am not very good with sql but I'll be working on that next week off and on. I'll post something once I get a solution. - Eray 9 years ago