Custom SQL Report - Search Patch Titles for specific word and report in new column status of search.
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.
Answers (1)
It'd be nice if we could mark a comment as the answer for the question.
chucksteel gave me the solution in the comments:
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.
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',
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 5 years ago
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 - Tim_Lawhead 5 years ago