Need help converting old report
We had a "Missing Patches" report in Version 9 of KACE. We use this on a constant basis. The SQL scripting for this is below:
SELECT
PP.TITLE AS DISPLAY_NAME,
(CASE PP.TYPE WHEN 0 THEN 'Non-Security' WHEN 1 THEN 'Security' ELSE '' END) AS TYPE,
PP.IMPACTID,
PP.SEVERITY,
PP.VENDOR,
PP.RELEASEDATE,
M.NAME AS ComputerName,
IP,
(CASE PP.IS_SUPERCEDED WHEN 1 THEN 'Yes' ELSE 'No' END) AS SUPERSEDED
FROM PATCH_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE MS.STATUS = 'NOTPATCHED'
ORDER BY PP.TITLE, M.NAME
Does anyone have any idea how to convert this to the new tables for Version 10? I know that the tables were changed and it broke some of the custom reports, I am just hoping that someone might have a secret decoder ring to help out with this.
Thanks!
Answers (1)
Quest put out an Article describing the 9.1 > 10.0 DB Schema Changes back on 8/27/2019. >>>>> https://support.quest.com/kb/309180/kace-sma-10-0-database-schema-changes
And here is one other article reflecting the MAPPING Changes which has helped me out a bunch too: https://support.quest.com/kace-systems-management-appliance/kb/309572/updating-custom-sql-queries-for-10-0
Additionally, because we don't allow for the off appliance access to the database, I have been able to run simple SQL Reports that List * columns and that gives me an idea on what the column names may have changed to....a bit of trial and error, but still helpful none-the-less.
Hope this helps a bit.
But let me see if this may help you out for now (by the way, I made some assumptions and clarified some alias info to match the Table Names). It looks like previously you were trying to assign an importance the Patch Type. They have previously marked it based upon an ID (which is where I think you were going with the CASE query) but they have stored the Classification into the same Database Table along with the rest of the main Patch Info. So i condensed it down to show Security vs Non-security. Obviously you can add upon this based upon your needs if you want but I think this was the point that you were trying to get to. I don't remember off the top of my head the IMPACTID but the previous 9.x tables actually had IMPACT (from a patching perspective) as only Critical and Recommended. But the migration of that terminology is now identified as SEVERITY. This link is very informative as I have linked to it above as well (https://support.quest.com/kace-systems-management-appliance/kb/309572/updating-custom-sql-queries-for-10-0):
SELECT
P.TITLE AS DISPLAY_NAME,
(CASE P.CLASSIFICATION WHEN 'Security Updates' THEN 'Security' ELSE 'Non-Security' END) AS TYPE,
P.IMPACT_INSTALL,
P.SEVERITY,
P.PUBLISHER,
P.CREATION_DATE,
M.NAME AS ComputerName,
IP,
(CASE P.IS_SUPERCEDED WHEN 1 THEN 'Yes' ELSE 'No' END) AS SUPERSEDED
FROM PATCH_MACHINE_STATUS MS
JOIN KBSYS.PATCH P ON P.ID = MS.PATCH_ID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE MS.DETECT_STATUS = 'NOTPATCHED'
ORDER BY P.TITLE, M.NAME
