Hello,

I am trying to create a report that will query some machine labels I have created one that will tell me what patches are missing so I can focus on getting those patched by Friday. Im not sure why my disabled patches are showing up and I cant get the machines to show up in the report either so I can tell what I need to round up. Here is what I have so far:

 

SELECT RELEASEDATE,VENDOR,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n')
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 30 DAY)) AND LABEL.NAME like '%Patch - Win XP Recommended%' OR LABEL.NAME like '%Patch - Win XP Security Critical%'
AND PATCHLINK_PATCH_STATUS.STATUS = 0
AND ((VENDOR = 'Sun Java') OR (VENDOR = 'Adobe Systems Inc.') OR (VENDOR = 'Microsoft Corp.')) 
GROUP BY KBSYS.PATCHLINK_PATCH.ID desc,PATCH_STATUS desc

 

I wanted to try and keep the percentages in there so I can show that we are making headway in patching the environment.

 

As always, thanks for the help!!

CWest

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1

I appreciate the response. I made the changes but I get "Unknown column 'P.Machine_ID' in 'on clause'. I also was still getting disabled patches with the new '0' parameter.

 

SELECT M.NAME,RELEASEDATE,VENDOR,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n')
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 MACHINE M ON (M.ID=P.MACHINE_ID)
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 30 DAY)) AND (LABEL.NAME like '%Patch - Win XP Recommended%' OR LABEL.NAME like '%Patch - Win XP Security Critical%')
AND PATCHLINK_PATCH_STATUS.STATUS = '0'
AND ((VENDOR = 'Sun Java') OR (VENDOR = 'Adobe Systems Inc.') OR (VENDOR = 'Microsoft Corp.')) 
GROUP BY KBSYS.PATCHLINK_PATCH.ID desc,PATCH_STATUS desc

Answered 05/08/2013 by: cwest311
Second Degree Blue Belt

  • What version server are you running? This works on a 5.4.
    • 5.4 SP1... I am trying to run the query on MySQL workbench. I get the same error if I try to paste it into the reporting window.
      • Take this line and move it to the bottom of your JOIN tasks. P.MACHINE_ID is not defined until later in the query.

        LEFT JOIN MACHINE M ON (M.ID=P.MACHINE_ID)
  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • Well I will say that this report works because I am now able to see machine names. The issue I have now is it only shows me one machine out of the group.

    EX.

    Name Releasedate Vendor Impactid Patch Status Patched Notpatched Percent Identifier Title
    ***1161W6SR**** 10/9/2012 0:00 Microsoft Corp Patch - Win XP Recommended Active 3855 367 91 KB2749655 Update for Windows XP (KB2749655)
    • I would try changing the "GROUP BY KBSYS.PATCHLINK_PATCH.ID desc,PATCH_STATUS desc" line to "GROUP BY M.NAME". Sorry I'm not able to test this currently. You'll have to let me know.
      • Here is the output that i have now.

        SELECT M.NAME,RELEASEDATE,VENDOR,
        GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n')
        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
        LEFT JOIN MACHINE M ON (M.ID=P.MACHINE_ID)
        WHERE (RELEASEDATE < DATE_SUB(NOW(),INTERVAL 30 DAY)) AND (LABEL.NAME like '%Patch - Win XP Recommended%' OR LABEL.NAME like '%Patch - Win XP Security Critical%')
        AND PATCHLINK_PATCH_STATUS.STATUS = '0'
        AND ((VENDOR = 'Sun Java') OR (VENDOR = 'Adobe Systems Inc.') OR (VENDOR = 'Microsoft Corp.'))
        GROUP BY 'M.NAME'

        This only returns one row in MySQL


        '***5000WCFZW***', '2006-05-02 00:00:00', 'Microsoft Corp.', 'Patch - Win XP Recommended\nPatch - Win XP Security Critical', 'Active', '1251803', '105139', '92', 'KB883921', 'Update for Windows XP SP2 Serbian-Latin Language Interface Pack (KB883921)'
      • Change the last line to

        GROUP BY M.NAME, KBSYS.PATCHLINK_PATCH.ID desc,PATCH_STATUS desc

        Also don't put the quotes around M.NAME. It's going to be a matter of the grouping. It's going to have to find the right combination. Usually I would test this, but working on a new patching setup. I might be able to throw something together tomorrow if this doesn't work. Let me know.
      • Actually looking at this again we need to combine the machine names. I'm thinking this is what you're looking for. Notice the change on the first line along with the last.

        SELECT GROUP_CONCAT(DISTINCT M.NAME SEPARATOR '\n') AS MACHINE_NAME,RELEASEDATE,VENDOR,
        GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n')
        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
        LEFT JOIN MACHINE M ON (M.ID=P.MACHINE_ID)
        WHERE (RELEASEDATE < DATE_SUB(NOW(),INTERVAL 30 DAY)) AND (LABEL.NAME like 'Patch Subscription: Adobe- All Titles - Windows%' OR LABEL.NAME like 'Patch Subscription: Windows Critical OS%')
        AND PATCHLINK_PATCH_STATUS.STATUS = '0'
        AND ((VENDOR = 'Sun Java') OR (VENDOR = 'Adobe Systems Inc.') OR (VENDOR = 'Microsoft Corp.'))
        GROUP BY M.NAME,KBSYS.PATCHLINK_PATCH.ID desc,PATCH_STATUS desc
  • OK. that seemed to work a lot better but when I input the code into KACE I get

    "Internal error (3). Please contact KACE support.

    Any ideas?!?
    • Sorry for the late reply. I've never seen that before, but would be curious to know what was causing it. My only guess is it is a pretty elaborate query. I'm wondering if it knows what to do with it?
Please log in to comment

Answers

0

I'm currently in the process of rebuilding our patching setup so I cannot test this, but you should be able to join the MACHINE table. 

Add M.NAME to the SELECT portion.

JOIN the MACHINE table:

LEFT JOIN MACHINE M ON M.ID=P.MACHINE_ID

 

Also it looks like you are correct on the disabled patches. I would try adding ' ' around the 0 in 

AND PATCHLINK_PATCH_STATUS.STATUS = '0'. You can also try including ( ) around (LABEL.NAME like '%Patch - Win XP Recommended%' OR LABEL.NAME like '%Patch - Win XP Security Critical%')

Answered 05/08/2013 by: dugullett
Red Belt

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

Share