Weekly Patch Report
-
Which report included with the appliance is closest to what you want and what changes would you like to see? - chucksteel 7 years ago
Answers (1)
SELECT (CASE
WHEN PBC.PATCHES_DISABLED = PBC.PATCHES_TOTAL THEN 'Disabled'
WHEN PBC.PATCHES_INACTIVE = PBC.PATCHES_TOTAL THEN 'Inactive'
WHEN PBC.PATCHES_ACTIVE = PBC.PATCHES_TOTAL THEN 'Active'
WHEN PBC.PATCHES_ACTIVE != PBC.PATCHES_TOTAL THEN
concat('Active (',PBC.PATCHES_ACTIVE,' of ',PBC.PATCHES_TOTAL,')')
ELSE 'Active'
END) AS PATCH_STATUS, PB.IDENTIFIER as Package, PB.TITLE as 'Name',PB.RELEASEDATE AS 'Released', PB.VENDOR AS 'Publisher', (CASE PB.TYPE WHEN 1THEN 'Security' WHEN 0 THEN 'Non-Security' WHEN 2 THEN 'SOFTWARE' ELSE '' END)AS TYPE, (CASE PB.IMPACTID WHEN 'Critical' THEN 'Critical' WHEN 'Critical - 01'THEN 'Old Critical'
WHEN'Critical - 05' THEN 'Superseded' WHEN'Recommended' THEN 'Recommended' ELSE '' END) AS Impact, PB.SEVERITY asVENDOR_SEVERITY, PB.REBOOT as REBOOT, PBC.COMPLIANCE AS COMPLIANCE, PBC.PATCHEDAS INSTALLED, PBC.UNPATCHED AS MISSING, PBC.ERROR AS ERROR, PBC.CACHE_SIZE asSize, (CASE
WHENPBC.PATCHES_SUPERCEDED = 0 THEN 'No'
WHENPBC.PATCHES_SUPERCEDED = PBC.PATCHES_TOTAL THEN 'Yes'
WHENPBC.PATCHES_SUPERCEDED != PBC.PATCHES_TOTAL
THENconcat('Yes (', PBC.PATCHES_SUPERCEDED, ' of ',PBC.PATCHES_TOTAL,')')
END) ASIS_SUPERCEDED FROM KBSYS.PATCHLINK_BULLETIN PB
INNERJOIN KBSYS.PATCHLINK_IMPACT I ON I.IMPACT = PB.IMPACTID
LEFTJOIN PATCHLINK_BULLETIN_COUNT PBC ON PBC.BULLETIN_ID = PB.IDENTIFIER ANDPBC.PLATFORM_ID = PB.PLATFORM_ID WHERE (PBC.PATCHES_TOTAL!=0 AND PBC.PATCHES_DISABLED !=PBC.PATCHES_TOTAL) order by COMPLIANCEdesc
Comments:
-
FYI this query omits some spaces. Maybe line breaks are to blame? - JasonEgg 7 years ago
