cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
joe.chen
Level 7
Report Inappropriate Content
Message 1 of 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

6 Replies
epo909
Level 9
Report Inappropriate Content
Message 2 of 7

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

joe.chen
Level 7
Report Inappropriate Content
Message 3 of 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

epo909
Level 9
Report Inappropriate Content
Message 4 of 7

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

joe.chen
Level 7
Report Inappropriate Content
Message 5 of 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

cgrim
Level 13
Report Inappropriate Content
Message 6 of 7

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
joe.chen
Level 7
Report Inappropriate Content
Message 7 of 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.

You Deserve an Award
Don't forget, when your helpful posts earn a kudos or get accepted as a solution you can unlock perks and badges. Those aren't the only badges, either. How many can you collect? Click here to learn more.

Community Help Hub

    New to the forums or need help finding your way around the forums? There's a whole hub of community resources to help you.

  • Find Forum FAQs
  • Learn How to Earn Badges
  • Ask for Help
Go to Community Help

Join the Community

    Thousands of customers use the McAfee Community for peer-to-peer and expert product support. Enjoy these benefits with a free membership:

  • Get helpful solutions from McAfee experts.
  • Stay connected to product conversations that matter to you.
  • Participate in product groups led by McAfee employees.
Join the Community
Join the Community