/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


Query to detect unpatched machines

09/23/2014 2768 views
Does anyone happen to have a query that will return a list of un-patched computers.    I'm looking basically to recreate the results that are generated in the K1000 when you click, 'Security >> Patch Management >> Schedules >> Click a schedule.   At the bottom it shows the machines and their current patch status.  

I've been tasked with creating a report identifying machines that show errors, failures, or status other than 'completed'   

Patch Tasks 

NameIP AddressStatusPatch ResultsDate
AUS-WIN73201192.1.1.2error (Log Upload Failed)Patched: 0, Not Patched: 0, Detect Failures: 0 , Deploy Failures: 009/20/2014 02:10:34
ASQLCOMP1192.168.1.13error (Log Upload Failed)Patched: 0, Not Patched: 0, Detect Failures: 0 , Deploy Failures: 009/20/2014 02:10:33
CRONUTS192.168.1.14completedPatched: 132, Not Patched: 0, Detect Failures: 0 , Deploy Failures: 009/20/2014 02:12:00
DC567J11192.168.1.15error (Log Upload Failed)Patched: 0, Not Patched: 0, Detect Failures: 0 , Deploy Failures: 009/20/2014 02:10:34
2 Comments   [ + ] Show comments

Comments

  • Do you want a report showing machines where the status is not completed?
  • Actually I'm looking for any level of failure. Status <> 'completed' or Detect Failures > 0 or Deploy Failures > 0. (From what I understand 'Not Patched' is not considered a failure, so I was going to ignore those)

    I'm really new to this, but if I understood how the tables joined and which tables contained the patch information I could probably muddle through it, but the I can't really find anything documenting the tables.

All Answers

0
Something like this maybe?

#PATCH RESULT SUMMARY BY DEVICE
SELECT 
    M.NAME AS NAME,
    M.IP AS 'Last Known IP',
    M.USER_FULLNAME AS 'Last User Logged On',
    KT.PHASE AS STATUS,
    PS.PATCHED as 'Patched',
    PS.NOTPATCHED as 'Not Patched',
    PS.DETECT_FAILURES as 'Detect Failures',
    PS.LAST_RUN AS 'Last Patch Attempt (Time)',
    M.LAST_INVENTORY AS 'Last seen by KACE (Time)',
    DATE(M.LAST_REBOOT) AS 'Last Reboot (Date)'
FROM
    MACHINE M
        LEFT JOIN
    PATCHLINK_SCHEDULE_MACHINE_STATUS PS ON PS.MACHINE_ID = M.ID
        LEFT JOIN
    PATCHLINK_SCHEDULE PSS ON PSS.ID = PS.PATCHLINK_SCHEDULE_ID
        LEFT JOIN
    KBSYS.KONDUCTOR_TASK KT ON KT.KUID = M.KUID
WHERE
    KT.TYPE = PSS.KONDUCTOR_TASK_TYPE
            and KT.PHASE != 'COMPLETED'
ORDER BY STATUS
Answered 10/23/2018 by: cblake
Red Belt

  • I have little knowledge of writing SQL queries. Is there a way to add the WHERE P.DESCRIPTION= 'patch schedule name' to this query? I'd like to be able to separate the report based on the patch schedule name OR the patch label name.
    • You might consider reaching out to Professional Services to scope some 1-1 time for this and other tasks/projects you're working on. We can do work for you, or guide you and learn along the way, or do pure training. A lot of options exist (including custom options). https://support.quest.com/kace-systems-management-appliance/professional-services
 
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