/build/static/layout/Breadcrumb_cap_w.png
05/13/2019 215 views

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. 

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1
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


Answered 05/14/2019 by: chucksteel
Red Belt

  • 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.