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

Comments

Please log in to comment

Answers

0
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
Answered 07/30/2015 by: h2opolo25
Red Belt

  • Thank you for the response. I just tested the report but the install date was returned as a number not a date.
    • 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.
Please log in to comment
0
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)
Answered 08/04/2015 by: jleitsch
Orange Senior Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Five Steps for Easier- and More Effective- IT Inventorying
Having an accurate inventory of all your IT assets is critical to ensuring that users are productive and that every dollar is well spent. This white paper outlines five steps that improve inventorying both hardware and software.

Share