Report writing is one of the most tedious tasks when it comes to a Penetration Tester's life. It's unavoidable, and I think the Penetration Test's quality has a lot to do with the report. I cannot emphasize enough on the report. The report is an integral part of the Penetration Test. With that being said, let's move forward and do some PowerShell Magic ;)
A lot of organizations prepare an excel sheet to track the list of vulnerabilities. This sheet may also be shared with the client as an interim report. Let's calls this excel sheet as "Vuln Sheet". Vuln Sheet contains a list of vulnerabilities discovered, its description, the vulnerability impact, affected hosts, and remediation steps. If you look closely to this "Vuln Sheet" it has mostly all of the components which a tester uses to generate a Penetration Test report. Ofcourse, the report has a lot more elements such as scope, executive summary, disclaimers etc. but if we are just focusing on the "Findings" section of the report, I guess the "Vuln Sheet" covers it all.
PowerShell has a lot of capability in reading and writing both Word and Excel. So without wasting time, lets take a deep dive into parsing excel sheet for the vulnerabilities and writing a word document from the parsed contents.
Following is how my Excel Sheet of Vulnerabilities look like
Open Excel Sheet with PowerShell with the help of COM.
$objExcel = New-Object -ComObject Excel.Application$WorkBook = $objExcel.Workbooks.Open("C:\Users\tempuser\Documents\VulnList.xlsx")
Open specific sheet of the excel sheet
$SheetNames = $WorkBook.sheets | Select-Object -Property Name$WorkSheet = $WorkBook.sheets.item("Network Penetration Testing")
Get the Table Range
$WorksheetRange = $workSheet.UsedRange$RowCount = $WorksheetRange.Rows.Count$ColumnCount = $WorksheetRange.Columns.CountWrite-Host "RowCount:" $RowCountWrite-Host "ColumnCount" $ColumnCount
Initialize the column variables in Excel
$ColHeading = 0$ColObservation = 0$ColImplication = 0$ColRecommendation = 0$ColAffectedResources = 0$ColSeverity = 0$hash = @{} # I'll tell u why this is needed in a sec
Assign values to column variables
for($i=1;$i -le $ColumnCount;$i+=1){$ColHead = $WorkSheet.cells.Item(1, $i).textif($ColHead -like "Heading"){Write-Host "[+]Heading Column Found"$ColHeading = $i}if($ColHead -like "Observation"){Write-Host "[+]Observation Column Found"$ColObservation = $i}if($ColHead -like "Implication"){Write-Host "[+]Implication Column Found"$ColImplication = $i}if($ColHead -like "Recommendation"){Write-Host "[+]Recommendation Column Found"$ColRecommendation = $i}if($ColHead -like "Affected Resources"){Write-Host "[+]Affected Resources Column Found"$ColAffectedResources = $i}if($ColHead -like "Severity"){Write-Host "[+]Severity Column Found"$ColSeverity = $i}​}​Write-Host "`r`n"Write-Host "Printing Column Status"Write-Host "Heading:" $ColHeadingWrite-Host "Observation:" $ColObservationWrite-Host "Implication:" $ColImplicationWrite-Host "Recommendation:" $ColRecommendationWrite-Host "Affected Resources:" $ColAffectedResourcesWrite-Host "Severity:" $ColSeverity
We can now start extracting text from the columns. We are going to introduce a lot of functions in the following loop. Function "MakeWordReport" requires a Word Template. I'll go thorough that in later sections of this post.
# Extracting Text Now​for($i=2;$i -le $RowCount; $i+=1){$TextHeading = $WorkSheet.cells.Item($i, $ColHeading).text$TextObservation = $WorkSheet.cells.Item($i, $ColObservation).text$TextImplication = $WorkSheet.cells.Item($i, $ColImplication).text$TextRecommendation = $WorkSheet.cells.Item($i, $ColRecommendation).text$TextAffectedResources = $WorkSheet.cells.Item($i, $ColAffectedResources).text$TextSeverity = $WorkSheet.cells.Item($i, $ColSeverity).textWorkOnHeading $TextHeadingWorkOnObservation $TextObservationWorkOnImplication $TextImplicationWorkOnRecommendation $TextRecommendationWorkOnAffectedResources $TextAffectedResourcesWorkOnSeverity $TextSeverity$hash # Just Printing HashTableMakeWordReport $i $hash$hash = @{}}
Let's define the functions I proposed in the above for loop.
Function MakeWordReport($index,$hash){$template = "C:\Users\tempuser\Documents\TemplateFinding.docx"$wd = New-Object –comobject Word.Application$doc=$wd.documents.Add($template)$newfile="C:\Users\tempuser\Documents\file_$index.docx"foreach($key in $hash.keys){$objrange = $doc.Bookmarks.Item($key).Range$objrange.Text = $hash[$key]}$doc.SaveAs([ref]$newfile)$doc.Close()$wd.Quit()}​function WorkOnSeverity($RawSeverity){$Severity = $RawSeverity.trim()$hash["Severity"] = $Severity}function WorkOnRecommendation($RawRecommendation){if($RawRecommendation -like "*Reference:*"){$temp = $RawRecommendation -split "Reference:",2$Recommendation = $temp[0].trim()$Reference = $temp[1].Trim()}else{$Recommendation = $RawRecommendation.Trim()$Reference = ""}​$hash["Recommendation"] = $Recommendation$hash["Reference"] = $Reference}​function WorkOnAffectedResources($RawAffectedResources){$AffectedResources = $RawAffectedResources.Trim()$hash["AffectedResources"] = $AffectedResources}​function WorkOnImplication($RawImplication){$Implication = $RawImplication.Trim()$hash["Implication"] = $Implication​}​function WorkOnObservation($RawObservation){$Observation = $RawObservation.trim()$hash["Observation"] = $Observation}​function WorkOnHeading($RawHeading){$Heading = $RawHeading.trim()$hash["Heading"] = $Heading}
We have now defined the WorkOn* functions in PowerShell. Most functions defined are very basic here but I want to draw your attention to the WorkOnRecommendation fuction. Its not a basic function. Basically here i wanted to show that you can do string manipulation here. This is just an example.
There is one more importtant thing for you to note here. We here see that we are populating our hashtable $hash here. We are using key such as "Observation", "AffectedResources" etc. This key is important because we will use this key while making the Micorsoft Word Template
In the function MakeWordReport you can see that we are running a foreach loop on the hashtable keys. In the loop we are replacing the bookmarks predefined with those specific keys.
By the way final script as following
Function MakeWordReport($index,$hash){$template = "C:\Users\tempuser\Documents\TemplateFinding.docx"$wd = New-Object –comobject Word.Application$doc=$wd.documents.Add($template)$newfile="C:\Users\tempuser\Documents\file_$index.docx"foreach($key in $hash.keys){$objrange = $doc.Bookmarks.Item($key).Range$objrange.Text = $hash[$key]}$doc.SaveAs([ref]$newfile)$doc.Close()$wd.Quit()}​function WorkOnSeverity($RawSeverity){$Severity = $RawSeverity.trim()$hash["Severity"] = $Severity}function WorkOnRecommendation($RawRecommendation){if($RawRecommendation -like "*Reference:*"){$temp = $RawRecommendation -split "Reference:",2$Recommendation = $temp[0].trim()$Reference = $temp[1].Trim()}else{$Recommendation = $RawRecommendation.Trim()$Reference = ""}​$hash["Recommendation"] = $Recommendation$hash["Reference"] = $Reference}​function WorkOnAffectedResources($RawAffectedResources){$AffectedResources = $RawAffectedResources.Trim()$hash["AffectedResources"] = $AffectedResources}​function WorkOnImplication($RawImplication){$Implication = $RawImplication.Trim()$hash["Implication"] = $Implication​}​function WorkOnObservation($RawObservation){$Observation = $RawObservation.trim()$hash["Observation"] = $Observation}​function WorkOnHeading($RawHeading){$Heading = $RawHeading.trim()$hash["Heading"] = $Heading}​$objExcel = New-Object -ComObject Excel.Application$WorkBook = $objExcel.Workbooks.Open("C:\Users\tempuser\Documents\VulnList.xlsx")$SheetNames = $WorkBook.sheets | Select-Object -Property Name$WorkSheet = $WorkBook.sheets.item("Network Penetration Testing")$WorksheetRange = $workSheet.UsedRange$RowCount = $WorksheetRange.Rows.Count$ColumnCount = $WorksheetRange.Columns.CountWrite-Host "RowCount:" $RowCountWrite-Host "ColumnCount" $ColumnCount$ColHeading = 0$ColObservation = 0$ColImplication = 0$ColRecommendation = 0$ColAffectedResources = 0$ColSeverity = 0$hash = @{} # I'll tell u why this is needed in a sec​​for($i=1;$i -le $ColumnCount;$i+=1){$ColHead = $WorkSheet.cells.Item(1, $i).textif($ColHead -like "Heading"){Write-Host "[+]Heading Column Found"$ColHeading = $i}if($ColHead -like "Observation"){Write-Host "[+]Observation Column Found"$ColObservation = $i}if($ColHead -like "Implication"){Write-Host "[+]Implication Column Found"$ColImplication = $i}if($ColHead -like "Recommendation"){Write-Host "[+]Recommendation Column Found"$ColRecommendation = $i}if($ColHead -like "Affected Resources"){Write-Host "[+]Affected Resources Column Found"$ColAffectedResources = $i}if($ColHead -like "Severity"){Write-Host "[+]Severity Column Found"$ColSeverity = $i}​}​Write-Host "`r`n"Write-Host "Printing Column Status"Write-Host "Heading:" $ColHeadingWrite-Host "Observation:" $ColObservationWrite-Host "Implication:" $ColImplicationWrite-Host "Recommendation:" $ColRecommendationWrite-Host "Affected Resources:" $ColAffectedResourcesWrite-Host "Severity:" $ColSeverity​​for($i=2;$i -le $RowCount; $i+=1){$TextHeading = $WorkSheet.cells.Item($i, $ColHeading).text$TextObservation = $WorkSheet.cells.Item($i, $ColObservation).text$TextImplication = $WorkSheet.cells.Item($i, $ColImplication).text$TextRecommendation = $WorkSheet.cells.Item($i, $ColRecommendation).text$TextAffectedResources = $WorkSheet.cells.Item($i, $ColAffectedResources).text$TextSeverity = $WorkSheet.cells.Item($i, $ColSeverity).textWorkOnHeading $TextHeadingWorkOnObservation $TextObservationWorkOnImplication $TextImplicationWorkOnRecommendation $TextRecommendationWorkOnAffectedResources $TextAffectedResourcesWorkOnSeverity $TextSeverity$hash # Just Printing HashTableMakeWordReport $i $hash$hash = @{}}
To make word template, just modify your existing word template to have bookmarks at places you want your text in. To place a BookMark click insert and then Bookmark. If you are still unsure, follow the screenshots below
Before running the script check the variables such as
$template = "C:\Users\tempuser\Documents\TemplateFinding.docx"$newfile="C:\Users\tempuser\Documents\file_$index.docx"$WorkBook = $objExcel.Workbooks.Open("C:\Users\tempuser\Documents\VulnList.xlsx")$WorkSheet = $WorkBook.sheets.item("Network Penetration Testing")
Once you run the script, the output looks like below
RowCount: 5ColumnCount 7[+]Heading Column Found[+]Observation Column Found[+]Implication Column Found[+]Recommendation Column Found[+]Affected Resources Column Found[+]Severity Column Found​​Printing Column StatusHeading: 2Observation: 3Implication: 4Recommendation: 5Affected Resources: 6Severity: 7​Name Value---- -----Recommendation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi non neque nunc. Quisque mollis scelerisque dui, a laor...AffectedResources Affected Host 1...Observation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras rhoncus est eget aliquet dictum. Vestibulum sed pretium...Heading This is Heading 1Implication Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam vitae nisi vel arcu finibus ultricies eu dignissim urna....Severity CriticalReference www.google.com...Recommendation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi non neque nunc. Quisque mollis scelerisque dui, a laor...AffectedResources Affected Host 1...Observation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras rhoncus est eget aliquet dictum. Vestibulum sed pretium...Heading This is Heading 2Implication Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam vitae nisi vel arcu finibus ultricies eu dignissim urna....Severity HighReferenceRecommendation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi non neque nunc. Quisque mollis scelerisque dui, a laor...AffectedResources Affected Host 1...Observation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras rhoncus est eget aliquet dictum. Vestibulum sed pretium...Heading This is Heading 3Implication Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam vitae nisi vel arcu finibus ultricies eu dignissim urna....Severity MediumReferenceRecommendation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi non neque nunc. Quisque mollis scelerisque dui, a laor...AffectedResources Affected Host 1...Observation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras rhoncus est eget aliquet dictum. Vestibulum sed pretium...Heading This is Heading 4Implication Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam vitae nisi vel arcu finibus ultricies eu dignissim urna....Severity LowReference
And you will have the files like this at the destination defined in $newfile
So the final task is to combine all this file_*.docx files.
Use the following macro to combine the docx. The macro allows multiselect so select all the files you want to merge and click ok.
Sub Merge()Dim dlgFile As FileDialogDim nTotalFiles As IntegerDim nEachSelectedFile As Integer​Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)With dlgFile.AllowMultiSelect = TrueIf .Show <> -1 ThenExit SubElsenTotalFiles = .SelectedItems.CountEnd IfEnd WithFor nEachSelectedFile = 1 To nTotalFilesSelection.InsertFile dlgFile.SelectedItems.Item(nEachSelectedFile)If nEachSelectedFile < nTotalFiles ThenSelection.InsertBreak Type:=wdPageBreakElseIf nEachSelectedFile = nTotalFiles ThenExit SubEnd IfEnd IfNext nEachSelectedFileEnd Sub
Tip: Make a copy of the TemplateFinding.docx, delete everything inside it and run the macro. Doing this will preserve your formatting.
So we are hackers and hackers don't copy paste 😜. Basically, save time copy pasting stuff and use Powershell. I know this is not revolutionary but yeah, its a script I wrote and I found no reference of something similar over internet. Maybe you guys can find it and let me know.
By the way, for your ease of access, I have uploaded all these scripts and documents on my github for your quick reference. Feel free to change/modify/enhance to fit your needs
​