Finding Installed Date
Hi
I am trying to add an additional column to this report for install date:
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 "%Illustrator CC%" and END > DATE_SUB(now(), INTERVAL 3 MONTH)
GROUP BY TITLED_APPLICATION_ID, MACHINE_ID
ORDER BY Time DESC
Thanks for any help
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
h2opolo25
9 years ago
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",
SVTS.INSTALLED_ON as "Install Date"
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 "%Illustrator CC%" and END > DATE_SUB(now(), INTERVAL 3 MONTH)
GROUP BY TITLED_APPLICATION_ID, MACHINE_ID
ORDER BY Time DESC
Comments:
-
Thank you for the response. I just tested the report but the install date was returned as a number not a date. - CBT IT 9 years ago
-
This could be due to the way that the real time clock stores information. It does not store date and time, but it stores the number of "ticks" since the original start date of PC clocks which is sometime in 1978 if I recall correctly. You may need to add some code to convert the tick count to a date and time reading. As a test to see if this is the case, increment the date time in the registry of the test machine and see what tick count is returned in your report. - EdT 9 years ago
Posted by:
jleitsch
9 years ago
The installed_on field referenced in the SAM_VIEW_TITLED_SOFTWARE is not a date field, It is the number of machines that have it installed.
You could use the SAM_VIEW_INVENTORY_ADD_REMOVE_PROGRAMS to find when a software title was first inventoried by the K1000, but this is the first inventory of all machines.
If you wanted Machine specific you might have to go through the Machines History to see the first time THAT software was detected.(Not sure what table this change log is located though)
You could use the SAM_VIEW_INVENTORY_ADD_REMOVE_PROGRAMS to find when a software title was first inventoried by the K1000, but this is the first inventory of all machines.
If you wanted Machine specific you might have to go through the Machines History to see the first time THAT software was detected.(Not sure what table this change log is located though)
