Hello!
It’s been a while since I’ve posted but here I am again looking for help!
I have been tasked with somehow creating a way to import a CSV that has more than 2000 lines and scrubbing the data from it that isn’t needed.
The CSV Looks something like this:
“Plugin”,“Plugin Name”,“Family”,“Severity”,“IP Address”,“Protocol”,“Port”,“Exploit?”,“Repository”
“111111”,“Ping the remote host”,“Port scanners”,“Info”,“X.X.X.X”,“TCP”,“0”,“No”,“Individual Scan”
Then has data that ends up coming in different strings such as:
Object UUID :00000000000-0000-0000-000000000000
UUID : xxx-xxxx-xxxx-xxxxx-xxxxxx version 1.0
Description : Unknown RPC service
Annotation : Impl friendly name
Type : Local RPC service
Named pipe : LRPC-1234567
Then further down has webaddresses and hash numbers that start with 0x, and all of this data always shows up in the Plugin row.
The script I have somewhat works, but I find that when I try to go in and remove data, or look at the CSV in notepad the data I thought was removed actually isn’t, but doesn’t show up if I open it in CSV. I have tried using get-content, but it exports the CSV unformatted, and I’ve tried using other comparisons such as -notlike and -notmatching but they don’t seem to work either.
Imported/Exported Variables
$CSVData = Import-Csv -Path "C:\Test.csv"
$MatchingExportFile = "C:\RemovedItems.csv"
$NonMatchingExportFile = "C:\NewOutput.csv"
$MatchingData = @()
$NonMatchingData = @()
#Strings to search and remove from excel - Possibility to Add More
$RegexStrings = '*Object UUID*', '*UUID*', '*Description*','*Annotation*','*Type*','*senssvc*','*Named*','*Windows*','*0x*','*3A*','*navigation*','*Value*','*+ CGI*','*Methods*','*Argument*','*- https*','*static*','*Attached*'
#Headers
$headerSelection = "Plugin", "Plugin Name","Family","IP Address","Port","Exploit","DNS Name","NetBIOS Name","Plugin Text","Synopsis","Description","Solution","Check Type","Version"
Foreach ($Row in $CSVData) {
$MatchFound = $False
Foreach ($TestString in $RegexStrings) {
If ($Row.Plugin -like $TestString) {
$MatchFound = $True
Break
}
}
If ($MatchFound) {$MatchingData += $Row}
Else {$NonMatchingData += $Row}
}
If ($MatchingData.Count -gt 0) {
$MatchingData | Export-Csv -Path $MatchingExportFile -Force -NoTypeInformation
Write-Host "Matching Data exported to $MatchingExportFile"
}
Else {Write-Host "No matching data to export!"}
If ($NonMatchingData.Count -gt 0) {
$NonMatchingData | Select $headerSelection | Export-Csv -Path $NonMatchingExportFile -Force -NoTypeInformation
Write-Host "Non-matching Data exported to $NonMatchingExportFile"
}
Else {Write-Host "No non-matching data to export!"}
My goal is to import the CSV, delete the cells with the data that is not needed, and delete other blank cells that show up, then export to new CSV. But I cannot get it to successfully do either.
Can somebody provide some input on what I may be doing wrong?
Thank you
