Need Patch SQL script to show the device patching status and percent report
Need Patch SQL script to show the device patching status and percent report.
1、Report the patch installation according to label (win7, win10, win2016, win2019, etc.), such as success rate.
2、Which machines succeeded and which failed, percent rate.
The information is available in the PATCH_MACHINE_STATUS table.
If you are not familiar with writing a SQL report, you can contact the Professional Services group at GettingStarted@quest.com.
They can provide you with a quote on the fee.
I write a SQL report and it works by SMART LABEL
M.NAME AS MACHINE_NAME,
OS_NAME AS WINDOWS_VERSION,
M.LAST_SYNC as Last_Inventory,
M.LAST_REBOOT as Last_Reboot,
SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,
SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,
FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS =
'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS
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 SC ON (M.KUID = SC.KUID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
PPS.STATUS = 0
AND PPS.IS_SUPERCEDED = 0
AND L.NAME = 'Windows 10'
GROUP BY MS.MACHINE_ID
ORDER BY PERCENT_PATCHED , M.NAME