/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Did your custom patch reports break when upgrading Kace 1000 SMA to version 10? Mine all did and here is how to fix them...

11/14/2019 576 views

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



Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1

This is great information - you may want to add it under "blogs"

Answered 11/20/2019 by: jonniipalos
Red Belt

  • Thanks! since i was a new user to the forum it wouldn't let me start a blog so i posted it as a question for the time being. I have replicated it over there now. https://www.itninja.com/blog/view/did-your-custom-sql-patch-reports-break-when-upgrading-kace-1000-sma-to-version-10-mine-all-did-and-here-is-how-to-fix-them
 
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