Task

I’ve recently come across a logistic task that challenged my conception of Office tools and their practicality.

The task was about Scanning some assets with a Vulnerability Scanner and gather the results in a specific CSV format.

The initial part of the assessment is straightforward:

• Define the Assessment Scope
• Proceed to backup any asset involved
• Scan the assets

After that, the Scanning tool can output the list of Vulnerabilities found in a summary Report, exportable in PDF and CSV format.

The problem is simple: the expected Compliance CSV Format is different from the Scanning tool CSV Report Format.

Solution

I started mapping out the fields expected in the compliance report to the fields provided by the scan report, obtaining a table similar to the following:

Scan Report	Compliance Report
Absent, insert progressive ID	CODE
NAME	VULNERABILITY
CVSS v2.0	CVSSv2
CVSS v3.0	CVSSv3
RISK Map according to following: 1 – LOW 2 – MEDIUM 3 – HIGH 4 – CRITICAL	SEVERITY
HOST	TARGET
PORT	PORT
PROTOCOL	PROTOCOL
CVE	CVE
DESCRIPTION First part	DESCRIPTION
DESCRIPTION Second part	CONSEQUENCE
SOLUTION	SOLUTION

Since the assessment results have to be processed in a Windows environment, I opted for codifying the map above using Powershell v.7.

The choice was easily justified by the native support of CSV files offered by Powershell. Nevertheless, some unexpected behaviours manifested throughout the process and eventually required workarounds and tinkering.

So I started coding a conversion.ps1 script.

The first step was importing the source data:

$source = import-csv -path '.\network_scan.csv'

The file imported is the Scan result. Next, the core of the script, mapping field to field:

$report = @() # initialize array of entries
$count = 0 # progressive ID starting from 0

# map parameters
foreach ($entry in $source) {
  if ($entry.risk -eq "None") {continue;}
  $converted = [ordered]@{
    "CODE" = $count;
    "VULNERABILITY" = $entry.name;
    "CVSSv2" = "$($entry.'CVSS v2.0')";
    "CVSSv3" = "$($entry.'CVSS v3.0')";
    "SEVERITY" = switch ($entry.risk) {
        "INFO" {''}
        "LOW" {1}
        "MEDIUM" {2}
        "HIGH" {3}
        "CRITICAL" {4}
        default {''}
    };
    "TARGET" = "$($entry.host)";
    "PORT" = $entry.port;
    "PROTOCOL" = $entry.protocol;
    "CVE" = $entry.CVE;
    "DESCRIPTION" = $entry.description;
    "CONSEQUENCE" = $entry.description;
    "SOLUTION" = $entry.solution;
  }
  $report += $converted # add mapped entry to final report
  $count += 1; # increase ID
}

The foreach loop navigates through every entry in the scan report and maps it to a new entry as an Ordered HashMap. The ordered part is relevant for building the final CSV with columns in a specific order, rather than alphabetically sorted. The new entries are then added to the $report array. To exclude informative entries, I’ve added a continue statement at the start of the loop that skips non severe findings. The majority of the mapping is done by simply renaming the parameter. As regards the SEVERITY field, a simple switch - case construct converts a String value into a numeric or empty value.

Finally, the script exports the newly built report:

# temporary export in csv
$report | export-csv -path '.\temp.csv' -notypeinformation
# convert csv into xlsx
import-csv -path '.\temp.csv' | export-excel -path '.\report.xlsx' -autosize -nonumberconversion *

I’ve opted for producing a final report in .xlsx format, due to the fact that Excel requires at least an extra step for working with CSV files besides just opening the file.

To use the export-excel cmdlet, make sure to install first the necessary PS module:

PS> install-module ImportExcel

The painful part was figuring out the switch -nonumberconversion *. Such parameter resolved the issue of having floats under the column CVSS and IP addresses under the column TARGET being misinterpreted and losing their proper notation. The issue is addressed in this Reddit Post.

Also, the temporary export in CSV and consequent conversion to XLSX is necessary to avoid strange interactions that arise when trying to export a Nested Object such as the $report variable straight to XLSX.

Further Steps

Having the report generated, I then proceeded to expand the script by importing more than one Scan result at a time and merging the findings. This implied some manual operations on the final result, such as:

• deleting duplicated entries due to the same vulnerabilities being found in different scans.
• merging entries differing only by the CVE field.

Overall, the automation provided by the script still saved me the majority of the tedious stuff.

Conclusion

This task was a valid input to gain confidence with Powershell and its practical applications. Even though some aspects of the technology are counterintuitive, having a programmatically approach to Office tools is a leverage that comes handy at times of reporting. I’d definitely suggest to bite the bullet and overcome the initial friction towards PS by solving some simple tasks such as the one showcased.