cancel
Showing results for 
Search instead for 
Did you mean: 
joe.chen
Level 7

How to generate a CSV or XML report for Top 15 Hosts + vulnerability lists

Hi Foundstone experts,

How can I use a SQL query to generate "Top 15 Active Hosts with list of vulnerabilities" from the scan names contain "ebiz"?

For example:

Sum_of_Vul_Count,System,Domain,OS,Vulnerability,Risk

5,WK8ABC,USA,Windows Server 2008,(MS08-067.......),High

5,WK8ABC,USA,Windows Server 2008,(MS09-001.......),High

5,WK8ABC,USA,Windows Server 2008,(MS09-002.......),Medium

5,WK8ABC,USA,Windows Server 2008,(MS09-003.......),High

5,WK8ABC,USA,Windows Server 2008,(MS09-004.......),High

3,WS8DEF,USA,Windows Server 2008,(MS08-067.......),High

3,WS8DEF,USA,Windows Server 2008,(MS09-001.......),High

3,WS8DEF,USA,Windows Server 2008,(MS09-002.......),Medium

2,WS8ACD,USA,Windows Server 2008,(MS09-001.......),High

2,WS8ACD,USA,Windows Server 2008,(MS09-002.......),Medium

Thank you in the advance.

Joe

0 Kudos
6 Replies
epo909
Level 9

Re: How to generate a CSV or XML report for Top 15 Hosts + vulnerability lists

Hi,

By 'System' you mean Netbios Name, DNS or Asset Label?

RD

0 Kudos
joe.chen
Level 7

Re: How to generate a CSV or XML report for Top 15 Hosts + vulnerability lists

Hi epo909,

I meant Netbios Name.

Thank you, epo909.

Joe

0 Kudos
epo909
Level 9

Re: How to generate a CSV or XML report for Top 15 Hosts + vulnerability lists

Hi again,

I have been playing around with the FS tables and unfortunately, because my SQL scripting is rusty, I were unable to produce a query with all elements you requested.

This was what I came up with:

This query lists what you asked, except counting the Vuln by host, for a given scan name:

-------------------------------------------------------------------

declare @myscan varchar(60)
set        @myscan = 'INSERT SCAN NAME HERE'
select    hosts.nbname as SYSTEM,
        hosts.osname as PLATFORM,
        hosts.nbworkgroup as DOMAIN,
        vulns.name as VULN,
        vulns.risk as RISK
from    vulnsfound,
        vulns,
        hosts,
        scanconfigurations,
        jobs
where    hosts.jobid = jobs.jobid
and        jobs.jobid in (
            select    max(jobid)
            from    jobs,
                    scanconfigurations
            where    jobs.configurationid = scanconfigurations.configurationid
            and        configurationname = @myscan
            and        state = 'complete'
)
and        scanconfigurations.configurationname = @myscan
and        jobs.configurationid = scanconfigurations.configurationid
and        vulnsfound.faultlineid = vulns.faultlineid
and        hosts.hostid = vulnsfound.hostid
and        state = 'complete'
order by    nbname asc,risk desc

This query lists the number of vulnerabilities per host for a given scanname

--------------------------------------------------------------------

declare @myscan varchar(60)
set        @myscan = 'INSERT SCAN NAME HERE'
select    top 15
        hosts.nbname,
        count(*) as cnt
from    vulnsfound,
        vulns,
        hosts,
        scanconfigurations,
        jobs
where    hosts.jobid = jobs.jobid
and        jobs.jobid in (
            select    max(jobid)
            from    jobs,
                    scanconfigurations
            where    jobs.configurationid = scanconfigurations.configurationid
            and        configurationname = @myscan
            and        state = 'complete'
        )
and        scanconfigurations.configurationname = @myscan
and        jobs.configurationid = scanconfigurations.configurationid
and        vulnsfound.faultlineid = vulns.faultlineid
and        hosts.hostid = vulnsfound.hostid
and        state = 'complete'
group by    hosts.nbname
order by    cnt desc

------------------------------------------------------

The trick would be to inner join both queries... however I don't really know how.

I did some testing with my own MVM data and it seems ok, and I hope its not buggy.

Please note that these queries will only return the vulns detected for the latest job for the given scan name.

Hope it helps.

RD

0 Kudos
joe.chen
Level 7

Re: How to generate a CSV or XML report for Top 15 Hosts + vulnerability lists

Thank you so much, RD.  I will be able to try it on Friday and let you know the result.

Joe

0 Kudos
cgrim
Level 13

Re: How to generate a CSV or XML report for Top 15 Hosts + vulnerability lists

Hi Joe,

We like to prevent manually touching the DB whenever possible, and for that reason I've opened an FMR (529422 ) on your behalf to add this functionality to the product.

Please be sure, if you are running queries against the DB, to backup early and often :-)

Now, having said all of that... here is a query that will give you a bit of both the below.  Mine has the inner join, but I'm not sure how to get the top 15 part.

declare @ScanConfigName varchar(50)
declare @JobNum varchar(50)

SET @ScanConfigName = '<Enter Your Scan Name Here>'
SET @JobNum = '<Enter Your Job # Here>'


--get the JobID based on the input parameters
declare @JobID int

SELECT @JobID = JobID
FROM Jobs j inner join scanconfigurations sc on j.ConfigurationID = sc.ConfigurationID
WHERE sc.ConfigurationName = @ScanConfigName AND j.JobName = @JobNum

--print @JobID


-- retrieve the data for the job

select sc_snapshot.ConfigurationName, j.JobName,
h.dnsname, h.OSName, h.NBName, h.NBWorkGroup,
(CASE WHEN v.Risk between 7 and 10 then 'HIGH VULN'
WHEN v.Risk between 4 and 6 then 'MED VULN'
WHEN v.Risk between 1 and 3 then 'LOW VULN'
WHEN v.Risk = 0 then 'INFO VULN'
ELSE 'UNKNOWN'
END) as VulnRisk,
v.Name as VulnName
FROM jobs j inner join scanconfigurationsSnapshot sc_snapshot on j.JobID = sc_snapshot.JobID
inner join Hosts h on j.JobID = h.JobID
inner join VulnsFound vf on h.HostID = vf.HostID inner join Vulns v on vf.FaultlineID = v.FaultlineID
where j.JobID = @JobID
order by IPAddress, v.risk DESC

I hope that helps!
Cathy

Message was edited by: Cathy Grim on 11/11/09 9:16 AM
0 Kudos
joe.chen
Level 7

Re: How to generate a CSV or XML report for Top 15 Hosts + vulnerability lists

Thank you so much for your feature request and query, Cathy, I'll be able to try it on Friday and let you know the result.

Joe.

0 Kudos