Scripting

So about 2 weeks ago I upgraded our Kace 1000 to the latest and greatest version 10. Imagine my surprise when i went to look at my weekly emailed custom patch compliance reports to find that instead of the usual excel spreadsheet i received a message from Microsoft saying my attached excel file was corrupted!! Upon further inspection i found out that the report was generating MySQL errors for unknown tables. Well as it turns out Quest changed a BUNCH of their Table and Column names around in the Patch department. I fired up a chat with support and they provided me with this essential link Updating Custom SQL Queries for 10.0 (309572) to help me get my reports up and running again. While I am on the subject i will also share the 2 custom reports i have created here since they are pretty great for audits and finding problem workstations in your network.

Title: PC Critical Patch Compliance Report
Description: Percentage of windows critical patches installed on each machine for security compliance.
Category: Compliance
Break on Columns: leave blank     (Uncheck Box) Show Line Numbers
SQL:
SELECT M.NAME AS MACHINE,
OS_NAME AS WINDOWS_VERSION,
SUM(MS.PATCH_INSTALLED='1') AS INSTALLED,
SUM(MS.PATCH_INSTALLED='0') AS MISSING,
ROUND((SUM(MS.PATCH_INSTALLED='1')/(SUM(MS.PATCH_INSTALLED='1') +SUM(MS.PATCH_INSTALLED='0')))*100,0) AS COMPLIANCE_PERCENTAGE

FROM PATCH_MACHINE_STATUS MS

JOIN KBSYS.PATCH PA ON (PA.ID = MS.PATCH_ID)

JOIN PATCH_STATUS PS ON (PS.PATCH_ID = PA.ID)

JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)

JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)

JOIN LABEL L ON (ML.LABEL_ID = L.ID)

WHERE PA.SEVERITY = 'Critical'

AND PS.STATUS = 0

AND PA.IS_SUPERCEDED = 0

GROUP BY M.NAME

ORDER BY COMPLIANCE_PERCENTAGE, M.NAME

Title: Weekly Failed Critical Patch Report
Description: List all devices that are missing 'Critical' patches in order by patch name
Category: Patching
Break on Columns: PATCH  (Uncheck Box) Show Line Numbers
SQL:
SELECT PA.TITLE AS PATCH,
M.NAME AS MACHINE,
IP
FROM PATCH_MACHINE_STATUS MS
JOIN KBSYS.PATCH PA ON (PA.ID = MS.PATCH_ID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)

WHERE
PA.SEVERITY = 'Critical'
AND
MS.DEPLOY_ATTEMPT_COUNT >= MS.MAX_DEPLOY_ATTEMPT
AND
MS.PATCH_INSTALLED = 0
AND
PA.IS_SUPERCEDED = 0
AND
MS.PATCH_APPLICABLE = 1

ORDER BY PA.TITLE, M.NAME