/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Need help converting old report

02/10/2020 86 views

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!

0 Comments   [ + ] Show comments

Comments


All Answers

0

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

Answered 02/14/2020 by: balldigy
Senior White Belt

 
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