1 Reply Latest reply on Jul 26, 2010 10:22 AM by firstmidwestbank

    Vulnerability Report

    Apprentice

      All,

       

      Ok, I am slowly working my way through the iReporter program trying to create a simple report but having some issues.

       

      I would simply like a report that shows me a group of specific serves i.e. All Windows 2003 servers on this subnet 10.97.1.x.  In this list of servers I would like the computer name, os version, IP and COUNT of vulnerable patches.  That’s it.


      How do I calculate a count of vulnerable patches??  I am trying to work off of the canned report someone put out on the forum but this stuff is like reading Chinese.

       

      Any assistance would be greatly appreciated!

        • 1. Re: Vulnerability Report
          Apprentice

          I was able to work with a SQL dude here at work and this is what we came up with.  I am sure there are other ways but this worked for us.  If you want a simple report based on subnets and OS this will do it.

           

           

          SELECT DeviceName, COUNT(DeviceName) AS Total, CONVERT(CHAR, VALastScanDate) AS LastVulScan
          FROM Computer LEFT OUTER JOIN Operating_System ON Computer.Computer_Idn = Operating_System.Computer_Idn
          LEFT OUTER JOIN TCP ON Computer.Computer_Idn = TCP.Computer_Idn
          LEFT OUTER JOIN OSNT ON Computer.Computer_Idn = OSNT.Computer_Idn
          INNER JOIN CVDetected ON Computer.Computer_Idn = CVDetected.Computer_Idn
          WHERE Address LIKE 'x.x.x.%' AND OSType NOT LIKE '%Server%' AND
          CONVERT(DATETIME, InstallDate) < CONVERT(DATETIME, CONVERT(CHAR(2), MONTH(GETDATE())) + '/' + CONVERT(CHAR(2), DAY(GETDATE())) + '/' + CONVERT(CHAR(4), YEAR(GETDATE())))
          GROUP BY DeviceName, VALastScanDate
          ORDER BY Total DESC