/build/static/layout/Breadcrumb_cap_w.png

Query to detect unpatched machines

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
  • Do you want a report showing machines where the status is not completed? - chucksteel 9 years ago
  • 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. - rmavery 9 years ago

Answers (1)

Posted by: cblake 5 years ago
Red Belt
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

Comments:
  • 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. - edullum 5 years ago
    • 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 - cblake 5 years ago
 
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