Read .nessus file into Excel (with Power Query)

Objective

  • Read a .nessus file (hosts properties, vulnerability and compliance scan results) into excel.

How to

This procedure uses Excel Power Query which is an add-on if you use Excel < 2016.
We’ll create two queries (hosts and scanresults)

  1. Export and save your .nessus file
  2. Excel < 2016
    Open Excel > Power Query Tab >
    Excel => 2016
    Open Excel > Data > New Query >

    From File > From XML File > Browse to the location of the  .nessus file > All File (*.*) > Select the file > OK

  3. In the Navigator select the Report Table and chose Edit
  4. Remove the Attribute:name column
  5. Expand the ReportHost by clicking on the 2 arrows button in the column header.
  6. Remove the ReportHost.ReportItem column (we’ll use this column in the second query).
  7. Expand the ReportHost.HostProperties column by clicking on the 2 arrows button in the column header.
  8. Expand the ReportHost.HostProperties.tag column by clicking on the 2 arrows button in the column header.
  9. Reorder columns: Move the second column to the end of the table
    (we now have: ReportHost.Attribute:name, ReportHost.Hostproperties.tag.Attribute:name, ReportHost.Hostproperties.tag.Element.Text
  10. Pivot Columns: Select the second and the third column >Menu Transform > Pivot Columns
    Advanced options: Don’t Aggregate
    OK
  11. On the right you can name your query (default “Report”).
    I suggest you call it “hosts” or “targets”.You should now have all host properties in a good workable format.
    Close and load to your own desire. (to table or data model)For the vulnerability and compliance data we’ll create a second query:
  12. Repeat the browse to file steps in step 2 point it to the same .nessus. file
  13. In the Navigator select the Report Table and chose Edit
  14. Remove the Attribute:name column
  15. Expand the ReportHost by clicking on the 2 arrows button in the column header.
  16. Remove the ReportHost.HostProperties column (we used this one in the first query)
  17. Expand the ReportHost.ReportItemcolumn by clicking on the 2 arrows button in the column header.
  18. On the right you can name your query (default “Report”) I suggest you call it “ReportItems” or “Scandata”.You should now have all vulnerability data in a good workable format.
    Close and load to your own desire. (to table or data model)

You don’t have to repeat the steps above if you have another .nessus file.
All query steps are recorded and saved in the excel file.
Just edit both queries and first step called “Source” and point it to the new .nessus file and hit refresh.

Shortcut

You can also use the advanced editor and create the query by copying the code below:

Hosts Query:

let
 Source = Xml.Tables(File.Contents("D:\ReadNessusFiles\MyScan.nessus")),
 Table1 = Source{1}[Table],
 #"Changed Type" = Table.TransformColumnTypes(Table1,{{"Attribute:name", type text}}),
 #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Attribute:name"}),
 #"Expanded ReportHost" = Table.ExpandTableColumn(#"Removed Columns", "ReportHost", {"Attribute:name", "HostProperties", "ReportItem"}, {"ReportHost.Attribute:name", "ReportHost.HostProperties", "ReportHost.ReportItem"}),
 #"Removed Columns1" = Table.RemoveColumns(#"Expanded ReportHost",{"ReportHost.ReportItem"}),
 #"Expanded ReportHost.HostProperties" = Table.ExpandTableColumn(#"Removed Columns1", "ReportHost.HostProperties", {"tag"}, {"ReportHost.HostProperties.tag"}),
 #"Expanded ReportHost.HostProperties.tag" = Table.ExpandTableColumn(#"Expanded ReportHost.HostProperties", "ReportHost.HostProperties.tag", {"Element:Text", "Attribute:name"}, {"ReportHost.HostProperties.tag.Element:Text", "ReportHost.HostProperties.tag.Attribute:name"}),
 #"Reordered Columns" = Table.ReorderColumns(#"Expanded ReportHost.HostProperties.tag",{"ReportHost.Attribute:name", "ReportHost.HostProperties.tag.Attribute:name", "ReportHost.HostProperties.tag.Element:Text"}),
 #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[#"ReportHost.HostProperties.tag.Attribute:name"]), "ReportHost.HostProperties.tag.Attribute:name", "ReportHost.HostProperties.tag.Element:Text")
in
 #"Pivoted Column"

ScanResults Query:

let
 Source = Xml.Tables(File.Contents("D:\ReadNessusFiles\MyScan.nessus")),
 Table1 = Source{1}[Table],
 #"Changed Type" = Table.TransformColumnTypes(Table1,{{"Attribute:name", type text}}),
 #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Attribute:name"}),
 #"Expanded ReportHost" = Table.ExpandTableColumn(#"Removed Columns", "ReportHost", {"Attribute:name", "HostProperties", "ReportItem"}, {"ReportHost.Attribute:name", "ReportHost.HostProperties", "ReportHost.ReportItem"}),
 #"Removed Columns1" = Table.RemoveColumns(#"Expanded ReportHost",{"ReportHost.HostProperties"}),
 #"Expanded ReportHost.ReportItem" = Table.ExpandTableColumn(#"Removed Columns1", "ReportHost.ReportItem", {"agent", "attachment", "Attribute:pluginFamily", "Attribute:pluginID", "Attribute:pluginName", "Attribute:port", "Attribute:protocol", "Attribute:severity", "Attribute:svc_name", "cpe", "cvss_base_score", "cvss_vector", "description", "fname", "plugin_modification_date", "plugin_name", "plugin_output", "plugin_publication_date", "plugin_type", "risk_factor", "script_version", "see_also", "solution", "synopsis"}, {"ReportHost.ReportItem.agent", "ReportHost.ReportItem.attachment", "ReportHost.ReportItem.Attribute:pluginFamily", "ReportHost.ReportItem.Attribute:pluginID", "ReportHost.ReportItem.Attribute:pluginName", "ReportHost.ReportItem.Attribute:port", "ReportHost.ReportItem.Attribute:protocol", "ReportHost.ReportItem.Attribute:severity", "ReportHost.ReportItem.Attribute:svc_name", "ReportHost.ReportItem.cpe", "ReportHost.ReportItem.cvss_base_score", "ReportHost.ReportItem.cvss_vector", "ReportHost.ReportItem.description", "ReportHost.ReportItem.fname", "ReportHost.ReportItem.plugin_modification_date", "ReportHost.ReportItem.plugin_name", "ReportHost.ReportItem.plugin_output", "ReportHost.ReportItem.plugin_publication_date", "ReportHost.ReportItem.plugin_type", "ReportHost.ReportItem.risk_factor", "ReportHost.ReportItem.script_version", "ReportHost.ReportItem.see_also", "ReportHost.ReportItem.solution", "ReportHost.ReportItem.synopsis"})
in
 #"Expanded ReportHost.ReportItem"