/build/static/layout/Breadcrumb_cap_w.png
03/20/2019 221 views

I am currently working on extending the last report I was working on:

https://www.itninja.com/question/custom-sql-report-computers-w-user-missing-more-than-x-number-of-critical-amp-recommended-patches-showing-calculated-values-for-those-criteria-and-sorted-by-total-missing-patches


We are excluding Java and VMware Tools from patching via Smart Labels.  Some server applications are stuck with a specific version of Java and we prefer the VMware Tools to match the build deployed with our ESXi Hosts.  


Current script:

SELECT M.NAME, 

OS_NAME AS OS_Name,

CASE WHEN PP.TITLE LIKE 'VMware Tools%' THEN 'True' ELSE 'False' END AS 'VMware Tools Needed',

CASE WHEN LOWER(PP.TITLE) LIKE 'Oracle Java%'  THEN 'True' ELSE 'False' END AS 'Java Needed',

SUM(P.IMPACTID = 'Critical') AS 'Critical', 

SUM(P.IMPACTID = 'Recommended') as 'Recommended',

COUNT(P.IMPACTID) as 'Total'

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

-- Change the date below to match up with our patching window cutoff date

AND PP.RELEASEDATE <'2019-03-08' 

GROUP BY M.NAME

order by Total desc,M.Name


Issue I am seeing:

On 1 server I am getting a true and another a false for the Java column.  Both servers show the need for Java on them and the Java is not superseded, released before the cut-off date, .


The server that reports True to my query is showing the following Java patches missing:

Oracle Java SE Runtime Environment (JRE) 7 Update 80 (7.0.800.15) for Windows (See Notes)

        Oracle Java SE Runtime Environment (JRE) 8 Update 201 (8.0.2010.9) for Windows (See Notes)

Oracle Java SE Runtime Environment (JRE) 8 Update 202 (8.0.2020.8) for Windows (See Notes)


The server that reports False to my query is showing the following Java patches missing:

        Oracle Java SE Runtime Environment (JRE) 8 Update 201 (8.0.2010.9) for Windows (See Notes)

Oracle Java SE Runtime Environment (JRE) 8 Update 202 (8.0.2020.8) for Windows (See Notes)


The query for VMware Tools appears to be fine when I compare it against what I see in vCenter.  


Could a double positive create a negative in the query?  Appreciate any thoughts on how to get this sorted.


Edit:  2019-03-21

I found that when I add PP.TITLE to the query after the "OS_NAME as OS_NAME,  PP.TITLE,...." it is only returning 1 record and the true/false CASE query is relating to whatever is in the PP.TITLE column.   Looks like it isn't digging through all the patch titles that meet the criteria.  

2 Comments   [ + ] Show comments

Comments

  • My guess is that the grouping by machine name is causing the problem. It is probably returning the result of the last patch for each machine in those columns, but I'm not positive on that. To test that you can remove the grouping and include the patch title in the columns and see what the results are per patch per machine.

    Another thing you could try would be to use something like:
    CASE WHEN group_concat(PP.TITLE) like "%VMWare Tools% THEN 'True' ELSE 'False' END AS 'VMWare Tools Needed'

    That would check all of the patches in the machine group, instead of just one of them.
  • chucksteel - that did it. I've been Googling MYSQL queries this morning and trying to figure it out and was just reading about group_concat here: https://stackoverflow.com/questions/662207/mysql-results-as-comma-separated-list when an email alert popped up with your reply.

    Query that appears to be working:
    SELECT M.NAME as MACHINE_NAME,
    OS_NAME AS OS_Name,
    CASE WHEN group_concat(PP.TITLE) LIKE 'VMware Tools%' THEN 'True' ELSE 'False' END AS 'VMware Tools Needed',
    CASE WHEN group_concat(PP.TITLE) LIKE '%JAVA%' THEN 'True' ELSE 'False' END AS 'Java Needed',
    SUM(P.IMPACTID = 'Critical') AS 'Critical',
    SUM(P.IMPACTID = 'Recommended') as 'Recommended',
    COUNT(P.IMPACTID) as 'Total'
    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 /* 0=active patches */
    -- Change the date below to match up with our patching window cutoff date
    AND PP.RELEASEDATE <'2019-03-08'
    GROUP BY M.NAME
    order by Total desc,M.Name

All Answers

0

It'd be nice if we could mark a comment as the answer for the question.

chucksteel gave me the solution in the comments:



My guess is that the grouping by machine name is causing the problem. It is probably returning the result of the last patch for each machine in those columns, but I'm not positive on that. To test that you can remove the grouping and include the patch title in the columns and see what the results are per patch per machine. 

Another thing you could try would be to use something like:
CASE WHEN group_concat(PP.TITLE) like "%VMWare Tools% THEN 'True' ELSE 'False' END AS 'VMWare Tools Needed'

That would check all of the patches in the machine group, instead of just one of them.

Query that works:

CASE WHEN group_concat(PP.TITLE) LIKE 'VMware Tools%' THEN 'True' ELSE 'False' END AS 'VMware Tools Needed',
CASE WHEN group_concat(PP.TITLE) LIKE '%JAVA%' THEN 'True' ELSE 'False' END AS 'Java Needed',


Answered 03/21/2019 by: Tim_Lawhead
Senior White Belt