2 Replies Latest reply on Jun 21, 2016 12:13 PM by bob.mier

    Report needed to identify missing security patches as well as the number of days since each patch became available.

    bob.mier Apprentice

      I've been asked to create a report from LANDesk (9.6 SP2) that will provide missing patches by name, the number of days since the patch publish date, and a count of affected devices.  I'm trying to figure out how to get to the affected devices within a query; as I'm thinking that is the proper place to start.  Assistance in creating this report would be greatly appreciated.

        • 1. Re: Report needed to identify missing security patches as well as the number of days since each patch became available.
          Tanner Lindsay SupportEmployee

          While I don't have a report for you, if you are crafting your own report, you probably want to look at the CVDetected table, which contains information about what vulnerabilities are detected on what devices, and the Vulnerability table, which contains information about the vulnerabilities. You can also probably try using CVDetectedV, which is a view, containing much of this information.

           

          There is also the point you mention of "missing patches by name" - are you meaning the actual name of the binary file that needs to be run, or the common name of the patch/vulnerability, like MS16-005?

           

          Here is a rough SQL query to perhaps get you started:

           

          select max(Vulnerability_Idn) as Vulnerability_Idn,

            max(Vul_ID) as Vul_ID,

            max(PublishDate) as PublishDate,

            DATEDIFF(day,max(PublishDate),GetDate()) as DaysSincePublish,

            Datediff(day,min(DateDetected),GetDate()) as DaysSinceFirstDetection,

            count(distinct computer_idn) as AffectedComputers

          from CVDetectedV

          group by Vulnerability_Idn

           

          This one won't show you the exact patch name (like windows6.1-kb2688338-x64.msu) because I personally don't find that helpful. I just want to know it is vulnerable, then I'll have LANDESK install the right patch file. However it also includes the number of days since it was first detected in your environment, along with the days since published.