Create a CSV for each object using PowerShell

Question

I am running vulnerability scans and I have a raw CSV file that I import. I remove the Risk = None from the file and then pull the information I need (Name of vulnerability, Host, Risk, CVE, Description, Solution, See Also).

What I would like to do is chop the file up even more and create

Separate CSV files based on the vulnerability Name. the actual file name does not have to be the name of the vulnerability it can remain static V001, V002, ...

Populate the separate files with the results based on each vulnerability Name (Host, Risk, CVE, Description, etc.). So in file V001 all affected hosts for V001 vulnerability will populate in that file and so on.

$all = Import-Csv c:\scan.csv
$all_with_none_removed = $all | Where-Object {$_.Risk -ne "None"}
$Sumary_By_Vuln = $all_with_none_removed |
                  select Name, Host, Risk, CVSS, CVE, Description, Solution, 'See Also' |
                  Sort-Object Name
$Sumary_By_Vuln | Export-Csv C:\final.csv

Ansgar Wiechers · Accepted Answer · 2018-01-05 22:44:03Z

1

You could group your input by name and export each group to a separate file:

$all_with_none_removed |
    Select-Object Name, Host, Risk, CVSS, CVE, Description, Solution, 'See Also' |
    Group-Object Name |
    ForEach-Object { $_.Group | Export-Csv ($_.Name + '.csv') }

answered Jan 5, 2018 at 22:44

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ViperGDC Over a year ago

This works great but some of the file names are too long or have illegal characters. I get the following error 'Export-Csv : Illegal characters in path. At line:1 char:107 + ... ct Name | ForEach-Object { $_.Group | Export-Csv ($_.Name + '.csv') } + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OpenError: (:) [Export-Csv], ArgumentException + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ExportCsvCommand'

Ansgar Wiechers Over a year ago

So, check the value of $_.Name and fix that?

ViperGDC Over a year ago

Yes i found the values giving the errors, the characters are <, /, and \ i just have to figure out how to remove or ignore special characters when creating the file names. Thanks for your help Ansgar!

ViperGDC Over a year ago

I did the following to remove illegal characters but i am struggling with where to place this in the code you provided it just seems to not work correctly $remillcharacters | ForEach-Object { $_.Name -replace '[\W]',''} i try to place it

$all_with_none_removed | Select-Object Name, Host, Risk, CVSS, CVE, Description, Solution, 'See Also' | Group-Object Name | ForEach-Object { $_.Group ** -replace '[\W]',''** | Export-Csv ($_.Name + '.csv') }

but i dont think its right. Also i cant do export -path because i get an error cannot convert value.

Ansgar Wiechers Over a year ago

Replace invalid path characters with a valid character, e.g. an underscore.

MisterSeajay · Accepted Answer · 2018-01-05 22:42:20Z

0

Hint: You could first pull out all the distinct names from the data:

$Names = $all_with_none_removed | Select-Object Name -Unique

You then loop some code foreach (hint hint) of these names, filtering out the results you have found in a similar way to how you removed the no-risk results.

Sorry to not provide a full solution, but you're here to learn right?

answered Jan 5, 2018 at 22:42

MisterSeajay

4,7891 gold badge28 silver badges53 bronze badges

Collectives™ on Stack Overflow

Create a CSV for each object using PowerShell

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related