/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


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

07/08/2016 1648 views
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:
2 Comments   [ + ] Show comments

Comments

  • Thanks, JasonEgg! Exactly what was needed.
  • 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

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
Red Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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