xmlexcelpowershellcsvnessus

Powershell: extracting a comma-separated list of IPs?

I'm dealing with a .csv export from Nessus, where essentially I have a column of Host IPs and a column with Plugin IDs

My customer wants an output where, for example, Plugin X would be in a column, and then next to it would be a comma separated list of affected Host IPs, and then next to THAT would be a count of the affected Host IPs.

Pic of what I'm looking for

After importing the Nessus CSV with Powershell, I was able to start to get what I needed with this:

$allfiltered | select-object 'Host IP','Plugin ID' | Where-Object 'Plugin ID' -like "57041" | Format-Table -Property  'Plugin ID','Host IP'

This gives me an output like this:

57041     10.1.1.1
57041     10.1.1.2
57041     10.1.1.3
57041     10.1.1.4
57041     10.1.1.5
57041     10.1.1.6
57041     10.1.1.7

But as you can see, I have a long way to go to pull this into the output I need (see pic above).

I think I'm eventually going to need a for loop to get all the plugin values assessed, but I need to figure out how to essentially query for "Take all IPs that match plugin X and place them into a comma separated list" and go from there.

Can you help steer me in the right direction?

-B

Solution

  • What you want can be done simply using the Group-Object CmdLet.

    Something like this for instance would group all plugins together, then you could iterates the groups, join the ips together in a comma delimited string and do whatever you wish from there.

    $Grouped = $List | group -Property PluginID | sort PluginID

Foreach ($Group in $Grouped) {
   $IpsDelimited = ($group.Group | Select -ExpandProperty IP | Sort) -join ','
   Write-Host "Plugin ID: $($Group.Name) " -ForegroundColor Cyan -NoNewline
    Write-Host $IpsDelimited

}

    Here is the sample list I used for this example. 

    # The list is for example purpose based on what you have. 
$List = @(
    new-object psobject -Property @{'PluginID'='57041 ';IP='10.1.1.1'}
    new-object psobject -Property @{'PluginID'='57041 ';IP='10.1.1.3'}
    new-object psobject -Property @{'PluginID'='57041 ';IP='10.1.1.4'}
    new-object psobject -Property @{'PluginID'='57042 ';IP='10.1.1.6'}
    new-object psobject -Property @{'PluginID'='57042 ';IP='10.1.1.7'}
    new-object psobject -Property @{'PluginID'='57043 ';IP='10.1.1.8'}
    new-object psobject -Property @{'PluginID'='57043 ';IP='10.1.1.9'}
    new-object psobject -Property @{'PluginID'='57044 ';IP='10.1.1.10'}
)

    Resulting output

    Resulting output

    For fun

    If you were to re-export it to a CSV file, you could use calculated properties to manipulate your output object directly and export it with the modified informations. 

    $Output = $Grouped |
Select  @{'N'='PluginID';E={$_.Name}},
        @{N='IPSDelimited';E={($_.Group | Select -ExpandProperty IP | Sort) -join ','}},
        @{N='My other field';E={$_.Group.OtherField}}

$Output | Export-Csv -Path 'MyPath' -NoTypeInformation