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



          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 ,


          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