/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Need help fixing a report for KACE Version 10

02/04/2020 346 views

I had a weekly patching report that we ran but the version 10 upgrade broke it (of course). So now I am looking over all the schema changes and I can't seem to figure out the problem. Here is what the report looked like before the upgrade:

SELECT PP.TITLE AS DISPLAY_NAME, 

PP.RELEASEDATE, 

M.NAME AS ComputerName,

M.IP,

M.USER_LOGGED as USER_LOGGED, 

MS.DEPLOY_STATUS, 

MS.STATUS,

DETECT_ERROR_CODE,

DEPLOY_ERROR_CODE


FROM PATCHLINK_MACHINE_STATUS MS


JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID

JOIN MACHINE M ON M.ID = MS.MACHINE_ID

JOIN PATCHLINK_PATCH_STATUS S ON PP.UID = S.PATCHUID


WHERE (MS.STATUS = 'FAIL' or (MS.DEPLOY_STATUS = 'FAIL' and MS.STATUS != 'PATCHED')) 

and S.STATUS = 0 

and PP.IMPACTID LIKE 'Critical%' 

and PP.TITLE != 'Defender%' 

and PP.TITLE NOT LIKE '%Windows Malicious Software Removal%'

and DEPLOY_STATUS != 'SUCCESS'


ORDER BY M.NAME


I fixed all the tables using the schema change article (https://support.quest.com/kace-systems-management-appliance/kb/309572/updating-custom-sql-queries-for-10-0) and I looked through the other article outlining the changes (https://support.quest.com/kace-systems-management-appliance/kb/312911/kace-sma-10-1-database-schema-changes), but I am still having trouble with it.


I think some of the column names changed but I am having a hard time figuring out what they changed to. Any help is greatly appreciated.


This is what I have so far for the updated report, but right now it is coming back empty:

SELECT PP.TITLE AS DISPLAY_NAME, 

PP.CREATION_DATE,

M.NAME AS ComputerName,

M.IP,

M.USER_LOGGED as USER_LOGGED, 

MS.DEPLOY_STATUS, 

MS.DETECT_STATUS,

DETECT_ERROR_CODE,

DEPLOY_ERROR_CODE


FROM PATCH_MACHINE_STATUS MS


JOIN KBSYS.PATCH PP ON PP.PATCH_IDENTIFIER = MS.PATCH_ID

JOIN MACHINE M ON M.ID = MS.MACHINE_ID

JOIN PATCH_STATUS S ON PP.PATCH_IDENTIFIER = S.PATCH_ID


WHERE (MS.DETECT_STATUS = 'FAIL' or (MS.DEPLOY_STATUS = 'FAIL' and MS.DETECT_STATUS != 'PATCHED')) 

and S.STATUS = 0 

and PP.SEVERITY LIKE 'Critical%' 

and PP.TITLE != 'Defender%' 

and PP.TITLE NOT LIKE '%Windows Malicious Software Removal%'

and DEPLOY_STATUS != 'SUCCESS'


ORDER BY M.NAME

0 Comments   [ + ] Show comments

Comments


All Answers

0

In the KBSYS.PATCH table you need to use the ID column, not the PATCH_IDENTIFIER column. Changing your join statements will get you results.

Answered 02/05/2020 by: chucksteel
Red 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