How to open and work with (dot) Nessus 5 Reports in Excel 2010

0

I have been working to find a functional way to quickly move from a completed Nessus scan into excel without a lot of trouble. The goal of bringing the files into Nessus is to make the data more usable and more report friendly. I wanted to avoid messing with transforming the original XML data into csv or even digging through the XML data to chop parts out.

The following assumes you are just using a single report or multiple reports you have consolidated using my previous post regarding consolidating multiple Nessus scans into a single XML file. The purpose of this post is to walk you through the steps to achieve the following:

  1. Import an already downloaded/saved .Nessus or .Nessus.XML file into Excel 2010
  2. Perform some simple changes to the table to make the data useful for further analysis and reporting.
  3. Create some simple charts using the data you have mined out of the overall total report.

To begin:

  • Save the Nessus report to a standard folder on your hard drive.
    • The file should be either a .nessus or .nessus.xml version 2 file.
  • Open Excel 2010
    • Go to the “Data” tab
    • Select “From Other Sources” dropdown under the “Get External Data” section.
      • Choose “From XML Data Import”
From XML Data Import drop down in Excel 2010
      • Navigate to your Nessus report file and double click to open it.
    • When you open the file you will receive an error stating “The specified XML source does not refer to a schema. Excel will create a schema based on the XML source data”
      • Click “OK”
    • On the “Import Data” popup, select where you want to drop the data in. You can select from the current top corner cell or wherever you want it to end up. I will just select OK.
Import Data Window Excel 2010 during import
    • You should now see the spreadsheet filling in. During the loading period you will receive an error regarding data being truncated. This is expected so just select OK.
    • You should now have a spreadsheet containing all of the data from your report. However only a select portion of the data is actually useful for analysis and reporting. Let’s dig into the spreadsheet to hide what we do not need.
      • For my use case I do not need the first several columns of data which contain data regarding the settings for the scanning profile. I want to remove them up until column “R” which has a heading of “name6”. Select columns A-Q, right click the column bar and select “Hide”. If you want you can delete, however hiding the data is recommended.
Click here to hide columns in Excel 2010
      • I then want to remove all cells which do not contain a target IP address. Select the dropdown for “Name6” and uncheck “Blanks” located at the bottom. Click OK to confirm.
Use this window to remove blank IPs in Excel 2010
      • Feel free to retitle the name6 column to IP Address.
    • You should now start to see data appear. Next I want to hide all columns that do not have a risk level of Critical, High, Medium, or Low for additional research and chart making for example. I will use these ratings to find targets with a scan result which has a risk associated with it.
      • Locate a column with a header titled “risk_factor”. In my case it is column AH.
      • Click the dropdown and uncheck none and (blanks). Click OK to confirm.
Use this to choose the risk factors you want to display
      • Great, now you should have only rows that contain IP addresses as well as have a risk rating assigned to them. Next I want to hide the other unused columns so I can see what I have left to work with.
    • Work left to right CTRL+Clicking columns which are empty at this point. Once you have them selected right click and hide them like before.
    • In my case I hid S, T, X, AB, AC, AD, AE, AF, AG, and from AI on.
      • So at this point you can begin quantifying how many Critical, High, Medium, and Low risks the client has in their environment. To add these items up use the following Excel formula to count the total times it sees one of those 4 options using a couple empty cells. In my case AH is the risk_factor column, AH1 is where the data starts, and AH113008 is where the data ends.
        • =COUNTIF(AH1:AH113008, “Critical”)
        • =COUNTIF(AH1:AH113008, “High”)
        • =COUNTIF(AH1:AH113008, “Medium”)
        • =COUNTIF(AH1:AH113008, “Low”)
      • With the data now somewhere on your sheet it should look like the following:
Countif will help count target words for reporting
      • I set the Low count to display the formula for this example.

From here you could take this and turn it into a nifty pie chart or other kind of executive reporting item. As a note, make sure you compare your results against what you find actually in Nessus by opening the report and comparing results.

Using similar techniques you can use the dropdowns to find other interesting data points to include in your report such as if Metasploit has modules available for a finding. For each different measure you want to take against the data set you will need to recheck the previously targeted measure. In the case above I would recheck the items for “risk_factor” to display every row with an IP Address in it.

With these methods you can also create more concise coverage of groups of systems. Sometimes you do not need the look of the default Nessus reports, but instead need a way to port the target IP list of systems running very outdated versions of OpenSSH. (i.e. under pluginName9 we would look for “OpenSSH < 3.6.2 Reverse DNS Lookup Bypass”)  With this technique you can then Hide/Unhide necessary columns until you have the data set you need and either print it direct to hand off to a department lead or copy/paste it into word for consolidating with other chunks of data.

I would love to get your feedback and any other recommendations you might have on how to improve the data analysis piece using other functions within Excel.

Leave a comment


Name*

Email(will not be published)*

Website

Your comment*

Submit Comment

 

© Copyright Ryker's Blog - Theme by Pexeto