Creating a K1000 report that will list all machines that are part of a specific label that have patches listed as critical missing
I am trying to create a report that will list all machines that are part of a specific label that have patches listed as critical missing. The report is running correctly, with the exception that it seems to only gather that data from the first label and then quit. I would like it to gather the data from all of the listed labels. I am fairly new to SQL and would appreciate any help on this.
Here is what I have so far:
Select M.NAME as MACHINE_NAME, M.USER, M.USER_FULLNAME, GROUP_CONCAT(PP.TITLE) AS PATCH_NAME,
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED
FROM ORG1.PATCHLINK_MACHINE_STATUS MS JOIN ORG1.MACHINE M ON M.ID = MS.MACHINE_ID
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN ORG1.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
where PP.IMPACTID = ('Critical') AND MS.STATUS='NOTPATCHED' AND PPS.STATUS = 0 /* 0=active patches */
and (L.NAME LIKE ('Win 7 - CIO Org') or ('Win XP - CIO Org') OR ('Win Workstations (North America)'))
group by MACHINE_NAME
order by MACHINE_NAME, PP.TITLE
Answers (1)
Try changing this line:
and (L.NAME LIKE ('Win 7 - CIO Org') or ('Win XP - CIO Org') OR ('Win Workstations (North America)'))
To this:
and (L.NAME RLIKE 'Win 7 - CIO Org|Win XP - CIO Org|Win Workstations (North America)'))
Since REGEX (RLIKE) will match unique strings, this may also work (depending on what other labels you have):
and L.NAME RLIKE 'CIO|North A'))
John
Comments:
-
Thank you for the suggestion. Here is what I found when trying your method.
The above line works for the Win 7 - CIO Org|Win XP - CIO Org labels and returns the correct combined value. When I add |Win Workstations (North America) to the query it returns the same value as the previous query not containing |Win Workstations (North America).
Additionally, if I run the following query it returns nothing.
and (L.NAME RLIKE 'Win Workstations (North America)')
However, if I run it as follows it returns the correct amount of rows.
and L.NAME LIKE 'Win Workstations (North America)'
I am not sure if it matters but the labels are the following types:
Win Workstations (North America) = LDAP label
Win 7 - CIO Org = regular (manual) label
Win XP - CIO Org = regular (manual) label - jparkins 11 years ago -
My guess would be that it has something to do with it being an LDPA label and they way that they are applied only once checking in. Interesting result that will need some testing. - jdornan 11 years ago
-
I've run into instances of REGEX (RLIKE) not handling long strings well as well as certain characters (space, parentheses) causing unexpected behavior. If it works when you use the "LIKE" version, I'd substitute these two lines for the the one I suggested and see where this gets you:
and (L.NAME RLIKE 'Win 7 - CIO Org|Win XP - CIO Org'
or L.NAME = 'Win Workstations (North America)'))
John - jverbosk 11 years ago
