/build/static/layout/Breadcrumb_cap_w.png

Report Request - Need a report which lists the same information shown in the "Deployment Scheduled" area of the device inventory

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

2 Comments   [ + ] Show comments
  • Thanks, JasonEgg! Exactly what was needed. - eschmidt2050 4 years ago
  • How would one expand off of this query to include a column for "Patch deploy date" and "Patch deploy status". i'm not enough of a mySQL guru to find the table name associations - fcpkaceguy 1 year ago

Answers (1)

Answer Summary:
Posted by: JasonEgg 4 years ago
Red Belt
0

Top Answer

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'))
 
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