Issue creating new SQL report for compliance
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
Answers (2)
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
Comments:
-
What version server are you running? This works on a 5.4. - dugullett 11 years ago
-
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. - cwest311 11 years ago
-
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) - dugullett 11 years ago
-
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) - cwest311 11 years ago-
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. - dugullett 11 years ago
-
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)' - cwest311 11 years ago -
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. - dugullett 11 years ago -
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 - dugullett 11 years ago
-
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?!? - cwest311 11 years ago-
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? - dugullett 11 years ago
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%')