Seeking KACE Report assistance querying security patch information for all machines.
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
Have you tried using the Report Wizard? Create one with the Topics in the following order: Device, Patch Detects and Deployments, Patch. You will want to filter patches by those enabled/active (to not get every patch ever).

Depending on how many computers are in your environment and how many patches you're detecting, this report could be HUGE and take a little while. (e.g. This report in my environment would result in about 30,000 rows)
Answered 03/16/2017 by: JasonEgg
Tenth Degree Black Belt

  • Thank you Jason for your assistacne, Do you have any SQL query for that ?
    • I do, and will paste below, but I STRONGLY suggest you try with the report wizard: it is a nice tool that is relatively easy-to-use.
Please log in to comment
0
This query assumes you are not using superseded patches. The report does not check the software table, only the patch table(s). That means it will reflect the state of the computer the last time a patch schedule was run on it (NOT the last time it checked in).
SELECT 
    M.ID AS MACHINE_ID,
    M.NAME,
    M.OS_NAME,
    M.LAST_SYNC,
    PP.TITLE,
    MS.STATUS,
    MS.STATUS_DT,
    MS.DEPLOY_STATUS,
    MS.DEPLOY_STATUS_DT,
    MS.DEPLOY_ATTEMPT_COUNT,
    PEC.ERROR_DESCR
FROM
    PATCHLINK_MACHINE_STATUS MS
        LEFT JOIN
    MACHINE M ON (M.ID = MS.MACHINE_ID)
        LEFT JOIN
    KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
        LEFT JOIN
    KBSYS.PATCHLINK_ERROR_CODE PEC ON (PEC.ID = MS.DEPLOY_ERROR_CODE)
        OR (PEC.ID = MS.DETECT_ERROR_CODE)
WHERE
PP.TYPE = 1 AND PP.IS_SUPERCEDED = 0
GROUP BY M.KUID , PP.TITLE
ORDER BY M.NAME , PP.TITLE , MS.DEPLOY_STATUS_DT DESC
As I said before, something similar to this can be generated by the Report Wizard so I suggest becoming familiar with that tool.
Answered 03/17/2017 by: JasonEgg
Tenth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share