/build/static/layout/Breadcrumb_cap_w.png
03/13/2019 290 views

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 NameUser# of Missing Patches Recommended# of Missing Patches CriticalTotal Missing Patches
Comp ABob Smith437
Comp BJane Doe336
Comp CSerge Storms335


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.


  

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0

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.


Answered 03/14/2019 by: chucksteel
Red Belt

  • 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