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

    Autofix Reporting




      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?



        • 1. Re: Autofix Reporting
          dwagner1 Apprentice



          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!




          Message was edited by: Darin Wagner

          • 2. Re: Autofix Reporting

            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



              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
                Provide bulk client deletions without carpal tunnel syndrome.
                Core Synchronization Allow Mirror functionality from Master Core