/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


K1000 Reporting: need report of patch compliance by device, not by patch

06/03/2015 10577 views
Hello. The built in patching reports list every single patch by compliance. These reports are huge and list literally every single thousands of patches on every device. I'm looking for something which lists each device instead of each patch, and count of patches, something that is more similar to the "Critical Patch Compliance" Widget on the K1000 homepage but a little more specific. My ultimate goal is to have a report that lists every computer and the count or the percentage of patched vs not patched critical patches. So instead of a huge report with every single patch, I'd have a nice simple report listing every one of my ~130 computers and the percentage of patched or not patched. Even better if computers could be grouped by a specific label.

I'm not too familiar with the SQL code and for now confused as to what each item actually refers to but I'm trying. Is there a SQL legend which explains each item? Support says no. 

An example of what I'm not looking for is the report "Devices compliant by patch"
SELECT PP.TITLE AS DISPLAY_NAME,
M.NAME AS ComputerName,
SYSTEM_DESCRIPTION, IP, MAC,
M.USER_LOGGED as USER_LOGGED,
CS_DOMAIN
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE MS.STATUS = 'NOTPATCHED'
ORDER BY PP.TITLE
If anybody has a report like this and can offer some help that would be great. In the meantime I'm going to play around with this report which is closer to what I am looking for: 
https://support.software.dell.com/k1000-systems-management-appliance/kb/111710

Thanks.

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

3
I believe this report will capture what you want to report:
SELECT M.ID as Machine_ID, M.NAME AS Machine, M.KUID
, K.PHASE AS Phase, K.TYPE
, PSMS.PATCHED, PSMS.NOTPATCHED
, P.DESCRIPTION AS Description, P.LAST_RUN AS LAST_RUN
FROM MACHINE M
LEFT JOIN KBSYS.KONDUCTOR_TASK K ON K.KUID = M.KUID
LEFT JOIN PATCHLINK_SCHEDULE_MACHINE_STATUS PSMS ON PSMS.MACHINE_ID = M.ID
LEFT JOIN PATCHLINK_SCHEDULE P ON P.ID = PSMS.PATCHLINK_SCHEDULE_ID
WHERE K.TYPE = 'patch-ORG1-3'
GROUP BY M.NAME
ORDER BY M.NAME
This report only includes one patching schedule (we have several) so it will need to be modified to report on your patching schedule. This line:
WHERE K.TYPE = 'patch-ORG1-3' 
contains the ID of the patching schedule, in this case it is 3. You need to change the value of 3 to match the value of the patching schedule that you want to report on.

Answered 06/04/2015 by: chucksteel
Red Belt

  • Thanks Chuck. Good report. I also found the below report from this website which is basically exactly what I was looking for. It shows patched/unpatched counts plus a column for percentage patched.

    http://www.itninja.com/blog/view/k1000-reports-patching-reports-for-completion-by-patch-machine-vendor-using-labels

    SELECT M.NAME AS MACHINE_NAME,
    USER_NAME,
    OS_NAME AS OS_Name,
    SUM(MS.STATUS='PATCHED') AS PATCHED,
    SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
    ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
    +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
    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 MACHINE M ON (M.ID = MS.MACHINE_ID)
    JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
    JOIN LABEL L ON (ML.LABEL_ID = L.ID)
    WHERE PP.IMPACTID = 'Critical'
    AND PPS.STATUS = 0
    AND PP.IS_SUPERCEDED = 0
    AND L.NAME rlike 'All Production Computers'
    GROUP BY M.NAME
    ORDER BY PERCENT_PATCHED, M.NAME
    • Just what I was looking for. thanks
  • Chuck,
    When you say "WHERE K.TYPE = 'patch-ORG1-3'
    contains the ID of the patching schedule, in this case it is 3"
    Is "3" the name of the Patch Schedule? So if I had a schedule named "Windows Critical Patch Run" the SQL would look like WHERE K.TYPE = 'patch-ORG1-Windows Critical Patch Run' or "WHERE K.TYPE = 'Windows Critical Patch Run'.
    I couldn't get either to way to work for me, but does this still work on 6.4?
    • Each patch schedule has an ID number. If you login to your appliance using /adminui instead of /admin then you can see the ID when editing a patching schedule.
      • You must be using a K2000? Because I don't see that anywhere on my patch schedules in the K1000.
      • I'm not sure what to tell you. Every patch schedule has an ID associated with it. If you are using the /adminui interface it should be in the URL.
      • Oh I see what you mean about the ID showing up in the URL now. I was looking everywhere but the URL.
        Actually I figured this out by removing your WHERE K.TYPE = 'patch-ORG1-3' and running the report. Then I was able to create a different WHERE statement based on P.Description, which is the actual name of the Patch Schedule.
        Now the report created shows a Type, which is the K.Type field, either labeled as "inventory" or "dellinv-ORG1-#'. What is the difference between these types?

Community Chosen Answer

1

Hi guys,

does anyone already got this SQL query working on Kace V10?

And would it share?


Thanks, Christian
Answered 10/31/2019 by: CFassbender
White Belt

  • I have one that reports on critical patches for V10.
    SELECT
    M.NAME AS MACHINE_NAME,
    OS_NAME AS WINDOWS_VERSION,
    LAST_USER as Last_User,
    CLIENT_VERSION as Agent_Version,
    M.Notes as Notes,
    M.LAST_SYNC as Last_Inventory,
    M.LAST_REBOOT as Last_Re_Boot,
    SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,
    SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,
    FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS =
    'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS
    PERCENT_PATCHED
    FROM
    PATCH_MACHINE_STATUS MS
    JOIN KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
    JOIN PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
    JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
    JOIN KBSYS.SMMP_CONNECTION SC ON (M.KUID = SC.KUID)
    WHERE

    PPS.STATUS = 0
    and PP.SEVERITY = 'Critical'
    AND PPS.IS_SUPERCEDED = 0
    GROUP BY MS.MACHINE_ID
    ORDER BY PERCENT_PATCHED , M.NAME
    • Wow, many thanks, that is exactly, what I am looking for.

      To get recommended updates too, I changed the following line.

      PP.SEVERITY = 'Critical' OR PP.SEVERITY = 'Recommended'
    • Is this per last Detect/Deploy? Can we put in a time frame and say Last Month? Sorry, not SQL code savvy either
      • No, its an general overview of all requested Updates with the selected severity (critical, important,recommended, moderate, low) for the client. Feel free to change the PP.Severity like you want.

        If everything is working fine the report should show mostely 100%, if not, you have to check which requested patches/updates are missing.

        I use the following query to check the updates. including the systems last reboot and inventory. maybe it will help you.

        For time frame when only last client sync was done it should be:
        WHERE
        (Last_Sync > CURDATE() - INTERVAL 30 DAY) AND
        PPS.STATUS = 0

        For updates it should be Creation_Date:_

        WHERE (Creation_Date > CURDATE() - INTERVAL 30 DAY) AND
        PPS.STATUS = 0

        But I don´t think that updates within last 30 days is a good idea.

        Full query for last inventory (Last Sync of client 30 days ago):

        SELECT
        M.NAME AS Computername,
        OS_NAME AS Operatingsystem,
        CASE M.OS_BUILD
        WHEN '7600' THEN 'Windows 7'
        WHEN '7601' THEN 'Windows 7 SP1'
        WHEN '9600' THEN '2012R2'
        WHEN '10240' THEN '1507 (RTM)'
        WHEN '10586' THEN '1511'
        WHEN '14393' THEN '1607'
        WHEN '15063' THEN '1703'
        WHEN '16299' THEN '1709'
        WHEN '17134' THEN '1803'
        WHEN '17763' THEN '1809'
        WHEN '18362' THEN '1903'
        WHEN '18363' THEN '1909'
        ELSE 'Unknown OS Build'
        END
        AS 'Version',
        LAST_USER as Username,
        M.IP as IPAdress,
        CLIENT_VERSION as Agentversion,
        M.LAST_SYNC as LastInventory,
        M.LAST_REBOOT as LastReboot,
        SUM(MS.DETECT_STATUS = 'PATCHED') AS Installed,
        SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS Notinstalled,
        FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS =
        'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS
        Percent
        FROM
        PATCH_MACHINE_STATUS MS
        JOIN KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
        JOIN PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
        JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
        JOIN KBSYS.SMMP_CONNECTION SC ON (M.KUID = SC.KUID)
        WHERE (Last_Sync > CURDATE() - INTERVAL 30 DAY) AND
        PPS.STATUS = 0
        and PP.SEVERITY != 'low' AND PPS.IS_SUPERCEDED = 0
        GROUP BY MS.MACHINE_ID
        ORDER BY Percent , M.NAME
    • This is great, however it only shows for windows computers how do i get to show for macOS computers as well or just macOS computers?
      Sorry i cant see where to change the code for this
      • sorry just noticed the ones for macOS was not listed as critical which would explain this

All Answers

0

Hi,
This report was built for V10 Kace. It lists device, windows version, patched number, un-patched number minus our 12 day grace period for "new" patches, Percentage patched with the same 12 day grace period and device up time.  It breaks the group of devices into two manual label groups.    I thought you might find it handy to use and configure for your system.

SELECT
    M.NAME AS MACHINE_NAME,
    OS_NAME AS WINDOWS_VERSION,
    SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,
    SUM(MS.DETECT_STATUS = 'NOTPATCHED' AND DATE_FORMAT(PP.CREATION_DATE, '%Y-%m-%d') > DATE_SUB(NOW(),INTERVAL 12 DAY)) AS NOTPATCHED,
    FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS = 'PATCHED') + (SUM(MS.DETECT_STATUS = 'NOTPATCHED' AND DATE_FORMAT(PP.CREATION_DATE, '%Y-%m-%d') > DATE_SUB(NOW(),INTERVAL 12 DAY))))) * 100,1)) AS PERCENT_PATCHED,
(CONCAT(SUBSTRING_INDEX(M.UPTIME, ',', 1), ' days, ', SUBSTRING(M.UPTIME, LOCATE(',', UPTIME) + 1, LOCATE(':', M.UPTIME) - LOCATE(',', M.UPTIME) - 1), ' hours, ', SUBSTRING_INDEX(M.UPTIME, ':', -1), ' minutes')) AS UPTIME,

 LABEL.NAME AS LABEL

FROM
    PATCH_MACHINE_STATUS MS
        JOIN
    KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
        JOIN
    PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
        JOIN
    MACHINE M ON (M.ID = MS.MACHINE_ID)
        JOIN
    KBSYS.SMMP_CONNECTION SC ON (M.KUID = SC.KUID)

        JOIN
    MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = M.ID
        JOIN
    LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID

WHERE
    PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0
        AND PPS.IS_SUPERCEDED = 0
        AND PP.TITLE NOT RLIKE 'Silverlight|Java|Skype|Chrome|Flash|Adobe|7-Zip|Server Management Studio'
        AND SC.CLIENT_CONNECTED = 1
    and LABEL.NAME = "KACE Initial Server-Patching" or LABEL.NAME = "Critical Server Manual Label"
   
GROUP BY MS.MACHINE_ID
ORDER BY LABEL, UPTIME, PERCENT_PATCHED , M.NAME

Answered 02/24/2020 by: j.catlin@nmscolo.org
White Belt

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ