ITNinja

chucksteel here is my sql code. I am not an expert in SQL so editing that for me is a bit cumbersome. - tjbake 12 years ago

SELECT RELEASEDATE,VENDOR,GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,IMPACTID,(CASE PATCHLINK_PATCH_STATUS.STATUS WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' WHEN 4 THEN 'Disabled' ELSE 'Unknown' END) AS PATCH_STATUS,SUM(P.STATUS='PATCHED') AS PATCHED,SUM(P.STATUS='NOTPATCHED') AS NOTPATCHED,IDENTIFIER,TITLE FROM KBSYS.PATCHLINK_PATCH LEFT JOIN PATCHLINK_PATCH_LABEL_JT ON (PATCHLINK_PATCH_LABEL_JT.PATCHUID = KBSYS.PATCHLINK_PATCH.UID) LEFT JOIN LABEL ON (LABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_ID) LEFT JOIN PATCHLINK_PATCH_STATUS ON PATCHLINK_PATCH_STATUS .PATCHUID = PATCHLINK_PATCH.UID LEFT JOIN PATCHLINK_MACHINE_STATUS P ON P.PATCHUID = KBSYS.PATCHLINK_PATCH.UID WHERE ((DATE(RELEASEDATE) <= NOW() AND DATE(RELEASEDATE) > DATE_SUB(NOW(),INTERVAL 20 DAY))) OR ((VENDOR = 'Sun Java') AND (VENDOR = 'Adobe Systems Inc.') AND (VENDOR = 'Microsoft Corp.')) GROUP BY KBSYS.PATCHLINK_PATCH.ID ORDER BY LABEL_NAME_GROUPED desc,PATCH_STATUS desc - tjbake 12 years ago

Here's an updated query:
SELECT RELEASEDATE,VENDOR,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,
IMPACTID,
(CASE PATCHLINK_PATCH_STATUS.STATUS WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' WHEN 4 THEN 'Disabled' ELSE 'Unknown' END) AS PATCH_STATUS,
SUM(P.STATUS='PATCHED') AS PATCHED,SUM(P.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(P.STATUS='PATCHED')/(SUM(P.STATUS='PATCHED')+SUM(P.STATUS='NOTPATCHED')))*100,0) AS PERCENT,
IDENTIFIER,TITLE
FROM KBSYS.PATCHLINK_PATCH
LEFT JOIN PATCHLINK_PATCH_LABEL_JT ON (PATCHLINK_PATCH_LABEL_JT.PATCHUID = KBSYS.PATCHLINK_PATCH.UID)
LEFT JOIN LABEL ON (LABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_ID)
LEFT JOIN PATCHLINK_PATCH_STATUS ON PATCHLINK_PATCH_STATUS .PATCHUID = PATCHLINK_PATCH.UID
LEFT JOIN PATCHLINK_MACHINE_STATUS P ON P.PATCHUID = KBSYS.PATCHLINK_PATCH.UID
WHERE (RELEASEDATE > DATE_SUB(NOW(),INTERVAL 20 DAY))
AND ((VENDOR = 'Sun Java') OR (VENDOR = 'Adobe Systems Inc.') OR (VENDOR = 'Microsoft Corp.'))
GROUP BY KBSYS.PATCHLINK_PATCH.ID ORDER BY LABEL_NAME_GROUPED desc,PATCH_STATUS desc

A few things:
I added the percent patched:
ROUND((SUM(P.STATUS='PATCHED')/(SUM(P.STATUS='PATCHED')+SUM(P.STATUS='NOTPATCHED')))*100,0) AS PERCENT

Your syntax for the date matching wasn't correct. The RELEASEDATE column is already a date format, so you don't need to use the DATE function to get the date part. Also, the post was missing the greater than symbol, although it's possible that the website removed it, as that's also a special character for webpages. The code should read:
(RELEASEDATE [greater than] DATE_SUB(NOW(),INTERVAL 20 DAY))

Your statement was also looking for things within that date range or with the vendors specified, I think what you're actually after is patches within that date range and with the vendors specified. You might also want to consider matching based on labels that you're actually using to identify those patches that you're deploying. Otherwise the query will show all patches from those vendors, including those that you're not deploying at all.

If you're not already using a tool like MySQL Workbench I would highly recommend installing it. It's much easier to work on your query there and then copy it into a SQL report. - chucksteel 12 years ago

One more thing, adding these two statements to the WHERE clause will only include patches that have a label name containing Critical and that are active:
AND LABEL.NAME like '%Critical%'
AND PATCHLINK_PATCH_STATUS.STATUS = 0

You would place these before the GROUP BY statement. - chucksteel 12 years ago

Thanks chucksteel. I will get this setup and see how it looks once some of my patches run. - tjbake 12 years ago

I copied and ran the report and here is the output
Title: Copy of Critical Patch Report
Description:
Category: patch
Server Hostname: k1000.kimballmidwest.local
Generated: 2012/04/24 07:06:00

Date Posted Vendor Severity Patched Not Patched Identifier Title
12 of Label Names: Win7 MS Critical Patches, Status: Active
2012-04-10 00:00:00 Microsoft Corp. Critical 2 4 33 kb2598039 MS12-027 2598039 Security Update for Microsoft Office 2010 (All Languages)
2012-04-10 00:00:00 Microsoft Corp. Critical 2 14 13 MS12-025 MS12-025 Security Update for Microsoft .NET Framework 4 on XP, 2003, Vista, Win7, 2008 x86 (KB2656368)
2012-04-10 00:00:00 Microsoft Corp. Critical MS12-025 MS12-025 Security Update for Microsoft .NET Framework 4 on XP, 2003, Vista, Win7, 2008, 2008 R2 x64 (KB2656368)
2012-04-10 00:00:00 Microsoft Corp. Critical 2 10 17 MS12-023 MS12-023 Cumulative Security Update for Internet Explorer 8 for Windows 7 (KB2675157)
2012-04-10 00:00:00 Microsoft Corp. Critical MS12-023 MS12-023 Cumulative Security Update for Internet Explorer 9 for Windows 7 (KB2675157)
2012-04-10 00:00:00 Microsoft Corp. Critical 2 10 17 MS12-024 MS12-024 Security Update for Windows 7 (KB2653956)
2012-04-10 00:00:00 Microsoft Corp. Critical 2 10 17 MS12-025 MS12-025 Security Update for Microsoft .NET Framework 3.5.1 on Win7 x86 (KB2656372)
2012-04-10 00:00:00 Microsoft Corp. Critical MS12-023 MS12-023 Cumulative Security Update for Internet Explorer 8 for Windows 7 for x64 (KB2675157)
2012-04-10 00:00:00 Microsoft Corp. Critical MS12-023 MS12-023 Cumulative Security Update for Internet Explorer 9 for Windows 7 for x64 (KB2675157)
2012-04-10 00:00:00 Microsoft Corp. Critical MS12-024 MS12-024 Security Update for Windows 7 for x64 (KB2653956)
2012-04-10 00:00:00 Microsoft Corp. Critical MS12-025 MS12-025 Security Update for Microsoft .NET Framework 3.5.1 on Win7 and 2008 R2 SP1 x64 (KB2656373)
2012-04-10 00:00:00 Microsoft Corp. Critical MS12-025 MS12-025 Security Update for Microsoft .NET Framework 3.5.1 on Win7 SP1 x86 (KB2656373) - tjbake 12 years ago

I do not see any kind of percentage patched and the date is not showing 30 days - tjbake 12 years ago