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?

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

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%'
Answered 09/05/2013 by: dugullett
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity