Report to show Device Name, OS Name, Domain Name, and Last Patched date.
I am trying to figure out how to create a report that has the Device Name, OS Name, Domain Name, and Last Patched date. I have never learned how to use SQL Scripting but I am sure it can be done through SQL script reporting. If anyone could help me with this I would be very appreciative.
There is a script that is very close that someone else made. (https://www.itninja.com/question/k1000-reporting-need-report-of-patch-compliance-by-device-not-by-patch)
SELECT M.ID as Machine_ID, M.NAME AS Machine, M.KUID
, K.PHASE AS Phase, K.TYPE
, PSMS.PATCHED, PSMS.NOTPATCHED
, P.DESCRIPTION AS Description, P.LAST_RUN AS LAST_RUN
FROM MACHINE M
LEFT JOIN KBSYS.KONDUCTOR_TASK K ON K.KUID = M.KUID
LEFT JOIN PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS ON PSMS.MACHINE_ID = M.ID
LEFT JOIN PATCHLINK_SCHEDULE P ON P.ID = PSMS.PATCHLINK_SCHEDULE_ID
WHERE K.TYPE = 'patch-ORG1-3'
GROUP BY M.NAME
ORDER BY M.NAME
But what I would like is just Device Name, OS Name, Domain Name, and Last Patched date.
Answers (1)
Top Answer
SELECT M.NAME AS 'Device Name',
M.OS_NAME as 'OS Name',
M.CS_DOMAIN as 'Domain Name',
PSMS.LAST_RUN AS 'Last Patched'
FROM MACHINE M
LEFT JOIN KBSYS.KONDUCTOR_TASK K ON K.KUID = M.KUID
LEFT JOIN PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS ON PSMS.MACHINE_ID = M.ID
LEFT JOIN PATCHLINK_SCHEDULE P ON P.ID = PSMS.PATCHLINK_SCHEDULE_ID
WHERE P.DESCRIPTION = 'Patch Production Deploy'
GROUP BY M.NAME
ORDER BY M.NAME
Be sure to change the P.DESCRIPTION = 'Patch Production Deploy' to match the name of the patching schedule you want to target. If you don't care which schedule it was, then the query can be simpler:
SELECT M.NAME AS 'Device Name',
M.OS_NAME as 'OS Name',
M.CS_DOMAIN as 'Domain Name',
MAX(PSMS.LAST_RUN) AS 'Last Patched'
FROM MACHINE M
LEFT JOIN PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS ON PSMS.MACHINE_ID = M.ID
GROUP BY M.NAME
ORDER BY M.NAME
Comments:
-
Thank you so much chucksteel! It worked great! You are the Man! As you probably noticed I used your script as the script that was closest to what I wanted in my question. - JZeigler 5 years ago
-
It appears that the SQL tables have changed. Replace "PATCHLINK_SCHEDULE_MACHINE_STATUS" with "PATCH_SCHEDULE_MACHINE_STATUS". - Nick_F 1 year ago
