1 Reply Latest reply on Oct 5, 2011 2:39 PM by sonic

    MVM DB Query

      Hi all:

       

      I am running verion 7.0 any trying to run a query against the DB.  My goal is to display a list IP addresses with associated vulnerabilities from the lastest scan only.  I don't want any historic or discovery scan data.

       

      Thanks in advance - Claud

        • 1. Re: MVM DB Query
          sonic

           

           

          Below is the TSQL for what you want including some additional information about the host and vulnerabilities.  Enjoy.

           

          /****************************************************************************

          * TSQL query to pull the vulnerabilities for the latest run of a scan

          * In the line SET @ScanName = 'XXXXXXX'

          * replace XXXXXXX with the name of a scan

          * Example: the line will look like the following...

          * SET @ScanName = 'Corporate Internal Scan'

          *

          * Compatibility: MVM 7.0 Only

          ****************************************************************************/

          DECLARE @ScanName VARCHAR(128)

           

          --Change the value of 'XXXXXXX' below to the name of a scan

          SET @ScanName = 'XXXXXXX'

           

          SELECT  dbo.fsLong2IP(Hosts.IPAddress) AS [IPAddress] ,

                  Hosts.DNSName ,

                  Hosts.NBName ,

                  Content.Vuln.FaultlineID ,

                  Content.Vuln.CVE ,

                  Content.Vuln.Name ,

                  Content.Vuln.Risk ,

                  Hosts.OSName

          FROM    VulnsFound

                  JOINHosts ON VulnsFound.HostID = Hosts.HostID

                  JOINContent.Vuln ONVulnsFound.FaultlineID =Content.Vuln.FaultlineID

          --The WHERE clause looks up the JobID of the most recent 'Complete' scan

          WHERE   VulnsFound.JobID = ( SELECT MAX(Jobs.JobID)

                                       FROM   ScanConfigurationsSnapshot

                                              JOIN jobs ONScanConfigurationsSnapshot.JobID = Jobs.JobID

                                       WHERE  jobs.State = 'Complete'

                                              AND ScanConfigurationsSnapshot.ConfigurationName = @ScanName

                                     )

          ORDER BY Hosts.IPAddress ,

                  Content.Vuln.Risk DESC