Hello,

This report gives me missing OS Patches ordered by Machine. Currently, it will include inactive patches in the output. I would like to further refine this report to only give active patches. Can anyone help me out? I apologize in advance, I am new with MySQL.

Thanks,
Nicole

SELECT PP.TITLE AS DISPLAY_NAME,
M.NAME AS ComputerName
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE MS.STATUS = 'NOTPATCHED'
and is_app=0 /*Only select Operating System Patches*/
and Vendor like "%Microsoft%" /*Select patches from only Microsoft as Vendor*/
and identifier!="2k3sp2" /*Exclude Service Pack 2 for Server 2003*/
and identifier!="Win2K8SP2" /*Exclude Service Pack 2 for Server 2008*/
ORDER BY M.NAME
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Hey Nicole,

Try this
SELECT PP.TITLE AS DISPLAY_NAME,
M.NAME AS COMPUTERNAME
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 PS
ON PP.UID = PS.PATCHUID
WHERE MS.STATUS = 'NOTPATCHED'
AND IS_APP = 0 /*Only select Operating System Patches*/
AND VENDOR LIKE "%Microsoft%"
/*Select patches from only Microsoft as Vendor*/
AND IDENTIFIER!="2k3sp2" /*Exclude Service Pack 2 for Server 2003*/
AND IDENTIFIER!="Win2K8SP2" /*Exclude Service Pack 2 for Server 2008*/
AND PS.STATUS = 0 /* 0=ACTIVE, 1=INACTIVE, 4=DISABLED */
ORDER BY M.NAME
Answered 11/23/2010 by: dchristian
Red Belt

Please log in to comment
0
Hi David,

Yes, it did work. Thank you so much for your time. It not only helps me in the learning process but I can also use the report in the meantime.

Nicole
Answered 11/24/2010 by: NicoleK
Senior Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity