4 Replies Latest reply on May 24, 2011 8:06 AM by zman

    Autofix Reporting

    Rookie

      Hello

       

      I thought I would open a new discussion rather than resurrect this old http://community.landesk.com/support/message/19830  one. I am try to get some sort of reporting on autopatching.  Basically which machines where successfully patched and which weren't.

       

      The above link suggests that you have to create a SQL script to achieve this. I have asked my DB guys and they have kindly written one for me, however it seems that the computer vulnerabilities table is empty. Why would this be?

       

      Andrew

        • 1. Re: Autofix Reporting
          dwagner1 Apprentice

          Andrew,

           

          The vulnerabilities are listed in the dbo.Vulnerability table.  We are running v9 sp2

           

          Here is a query that I wrote that may help...

           

          I'm looking at the vulnerabilities and not looking at computers in the "Domain/Servers" group...etc...that are MS patches and over 30days old.

           

          select c.computerLocation, c.DisplayName,
          v.Vul_ID, v.publishDate,
          c.PrimaryOwner, cv.DateDetected, cv.PatchInstallSucceeded
          from vulnerability v, cvdetected cv, computer c
          where v.Vulnerability_Idn = cv.Vulnerability_Idn
          and c.Computer_idn = cv.computer_idn
          and c.computerLocation not like 'Domain/Servers/%'
          and c.computerLocation not like '%ADGroup%'
          and v.Vul_ID like 'MS%'
          and v.publishDate <= (getDate() - 30) order by c.computerLocation

           

          I'm also running Office 2010 and have the PowerPivot add-in installed.  You can enter this query in the PowerPivot (Excel) and click the "Refresh button" to update the data.  Then you can easily create a pivot table with the names of the computers and the number of vulnerabilites per computer...

           

          Pretty handy for what I use it for....

           

          Hope that helps!

           

          Darin

           

          Message was edited by: Darin Wagner

          • 2. Re: Autofix Reporting
            Rookie

            We are also using 9.0 SP2 , this is the the DBA guys created for me, it already has the dbo.vulnerability listed.

             

            select C.DeviceName,CM.*,'----',V.* from dbo.Vulnerability V,COMPUTERVULNERABILITY CM,dbo.Computer C

            where V.autofix=1

            and V.VUL_ID =CM.VUL_Id

            and C.Computer_Idn = CM.Computer_Idn

            and cm.Detected =1

            order by C.DeviceName

            • 3. Re: Autofix Reporting
              dwagner1 Apprentice

              Andrew,

               

              Are you stating that when you run your sql you do not get any results?

              • 4. Re: Autofix Reporting
                zman Master

                Depending on your LANDesk version, hopefully you have 9.0 SP2, if you have installed the latest MCPs, security and patch have numerous new dashboards that should provide you with this information. Also, in SP2 you can take the excellent SQL provided in the other responses and create a very nice looking LANDesk custom report without using SQL. http://elearning.landesk.com/LAN-E-MS9-19/LDMS-LDSS-V9-SP2-Reporting.html

                 

                _____________________________________________________________
                Please consider voting for these ERs:
                Query Builder Import
                http://community.landesk.com/support/ideas/1794
                Provide bulk client deletions without carpal tunnel syndrome.
                http://community.landesk.com/support/ideas/2293
                Core Synchronization Allow Mirror functionality from Master Core
                http://community.landesk.com/support/ideas/2292