K1000 Metered Data for all computers
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.
SELECT COUNT(SMD.ID) as "Launches",
SUM(SECONDS_USED)/3600 as "Time",
MACHINE.NAME as "Computer",
MAX(END) as "Last Used",
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