/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: h2opolo25 8 years ago
Red Belt
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

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 8 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 8 years ago
Posted by: jleitsch 8 years ago
Purple Belt
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)

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ