/build/static/layout/Breadcrumb_cap_w.png
09/23/2014 2258 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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share