Manipulating CSV Formatting for HBSS Modules

问题:

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 Hostname1 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:

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 separated by columns and still only having one hostname per line as shown below:

回答1:

I do not have multiple computers available, nor have I any McAffee product installed. However, here is a possible solution to your problem. You would have to adjust Vendor to PSComputerName, the -match RegEx, and add the -ComputerName or -CimSession parameter to Get-CimInstance. But the basic concept should work.

$Product = Get-CimInstance -ClassName Win32_Product | Where-Object {$_.Vendor -match '(microsoft|intel)'}
$Group = $Product | Group-Object -Property Vendor
$result = foreach ($Vendor in $Group) {
    $Products = foreach ($item in $Vendor.Group) {
        "$($item.Caption);$($item.Version)"
    }
    $Products = $Products -join ','
    "$($Vendor.Name),$Products"
}

$HeaderLength = ($result | ForEach-Object {($_ -split ',').Count} | Sort-Object -Descending)[0]-1
$Header = "Vendor,"
$x = 1
$Header += do {
    $x++
    "Caption;Version,"
} until ($x -eq $HeaderLength)

$Header | Out-File -FilePath $env:TEMP\strange.csv
$result | Out-File -FilePath $env:TEMP\strange.csv -Append

If you open $env:TEMP\strange.csv in Excel and use the Text to columns function with , as the delimiter, you get the result es requested in your Phase 3 Output.

It's not beautiful and also it doesn*t make any sense to me, but that's what you requested for. :p

回答2:

Using Group-Object and Flatten-Object:

Given:

$CSV = ConvertFrom-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
Hostname2,McAfee Agent,5.00.4006
Hostname2,McAfee DLP Endpoint,10.0.260.42
Hostname2,McAfee DLP Endpoint,10.0.260.42
Hostname3,McAfee Policy Auditor Agent,6.2.0
Hostname3,McAfee DLP Endpoint,10.0.260.42
"@

Command:

$CSV | Group PSComputerName | Flatten | Format-Table

Result:

Values.1  Count Group.1.PSComputerName Group.1.caption                  Group.1.version Group.2.PSComputerName Group.2.caption
--------  ----- ---------------------- ---------------                  --------------- ---------------------- ---------------
Hostname1     6 Hostname1              McAfee Host Intrusion Prevention 8.00.0801       Hostname1              McAfee Policy A...
Hostname2     3 Hostname2              McAfee Agent                     5.00.4006       Hostname2              McAfee DLP Endp...
Hostname3     2 Hostname3              McAfee Policy Auditor Agent      6.2.0           Hostname3              McAfee DLP Endp...