Miscellaneous : Custom Patch Completion Rate Report by Smart Label

Custom Patch Completion Rate Report by Smart Label

We have dozens of "agencies" within our environment, and I'm trying to figure out how to run a report that shows overall Patching success rates(percentage) by agency(each agency has an associated device Smart Label). I am a SQL novice (at best) so please be gentle ;-). The results I'm looking for would be something along the line of this:

Appreciate any guidance!

1 Comment [ + ] Show comment

Can you define “success”? Is it that a patch deployment has run on a system, has completed on a system, or that patches are up to date? - Hobbsy 7 years ago
- Hobbsy - I am looking for compliance. Here is the SQL that my predecessor created that gives very close to the desired results, except this report uses the domain field to break out the results, I'm looking to use a Smart Label:
  
  SELECT M.CS_Domain as Domain,
  #M.NAME AS MACHINE_NAME, M.IP AS IP_Address, M.CS_Domain as Domain,
  # PP.title, MS.*
  count(distinct case when PP.impactid = 'Critical' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Critical,
  # count(case when PP.impactid = 'Critical' and MS.STATUS = 'PATCHED' then PP.uid end) as x,
  # COUNT(distinct MS.MACHINE_ID) as b,
  # SUM(MS.STATUS='PATCHED') as c,
  ROUND((count(case when PP.impactid = 'Critical' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Critical' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Critical_Compliance,
  count(distinct case when PP.impactid = 'Recommended' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Recommended,
  ROUND((count(case when PP.impactid = 'Recommended' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Recommended' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Recommended_Compliance,
  count(distinct case when PP.impactid = 'Software' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Software,
  ROUND((count(case when PP.impactid = 'Software' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Software' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Software_Compliance,
  count(distinct case when MS.Status = 'NOTPATCHED' then PP.uid end) AS Total_Pending,
  ROUND((SUM(MS.STATUS='PATCHED')/COUNT(MS.MACHINE_ID)) * 100, 2) AS Overall_Compliance
  #PP.IDENTIFIER as KB_ARTICLE, PP.RELEASEDATE as Release_Date
  
  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 PATCHLINK_PATCH_LABEL_JT PPLJT ON PPLJT.PATCHUID = PP.UID
  JOIN LABEL PL ON PL.ID = PPLJT.LABEL_ID
  JOIN MACHINE M ON M.ID = MS.MACHINE_ID
  JOIN MACHINE_LABEL_JT MLJT ON MLJT.MACHINE_ID = M.ID
  JOIN LABEL L ON L.ID = MLJT.LABEL_ID
  
  WHERE
  
  #L.NAME like '%Patch Group - Wednesday Night%'
  
  ( PL.NAME like 'Windows OS' or PL.NAME like 'MS Applications' or PL.NAME like '.NET Framework')
  
  #AND MS.STATUS != 'PATCHED'
  
  #and M.IP = '10.84.1.28'
  
  #AND PPS.STATUS in (0)
  
  group by M.CS_Domain
  
  order by M.CS_Domain
  
  I created a test label that included the Smart Label 'wildcard' I'm looking for, and here is the SQL from that:
  
  SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE WHERE ((( exists (select 1 from LABEL, MACHINE_LABEL_JT where MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID AND MACHINE_LABEL_JT.LABEL_ID = LABEL.ID AND LABEL.TYPE <> 'hidden' and LABEL.NAME like 'Agency -%')) ))
  
  So basically, I'd like to replace the domain entries with the Smart Label above, or Smart Label begins with 'Agency - '
  
  My SQL is weak, any help would be very much appreciated! - stickman00 7 years ago

Answers (1)

Answer Summary:

Posted by: chucksteel 7 years ago

Top Answer

If your labels are in a label group this is possible. You will need the ID of the label group (login to the K1000 using /adminui path and it will be in the URL when you edit the label group). Once you have that ID (in my examples below the label group ID is 439) you will need to add the following join statements to the query:

LEFT JOIN LABEL AGENCY ON (AGENCY.ID = MLJT.LABEL_ID) LEFT JOIN LABEL_LABEL_JT AGENCY_JT ON (AGENCY.ID = AGENCY_JT.CHILD_LABEL_ID AND AGENCY_JT.LABEL_ID = 439)

This creates the links to the label table with an alias of AGENCY that should contain the labels in the label group with ID 439. You need to also add this statement in your where clause:

AGENCY_JT.LABEL_ID = 439

For some reason the join statement doesn't perform the limit as expected, this clears that up.

Now you can select the Agency's name when selecting the columns:

DISTINCT(AGENCY.NAME) AS AGENCY

And this allows us to group by the name and sort by it, too. Replace your current group and order by statements with these:

GROUP BY AGENCY
ORDER BY AGENCY

Good luck.

Comments:

Chuck - so in my case, the label group I created where 'Label Name begins with Agency - ' is 591

Please forgive my ignorance, but would I then replace the first line of my query with:

LEFT JOIN LABEL AGENCY ON (AGENCY.ID = MLJT.LABEL_ID) LEFT JOIN LABEL_LABEL_JT AGENCY_JT ON (AGENCY.ID = AGENCY_JT.CHILD_LABEL_ID AND AGENCY_JT.LABEL_ID = 591)

or would it go elsewhere in the query? Also, where would the DISTINCT(AGENCY.NAME) AS AGENCY line go?

Really sorry to ask probably basic SQL questions, but I truly am green at this it. - stickman00 7 years ago
- You also need to change the part that goes in the where clause of the statement. After this line:
  #AND PPS.STATUS in (0)
  add this:
  AND AGENCY_JT.LABEL_ID = 591
  
  The first part of the sql statement is the select clause, this is where you specify which columns will be returned in the results, that is where you need to add the DISTINCT(AGENCY.NAME) AS AGENCY. It's a comma separated list, so you could put it after the M.CS_Domain as Domain, and don't forget the comma after it. So your first line will look like this:
  SELECT M.CS_Domain as Domain, DISTINCT(AGENCY.NAME) AS AGENCY, - chucksteel 7 years ago
  - Thanks so much for the help - I'm still not clear as to where to add the Left Join statement - I tried adding it as the second or last line of the FROM statement, but it is returning syntax errors. Here is my current query (I was wrong about the 591, it is actually 216):
    
    SELECT M.CS_Domain as Domain, DISTINCT(AGENCY.NAME) AS AGENCY,
    #M.NAME AS MACHINE_NAME, M.IP AS IP_Address, M.CS_Domain as Domain,
    # PP.title, MS.*
    count(distinct case when PP.impactid = 'Critical' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Critical,
    # count(case when PP.impactid = 'Critical' and MS.STATUS = 'PATCHED' then PP.uid end) as x,
    # COUNT(distinct MS.MACHINE_ID) as b,
    # SUM(MS.STATUS='PATCHED') as c,
    ROUND((count(case when PP.impactid = 'Critical' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Critical' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Critical_Compliance,
    count(distinct case when PP.impactid = 'Recommended' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Recommended,
    ROUND((count(case when PP.impactid = 'Recommended' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Recommended' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Recommended_Compliance,
    count(distinct case when PP.impactid = 'Software' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Software,
    ROUND((count(case when PP.impactid = 'Software' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Software' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Software_Compliance,
    count(distinct case when MS.Status = 'NOTPATCHED' then PP.uid end) AS Total_Pending,
    ROUND((SUM(MS.STATUS='PATCHED')/COUNT(MS.MACHINE_ID)) * 100, 2) AS Overall_Compliance
    #PP.IDENTIFIER as KB_ARTICLE, PP.RELEASEDATE as Release_Date
    
    FROM PATCHLINK_MACHINE_STATUS MS
    LEFT JOIN LABEL AGENCY ON (AGENCY.ID = MLJT.LABEL_ID) LEFT JOIN LABEL_LABEL_JT AGENCY_JT ON (AGENCY.ID = AGENCY_JT.CHILD_LABEL_ID AND AGENCY_JT.LABEL_ID = 216)
    JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
    JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
    JOIN PATCHLINK_PATCH_LABEL_JT PPLJT ON PPLJT.PATCHUID = PP.UID
    JOIN LABEL PL ON PL.ID = PPLJT.LABEL_ID
    JOIN MACHINE M ON M.ID = MS.MACHINE_ID
    JOIN MACHINE_LABEL_JT MLJT ON MLJT.MACHINE_ID = M.ID
    JOIN LABEL L ON L.ID = MLJT.LABEL_ID
    
    WHERE
    
    #L.NAME like '%Patch Group - Wednesday Night%'
    
    ( PL.NAME like 'Windows OS' or PL.NAME like 'MS Applications' or PL.NAME like '.NET Framework')
    
    #AND MS.STATUS != 'PATCHED'
    
    #and M.IP = '10.84.1.28'
    
    #AND PPS.STATUS in (0)
    AND AGENCY_JT.LABEL_ID = 216
    
    GROUP BY AGENCY
    
    ORDER BY AGENCY
    
    Thanks! - stickman00 7 years ago
  - The joins rely on the previous join to the MACHINE_LABEL_JT table, so they need to be placed after that one. Also, that should be two separate lines, my formatting got messed up when I pasted. - chucksteel 7 years ago
chucksteel, I'm going to figure out a way to email you a beer after all of this!

So here is my query with your edits where I believe you are wanting them to go - when I run it, Kace returns an error:

SELECT M.CS_Domain as Domain, DISTINCT(AGENCY.NAME) AS AGENCY,
#M.NAME AS MACHINE_NAME, M.IP AS IP_Address, M.CS_Domain as Domain,
# PP.title, MS.*
count(distinct case when PP.impactid = 'Critical' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Critical,
# count(case when PP.impactid = 'Critical' and MS.STATUS = 'PATCHED' then PP.uid end) as x,
# COUNT(distinct MS.MACHINE_ID) as b,
# SUM(MS.STATUS='PATCHED') as c,
ROUND((count(case when PP.impactid = 'Critical' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Critical' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Critical_Compliance,
count(distinct case when PP.impactid = 'Recommended' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Recommended,
ROUND((count(case when PP.impactid = 'Recommended' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Recommended' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Recommended_Compliance,
count(distinct case when PP.impactid = 'Software' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Software,
ROUND((count(case when PP.impactid = 'Software' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Software' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Software_Compliance,
count(distinct case when MS.Status = 'NOTPATCHED' then PP.uid end) AS Total_Pending,
ROUND((SUM(MS.STATUS='PATCHED')/COUNT(MS.MACHINE_ID)) * 100, 2) AS Overall_Compliance
#PP.IDENTIFIER as KB_ARTICLE, PP.RELEASEDATE as Release_Date

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 PATCHLINK_PATCH_LABEL_JT PPLJT ON PPLJT.PATCHUID = PP.UID
JOIN LABEL PL ON PL.ID = PPLJT.LABEL_ID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN MACHINE_LABEL_JT MLJT ON MLJT.MACHINE_ID = M.ID
LEFT JOIN LABEL AGENCY ON (AGENCY.ID = MLJT.LABEL_ID)
LEFT JOIN LABEL_LABEL_JT AGENCY_JT ON (AGENCY.ID = AGENCY_JT.CHILD_LABEL_ID AND AGENCY_JT.LABEL_ID = 216)
JOIN LABEL L ON L.ID = MLJT.LABEL_ID

WHERE

#L.NAME like '%Patch Group - Wednesday Night%'

( PL.NAME like 'Windows OS' or PL.NAME like 'MS Applications' or PL.NAME like '.NET Framework')

#AND MS.STATUS != 'PATCHED'

#and M.IP = '10.84.1.28'

#AND PPS.STATUS in (0)

AND AGENCY_JT.LABEL_ID = 216

GROUP BY AGENCY

ORDER BY AGENCY

Here is the Kace error:

mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DISTINCT(AGENCY.NAME) AS AGENCY, #M.NAME AS MACHINE_NAME, M.IP AS IP_Address, M' at line 1] in EXECUTE( "SELECT M.CS_Domain as Domain, DISTINCT(AGENCY.NAME) AS AGENCY, #M.NAME AS MACHINE_NAME, M.IP AS IP_Address, M.CS_Domain as Domain, # PP.title, MS.* count(distinct case when PP.impactid = 'Critical' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Critical, # count(case when PP.impactid = 'Critical' and MS.STATUS = 'PATCHED' then PP.uid end) as x, # COUNT(distinct MS.MACHINE_ID) as b, # SUM(MS.STATUS='PATCHED') as c, ROUND((count(case when PP.impactid = 'Critical' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Critical' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Critical_Compliance, count(distinct case when PP.impactid = 'Recommended' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Recommended, ROUND((count(case when PP.impactid = 'Recommended' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Recommended' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Recommended_Compliance, count(distinct case when PP.impactid = 'Software' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Software, ROUND((count(case when PP.impactid = 'Software' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Software' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Software_Compliance, count(distinct case when MS.Status = 'NOTPATCHED' then PP.uid end) AS Total_Pending, ROUND((SUM(MS.STATUS='PATCHED')/COUNT(MS.MACHINE_ID)) * 100, 2) AS Overall_Compliance #PP.IDENTIFIER as KB_ARTICLE, PP.RELEASEDATE as Release_Date 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 PATCHLINK_PATCH_LABEL_JT PPLJT ON PPLJT.PATCHUID = PP.UID JOIN LABEL PL ON PL.ID = PPLJT.LABEL_ID JOIN MACHINE M ON M.ID = MS.MACHINE_ID JOIN MACHINE_LABEL_JT MLJT ON MLJT.MACHINE_ID = M.ID LEFT JOIN LABEL AGENCY ON (AGENCY.ID = MLJT.LABEL_ID) LEFT JOIN LABEL_LABEL_JT AGENCY_JT ON (AGENCY.ID = AGENCY_JT.CHILD_LABEL_ID AND AGENCY_JT.LABEL_ID = 216) JOIN LABEL L ON L.ID = MLJT.LABEL_ID WHERE #L.NAME like '%Patch Group - Wednesday Night%' ( PL.NAME like 'Windows OS' or PL.NAME like 'MS Applications' or PL.NAME like '.NET Framework') #AND MS.STATUS != 'PATCHED' #and M.IP = '10.84.1.28' #AND PPS.STATUS in (0) AND AGENCY_JT.LABEL_ID = 216 GROUP BY AGENCY ORDER BY AGENCY LIMIT 0") - stickman00 7 years ago
- Looks like the DISTINCT function is causing the problem. It might not be necessary. I also cleaned up the commented lines to make things a bit more clear.
  
  SELECT M.CS_Domain as Domain, AGENCY.NAME AS AGENCY,
  count(distinct case when PP.impactid = 'Critical' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Critical,
  ROUND((count(case when PP.impactid = 'Critical' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Critical' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Critical_Compliance,
  count(distinct case when PP.impactid = 'Recommended' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Recommended,
  ROUND((count(case when PP.impactid = 'Recommended' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Recommended' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Recommended_Compliance,
  count(distinct case when PP.impactid = 'Software' and MS.STATUS = 'NOTPATCHED' then PP.uid end) as Software,
  ROUND((count(case when PP.impactid = 'Software' and MS.STATUS = 'PATCHED' then PP.uid end)/COUNT(case when PP.impactid = 'Software' and MS.MACHINE_ID then PP.uid end)) * 100, 2) AS Software_Compliance,
  count(distinct case when MS.Status = 'NOTPATCHED' then PP.uid end) AS Total_Pending,
  ROUND((SUM(MS.STATUS='PATCHED')/COUNT(MS.MACHINE_ID)) * 100, 2) AS Overall_Compliance
  
  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 PATCHLINK_PATCH_LABEL_JT PPLJT ON PPLJT.PATCHUID = PP.UID
  JOIN LABEL PL ON PL.ID = PPLJT.LABEL_ID
  JOIN MACHINE M ON M.ID = MS.MACHINE_ID
  JOIN MACHINE_LABEL_JT MLJT ON MLJT.MACHINE_ID = M.ID
  LEFT JOIN LABEL AGENCY ON (AGENCY.ID = MLJT.LABEL_ID)
  LEFT JOIN LABEL_LABEL_JT AGENCY_JT ON (AGENCY.ID = AGENCY_JT.CHILD_LABEL_ID AND AGENCY_JT.LABEL_ID = 216)
  JOIN LABEL L ON L.ID = MLJT.LABEL_ID
  
  WHERE
  
  ( PL.NAME like 'Windows OS' or PL.NAME like 'MS Applications' or PL.NAME like '.NET Framework')
  
  AND AGENCY_JT.LABEL_ID = 216
  
  GROUP BY AGENCY
  
  ORDER BY AGENCY - chucksteel 7 years ago
  - You sir, are a lifesaver! That worked beautifully! Thanks for bailing me out! - stickman00 7 years ago

Answer this question

Posted by:

View more:

Miscellaneous Questions

question

Custom Patch Completion Rate Report by Smart Label

Answers (1)

Posted by:

View more:

Related Questions

Related Links

Related Posts

Miscellaneous

Custom Patch Completion Rate Report by Smart Label

Answers (1)

Posted by:

Don't be a Stranger!

View more:

Related Questions

Related Links

Related Posts

Share