Kace SQL customized report - ADD agent connection date and time
Hello,
I do have a customized report to keep track of patchs installed in our systems where i would like to add "agent last connection" column for each device.
I have no clue with queries so If anyone could help would be appreciated !
Thank you
------------------------------------------------------------------------
SELECT
M.NAME AS MACHINE_NAME,
SYSTEM_DESCRIPTION,
OS_NAME AS WINDOWS_VERSION,
SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,
SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,
concat("http://kace.mycompany.com/adminui/machine.php?ID=", M.id) as linkKACE
FROM
PATCH_MACHINE_STATUS MS
JOIN
KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
JOIN
PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
JOIN
MACHINE M ON (M.ID = MS.MACHINE_ID)
WHERE
PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0
AND PPS.IS_SUPERCEDED = 0
GROUP BY MS.MACHINE_ID
ORDER BY NOTPATCHED DESC
Answers (2)
Top Answer
Added Last Inventory and Last Connection date/time
SELECT
M.NAME AS MACHINE_NAME,
SYSTEM_DESCRIPTION,
OS_NAME AS WINDOWS_VERSION,
SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,
SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,
M.LAST_INVENTORY,
KBSYS.SMMP_CONNECTION.CONNECT_TIME as 'Last Connection',
concat("http://kace.mycompany.com/adminui/machine.php?ID=", M.id) as 'Link to Device'
FROM PATCH_MACHINE_STATUS MS
JOIN KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
JOIN PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN KBSYS.SMMP_CONNECTION ON M.KUID = KBSYS.SMMP_CONNECTION.KUID
WHERE PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0 AND PPS.IS_SUPERCEDED = 0
GROUP BY MS.MACHINE_ID ORDER BY NOTPATCHED DESC
