Powershell: Manipulating CSV Formatting for HBSS Modules

PowerShell Help
1

I need help with manipulating data that I export to a csv. Right now I actually learned how to merge columns together so that once it is put into a CSV the hostname shows on only 1 line and the other two columns I merge together so that each line will only have one hostname. I however need to take it a step further.

Below is my progression:

Phase 1:

This was only good because the script retrieved the data, but as you can see below HostName shows up on every line for each caption and version, which would lead to several repeats of a machine name for several lines making the csv file extremely longer than it necessarily should be:

Script:

GWMI -Class win32_product -Comp( GC D:\McAfeeHost.txt) | Where {$.vendor -like “mcafee*” -or $.vendor -like “usaf*”} | Select pscomputername, caption, version | Export-Csv -NoTypeInformation d:\Get-HBSSModules-Test.csv
Output in CSV:

PSComputerName caption version
Hostname1 McAfee Host Intrusion Prevention 8.00.0801
Hostname1 McAfee Policy Auditor Agent 6.2.0
Hostname1 McAfee DLP Endpoint 10.0.260.42
Hostname1 McAfee VirusScan Enterprise 8.8.08000
Hostname1 ACCM 3.0.4.1
Hostname1 McAfee Agent 5.00.4006

Phase 2:

I have progressed by merging the caption and version together, so that each hostname that is fetched from -Comp( GC D:\McAfeeHost.txt) shows up only once per line. While this is more of what I am looking for, it isn’t the best option for formatting as shown below the in the Output of the CSV:

Script:

GWMI -Class win32_product -Comp( GC D:\McAfeeHost.txt) | Where {$.vendor -like “mcafee*” -or $.vendor -like “usaf*”} | Select pscomputername, caption, version |Sort-Object Caption | Export-Csv -NoTypeInformation d:\Get-HBSSModules-Test.csv

$a = Import-Csv d:\Get-HBSSModules-Test.csv
$a |
Group-Object pscomputername | Select-Object
@{name=“pscomputername”;expression={$.Name}},
@{name=“caption, version”;expression={($.Group | % { $.caption, $.version }) -join ‘;’}} |
Export-Csv d:\Get-HBSSModules-Test.csv -NoTypeInformation

Output to CSV:

pscomputername caption, version
Hostname1 ACCM;3.0.4.1;McAfee Agent;5.00.4006;McAfee DLP Endpoint;10.0.260.42;

Phase 3:

If at all possible I would like to take each caption along with its version and put them together like phase 2, but seperated by columns and still only having one hostname per line as shown below:

PSComputerName Caption; Version Caption; Version Caption; Version
Hostname1 ACCM;3.0.4.1 McAfee Agent;5.00.4006 McAfee DLP Endpoint;10.0.260.42
Hostname2 ACCM;3.0.4.1 McAfee Agent;5.00.4006 McAfee DLP Endpoint;10.0.260.42

2

Hi

Am I missing something but aren’t you grouping all together with -join here? So you basically hare only two columns “pscomputername” and “caption, version”?

@{name=“pscomputername”;expression={$.Name}},
@{name=“caption, version”;expression={($.Group | % { $.caption, $.version }) -join ‘;’}}

Regards

Jake

3

Sorry Jarkko,

I thought I answered back to you. I did the -join to figure a way to merge both the caption and version. The next step would be for me to take each individual caption and version and separate them by columns. Like the McAfee Agent caption and its version would be on column B and the ACCM caption and its version would be on Column C and so forth for the next 4 modules that I would have left. I have the caption and version joined together which is what I want to do as you stated and I currently do only have two columns, but I would like to have the first column which would be Column A being the PSComputerName and then Column B being the first modules caption and Version, then Column C will have the 2nd module caption and version, then column D with its 3rd modules caption and version. Hope this helps. Here is how my output currently looks:

PSComputerName Name,Version
ExampleComputerABC McAfee Agent;5.00.4022;McAfee Host Intrusion Prevention;8.00.0801;McAfee DLP Endpoint;10.0.100.372

I would like to have the above two columns split up with every module and its version instead of having just two columns, but each module and its version having its own column and then the next module and its caption and version and so forth.

4

Sorry Jarkko,

I thought I answered back to you. I did the -join to figure a way to merge both the caption and version. The next step would be for me to take each individual caption and version and separate them by columns. Like the McAfee Agent caption and its version would be on column B and the ACCM caption and its version would be on Column C and so forth for the next 4 modules that I would have left. I have the caption and version joined together which is what I want to do as you stated and I currently do only have two columns, but I would like to have the first column which would be Column A being the PSComputerName and then Column B being the first modules caption and Version, then Column C will have the 2nd module caption and version, then column D with its 3rd modules caption and version. Hope this helps. Here is how my output currently looks:

PSComputerName Name,Version
ExampleComputerABC McAfee Agent;5.00.4022;McAfee Host Intrusion Prevention;8.00.0801;McAfee DLP Endpoint;10.0.100.372

I would like to have the above two columns split up with every module and its version instead of having just two columns, but each module and its version having its own column and then the next module and its caption and version and so forth.