/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Report to show Device Name, OS Name, Domain Name, and Last Patched date.

05/13/2019 458 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.
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ