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

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

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

Answered 02/18/2013 by: jverbosk
Red Belt

  • 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
  • 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.
  • 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
Please log in to comment
Answer this question or Comment on this question for clarity

Share