Custom SQL Report - Computers w/User missing Critical and Recommended patches showing calculated values for those criteria and sorted by Total missing patches
I am trying to get a report that shows the machine with its user, the number of missing patches that aren't superseded and are active broken down into Critical and Recommended with a Total of those calculated values:
| Computer Name | User | # of Missing Patches Recommended | # of Missing Patches Critical | Total Missing Patches |
| Comp A | Bob Smith | 4 | 3 | 7 |
| Comp B | Jane Doe | 3 | 3 | 6 |
| Comp C | Serge Storms | 3 | 3 | 5 |
Currently I'm trying to get the report with out the total of the missing patches and the user data and sorting by Critical count using the following:
Select M.NAME as MACHINE_NAME,
SUM((SELECT COUNT (PP.UID) FROM PATCHLINK_MACHINE_STATUS PMS
JOIN MACHINE M ON M.ID = PMS.MACHINE_ID
JOIN KBSYS.PATCHLINK_PATCH PP on (PP.UID = PMS.PATCHUID)
WHERE M.ID = MACHINE.ID AND PMS.STATUS = 'NOTPATCHED' and PP.IMPACTID = 'Critical')) AS CRITICAL,
SUM((SELECT COUNT (PP.UID) FROM PATCHLINK_MACHINE_STATUS PMS
JOIN KBSYS.MACHINE M ON M.ID = PMS.MACHINE_ID
JOIN KBSYS.PATCHLINK_PATCH PP on (PP.UID = PMS.PATCHUID)
WHERE M.ID = MACHINE.ID AND PMS.STATUS = 'NOTPATCHED' and PP.IMPACTID = 'Recommended')) AS RECOMMENDED
FROM PATCHLINK_MACHINE_STATUS PMS
order by CRITICAL desc, MACHINE_NAME
I get the following error:
mysqli error: [1142: SELECT command denied to user 'R8'@'localhost' for table 'MACHINE'] in EXECUTE(\n"Select M.NAME as MACHINE_NAME,\n SUM((SELECT COUNT (PP.UID) FROM PATCHLINK_MACHINE_STATUS PMS \n JOIN MACHINE M ON M.ID = PMS.MACHINE_ID\n JOIN KBSYS.PATCHLINK_PATCH PP on (PP.UID = PMS.PATCHUID)\n WHERE M.ID = MACHINE.ID AND PMS.STATUS = 'NOTPATCHED' and PP.IMPACTID = 'Critical')) AS CRITICAL,\n SUM((SELECT COUNT (PP.UID) FROM PATCHLINK_MACHINE_STATUS PMS\n JOIN KBSYS.MACHINE M ON M.ID = PMS.MACHINE_ID\n JOIN KBSYS.PATCHLINK_PATCH PP on (PP.UID = PMS.PATCHUID)\n WHERE M.ID = MACHINE.ID AND PMS.STATUS = 'NOTPATCHED' and PP.IMPACTID = 'Recommended')) AS RECOMMENDED\n FROM PATCHLINK_MACHINE_STATUS PMS\n order by CRITICAL desc, MACHINE_NAME LIMIT 0")\n
I am not an admin of the K1000, but a user in a sub org with a set of privileges that from the looks of things could be insufficient, that or I'm just doing it all wrong.
I can get the following report to run:
SELECT M.NAME AS MACHINE_NAME,
USER_NAME,
OS_NAME AS OS_Name,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
+SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE
(PP.IMPACTID = 'Critical')
AND PPS.STATUS = 0 /* 0=active patches */
AND PP.IS_SUPERCEDED = 0
OR
(PP.IMPACTID = 'Recommended')
AND PPS.STATUS = 0 /* 0=active patches */
AND PP.IS_SUPERCEDED = 0
GROUP BY M.NAME
order by NOT_PATCHED desc , M.NAME
That report however shows numbers that aren't matching up with the Inventory Device page for a computer and selecting the Missing Patches. Example: The above report is showing 1170 missing patches on one machine when the Device Detail page at Security
Patching Detect/Deploy Status Missing info is showing 195 missing patches.
Appreciate any feedback and assistance.
Answers (1)
Top Answer
Try this:
SELECT M.NAME,
SUM(P.IMPACTID = 'Critical') AS 'Critical',
SUM(P.IMPACTID = 'Recommended') as 'Recommended',
COUNT(P.IMPACTID) as 'Total'
-- for debugging uncomment these lines
-- , GROUP_CONCAT(CASE WHEN P.IMPACTID = 'Critical' THEN PP.TITLE END) AS 'Critical Patches'
-- , GROUP_CONCAT(CASE WHEN P.IMPACTID = 'Recommended' THEN PP.TITLE END) AS 'Recommended Patches'
-- end debugging lines
FROM ORG1.PATCHLINK_MACHINE_STATUS PMS
JOIN KBSYS.PATCHLINK_PATCH P on P.UID = PMS.PATCHUID
JOIN MACHINE M on M.ID = PMS.MACHINE_ID
JOIN KBSYS.PATCHLINK_PATCH PP on PP.UID = PMS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
WHERE PMS.STATUS = "NOTPATCHED"
and PP.IS_SUPERCEDED = 0
and PPS.STATUS = 0
GROUP BY M.NAME
I included some lines for debugging purposes that you may find useful. They will allow you to better compare the report to what you are seeing in the web interface.
Note: Your user on the system isn't particularly relevant to writing reports. Reports use an account for each organization, in your case R8.
Comments:
-
Thanks chucksteel for the reply.
When I tried your query I got the same SELECT command denied to user R8.
mysqli error: [1142: SELECT command denied to user 'R8'@'localhost' for table 'PATCHLINK_MACHINE_STATUS'] in EXECUTE(\n....
I know I have to be able to use SELECT in the queries already, and when I took the ORG1 off of the ORG1.PATCHLINK_MACHINE_STATUS PMS it started working.
The report Total of missing patches is matching up with the Deploy Status for Missing patches. Exactly what I was wanting.
Thank you so much for your time and the right method to grab the data.
Final SQL Query:
SELECT M.NAME,
SUM(P.IMPACTID = 'Critical') AS 'Critical',
SUM(P.IMPACTID = 'Recommended') as 'Recommended',
COUNT(P.IMPACTID) as 'Total'
-- for debugging uncomment these lines
-- , GROUP_CONCAT(CASE WHEN P.IMPACTID = 'Critical' THEN PP.TITLE END) AS 'Critical Patches'
-- , GROUP_CONCAT(CASE WHEN P.IMPACTID = 'Recommended' THEN PP.TITLE END) AS 'Recommended Patches'
-- end debugging lines
FROM PATCHLINK_MACHINE_STATUS PMS
JOIN KBSYS.PATCHLINK_PATCH P on P.UID = PMS.PATCHUID
JOIN MACHINE M on M.ID = PMS.MACHINE_ID
JOIN KBSYS.PATCHLINK_PATCH PP on PP.UID = PMS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
WHERE PMS.STATUS = "NOTPATCHED"
and PP.IS_SUPERCEDED = 0
and PPS.STATUS = 0
GROUP BY M.NAME
order by Total desc,M.Name - Tim_Lawhead 5 years ago
