Within Inventory > Devices > Security > Patching Detect/Deploy Status > Deployment Scheduled shows a list of the updates/patches to be deployed via the Detect/Deploy jobs.  I need a report which shows the same information as what is listed in that area. The SQL query (which I found on itninja) below gets me close, but it lists all critical patches, including patches which I have excluded in the patch smart label, due to breaking other software...

SELECT M.NAME AS COMPUTER_NAME,
PP.TITLE AS PATCH_NAME,
OS_NAME AS WINDOWS_VERSION,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN MACHINE_LABEL_JT ML on ML.MACHINE_ID = M.ID 
WHERE MS.STATUS = 'NOTPATCHED'
AND ML.LABEL_ID = (select ID from LABEL where NAME = 'Machine_Label_Name')
AND PP.IMPACTID = ('Critical')
AND PPS.STATUS in (0)
ORDER BY M.NAME, PP.TITLE

Your help with this is appreciated.

Eric
Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • Thanks, JasonEgg! Exactly what was needed.
Please log in to comment

Answer Chosen by the Author

0
This query assumes you have only one 'patch smart label' to run this query against:
SELECT M.NAME AS COMPUTER_NAME,
PP.TITLE AS PATCH_NAME,
OS_NAME AS WINDOWS_VERSION,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN PATCHLINK_PATCH_LABEL_JT PL on PPS.PATCHUID = PL.PATCHUID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN MACHINE_LABEL_JT ML on ML.MACHINE_ID = M.ID 
WHERE MS.STATUS = 'NOTPATCHED'
AND ML.LABEL_ID = (select ID from LABEL where NAME = 'YOUR-MACHINE-LABEL')
AND PL.LABEL_ID = (select ID from LABEL where NAME = 'YOUR-PATCH-LABEL')
AND PPS.STATUS = 0 
ORDER BY M.NAME, PP.TITLE

If you want to run against multiple machine labels or patch labels you will change the sub-selects in the 'where' clause as follows:
ML.LABEL_ID IN (select ID from LABEL where NAME IN ('YOUR_LABEL_NAME','OTHER_LABEL_NAME','YET_ANOTHER_LABEL'))
Answered 07/08/2016 by: JasonEgg
Third Degree Green Belt

Please log in to comment

Answers

Answer this question or Comment on this question for clarity

Share