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 8 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 5 years ago
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
JasonEgg
8 years ago
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'))
