6 Replies Latest reply on Nov 11, 2009 6:58 PM by joe.chen

    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

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

          Hi,

           

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

           

          RD

          • 2. 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

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

              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

              • 4. 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
                • 5. 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

                  • 6. 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.