/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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:

vwT0O3.png

Appreciate any guidance!
Answer Summary:
Cancel
1 Comment   [ - ] Hide Comment

Comments

  • 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 - 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!
Please log in to comment

Answer Chosen by the Author


Answers

1
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.

Answered 11/03/2017 by: chucksteel
Red Belt

  • 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.
    • 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,
      • 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!
      • 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.
  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • 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")
    • 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
      • You sir, are a lifesaver! That worked beautifully! Thanks for bailing me out!
Please log in to comment
Answer this question or Comment on this question for clarity

Answers