Patch Report
I would like a patch report to send to application owners detailing to them what patches are detected as not patched for their servers so I would need something like this:
SELECT MACHINE.NAME AS COMPUTER_NAME,
KBSYS.PATCHLINK_PATCH.TITLE AS PATCH_TITLE, PATCHLINK_MACHINE_STATUS.STATUS AS PATCH_STATUS, LABEL.NAME AS LABEL_NAME FROM (PATCHLINK_MACHINE_STATUS, LABEL, KBSYS.PATCHLINK_PATCH, MACHINE,
MACHINE_LABEL_JT,PATCHLINK_PATCH_STATUS)
WHERE PATCHLINK_MACHINE_STATUS.STATUS = 'NOTPATCHED'
and PATCHLINK_PATCH_STATUS.STATUS = 0
and PATCHLINK_MACHINE_STATUS.PATCHUID=KBSYS.PATCHLINK_PATCH.UID
and MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID
and LABEL.ID=MACHINE_LABEL_JT.LABEL_ID
and MACHINE.ID=MACHINE_LABEL_JT.MACHINE_ID
and is_app=1 /*Select only Application Patches*/ and LABEL.NAME="Stake Holder - application owners name" /*Only show patches for label "Stake Holder - Name"*/ and Vendor like "%Microsoft%" /*Select only Microsoft as the Vendor*/ and KBSYS.PATCHLINK_PATCH.TITLE not like "%autorun%"
and KBSYS.PATCHLINK_PATCH.TITLE not like "%windows malicious software%"
order by KBSYS.PATCHLINK_PATCH.TITLE, MACHINE.NAME
I would like to add to this only active patches. Can anyone help.
SELECT MACHINE.NAME AS COMPUTER_NAME,
KBSYS.PATCHLINK_PATCH.TITLE AS PATCH_TITLE, PATCHLINK_MACHINE_STATUS.STATUS AS PATCH_STATUS, LABEL.NAME AS LABEL_NAME FROM (PATCHLINK_MACHINE_STATUS, LABEL, KBSYS.PATCHLINK_PATCH, MACHINE,
MACHINE_LABEL_JT,PATCHLINK_PATCH_STATUS)
WHERE PATCHLINK_MACHINE_STATUS.STATUS = 'NOTPATCHED'
and PATCHLINK_PATCH_STATUS.STATUS = 0
and PATCHLINK_MACHINE_STATUS.PATCHUID=KBSYS.PATCHLINK_PATCH.UID
and MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID
and LABEL.ID=MACHINE_LABEL_JT.LABEL_ID
and MACHINE.ID=MACHINE_LABEL_JT.MACHINE_ID
and is_app=1 /*Select only Application Patches*/ and LABEL.NAME="Stake Holder - application owners name" /*Only show patches for label "Stake Holder - Name"*/ and Vendor like "%Microsoft%" /*Select only Microsoft as the Vendor*/ and KBSYS.PATCHLINK_PATCH.TITLE not like "%autorun%"
and KBSYS.PATCHLINK_PATCH.TITLE not like "%windows malicious software%"
order by KBSYS.PATCHLINK_PATCH.TITLE, MACHINE.NAME
I would like to add to this only active patches. Can anyone help.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
dchristian
14 years ago
Hey Caper,
I think this will work for you.
I think this will work for you.
SELECT MACHINE.NAME AS COMPUTER_NAME,
KBSYS.PATCHLINK_PATCH.TITLE AS PATCH_TITLE,
PATCHLINK_MACHINE_STATUS.STATUS AS PATCH_STATUS,
LABEL.NAME AS LABEL_NAME
FROM PATCHLINK_MACHINE_STATUS,
LABEL,
KBSYS.PATCHLINK_PATCH,
MACHINE,
MACHINE_LABEL_JT,
PATCHLINK_PATCH_STATUS
WHERE PATCHLINK_MACHINE_STATUS.PATCHUID=KBSYS.PATCHLINK_PATCH.UID
and MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID
and LABEL.ID=MACHINE_LABEL_JT.LABEL_ID
and MACHINE.ID=MACHINE_LABEL_JT.MACHINE_ID
and PATCHLINK_PATCH_STATUS.PATCHUID = KBSYS.PATCHLINK_PATCH.UID
and Vendor like "%Microsoft%" /*Select only Microsoft as the Vendor*/
and KBSYS.PATCHLINK_PATCH.TITLE not like "%autorun%"
and KBSYS.PATCHLINK_PATCH.TITLE not like "%windows malicious software%"
and KBSYS.PATCHLINK_PATCH.IS_APP =1 /* 0 for OS 1 for App */
and PATCHLINK_MACHINE_STATUS.STATUS = 'NOTPATCHED'
and PATCHLINK_PATCH_STATUS.STATUS = 0
and LABEL.NAME='Stake Holder - application owners name' /*Label to look for*/
ORDER by KBSYS.PATCHLINK_PATCH.TITLE, MACHINE.NAME
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
