Hello All,

    I am looking for a report that gives me very simple items however I cannot get teh results from the wizrd. I need a report that shws Critical Patches by label for the last 30 days and then % of machines patch complaint. It doe snot sound hard but I am having issues gettign there.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

2

What do you have so far? Can you post the SQL code for what you've developed to this point? Note that if you start with the wizard to get you to a certain point you can then edit the SQL to add the things that you can't get with the wizard.

Answered 04/18/2012 by: chucksteel
Red Belt

  • chucksteel here is my sql code. I am not an expert in SQL so editing that for me is a bit cumbersome.
  • 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
  • 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.
  • 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.
  • Thanks chucksteel. I will get this setup and see how it looks once some of my patches run.
  • 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)
  • I do not see any kind of percentage patched and the date is not showing 30 days
Please log in to comment
Answer this question or Comment on this question for clarity