cancel
Showing results for 
Search instead for 
Did you mean: 
crusnac
Level 7

MVM DB Query

Jump to solution

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

0 Kudos
1 Solution

Accepted Solutions
sonic
Level 9

Re: MVM DB Query

Jump to solution

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

0 Kudos
1 Reply
sonic
Level 9

Re: MVM DB Query

Jump to solution

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

0 Kudos