Systems Management

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