Added install date to report
I have a hopefully simple request. I have a query and I need to try and add the installed date to this report. Ive tried to search for it but the query keeps erroring out when I add the date string. The code below is fully functional.
select
S.DISPLAY_NAME as SOFTWARE_NAME,
S.DISPLAY_VERSION as VERSION,
M.NAME as MACHINE_NAME,
M.USER_FULLNAME as LAST_USER
from
SOFTWARE S
left join
MACHINE_SOFTWARE_JT MSJ ON S.ID = MSJ.SOFTWARE_ID
left join
MACHINE M ON MSJ.MACHINE_ID = M.ID
where
S.DISPLAY_NAME like 'AgentInstall64%'
and not IS_PATCH
order by S.DISPLAY_NAME , S.DISPLAY_VERSION , M.NAME
I was hoping for 'S.DISPLAY_Installed as Installed_date'..... not so much... haha
PS. Does anyone know of a good quick reference guide for SQL commands?
Answers (1)
The installed date in the software table is the date it joined Kace. You can query the asset history table and see when it was first detected.
SELECT VALUE1 AS 'SOFTWARE NAME', VALUE2 AS 'VERSION',
M.NAME AS 'MACHINE NAME', TIME AS 'DATE DETECTED',
M.USER_FULLNAME AS 'LAST USER', ASSET_ID
FROM ASSET_HISTORY AH
LEFT JOIN MACHINE M ON M.BIOS_SERIAL_NUMBER= AH.NAME
WHERE CHANGE_TYPE ='ADDITION'
AND VALUE1 LIKE 'AgentInstall64%'
