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:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

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

Answers

Answer this question or Comment on this question for clarity