Combining Powershell Output to Single csv

Question

I cobbled together the powershell commands below to export the list of servers which are members of a security group to csv. I have a number of commands which produces its own csv. Instead of having a dozen csv files, I want to produce one csv overall with two columns, server name and the security group it is in. How can this be done?

Get-ADGroupMember -identity "SecGrp-123" -server "test.net" | select name | sort-object name | export-csv "SecGrp-123.csv" -NoTypeInformation
Get-ADGroupMember -identity "SecGrp-456" -server "test.net" | select name | sort-object name | export-csv "SecGrp-456.csv" -NoTypeInformation

You can run the commands one by one and send the output to a single file. In Unix the double-gt-sign (>>) will append, where as a single (>) will overwrite, ie. `cat foo.txt >> bar.txt`. — kometen, Feb 10 '21 at 17:30
@kometen, you cannot simply concatenate multiple CSV files with `>>`, since each file has a header row. — mklement0, Feb 10 '21 at 17:58
You'd of course have to omit the header in that case. The utilities head and tail and grep does a nice job doing just that. — kometen, Feb 10 '21 at 18:39
These are Windows Powershell commands being run, not Unix. The important thing I need here though is to capture the security group name in the spreadsheet alongside the server name, i.e. row 1 would have VM001, SecGrp-123; row 2 has VM002; SecGrp-123; row 3 has VM100,SecGrp-456, etc etc — user2165827, Feb 10 '21 at 19:44

mklement0 · Accepted Answer · 2021-02-12T14:13:12.587

2

I assume you're looking for something like this:

Write-Output SecGrp-123, SecGrp-456 -PipelineVariable groupName | 
  ForEach-Object {
    Get-ADGroupMember -Identity $_ -Server test.net | Sort-Object Name
  } | 
      Select-Object Name, @{ n='Group'; e={ $groupName } } | 
        Export-Csv out.csv -NoTypeInformation

Note: The above sorts the servers by name per group. If you want to sort by name across all groups, move the Sort-Object Name call outside the ForEach-Object call and place it after the latter as a new pipeline segment.

If Get-ADGroupMember accepts group-name strings as pipeline input (I can't verify this), you can simplify to:

Write-Output SecGrp-123, SecGrp-456 -PipelineVariable groupName | 
  Get-ADGroupMember -Server test.net | 
    Sort-Object Name |
      Select-Object Name, @{ n='Group'; e={ $groupName } } | 
        Export-Csv out.csv -NoTypeInformation

Note: This sorts by server name across all groups; to sort per group, use Sort-Object Group, Name, which will list the groups in sort order too.

See also:

The common -PipelineVariable parameter (PSv4+; alias -pv), which saves a cmdlet's current output object in a variable of the given name for later use in a downstream cmdlet's script block.
Calculated properties, the mechanism that is used to define the Group property in the Select-Object call.

edited Feb 12 '21 at 14:13

answered Feb 10 '21 at 17:36

mklement0

382,024
64
607
775

That works really well thanks! I commented out lines 2 and 4 but it produces an empty out.csv file. If I wanted to add a third (& final!) column with the domain name (test.net, dev.net, prod.net) and append the result files together into one, could that be done too? – user2165827 Feb 11 '21 at 22:04
Thanks for checking: you cannot just comment out line 4, because then you're missing a `|` after the `Get-ADGroupMember` call, and the `Select-Object` call starts a _new_ pipeline; since that new pipeline then has no input, `Export-Csv` creates an empty file. – mklement0 Feb 11 '21 at 22:06
1

You can simply add another calculated property to the `Select-Object` call, such as `@{ n='Domain'; e={ 'test.net' } }` - but that would hard-code the domain name. Where would the other names come from? – mklement0 Feb 11 '21 at 22:08
You can use a single pipeline with multiple commands to create a single output file, using the following idiom: `& { ; ; } | Export-Csv ...` – mklement0 Feb 11 '21 at 22:10

Combining Powershell Output to Single csv

1 Answers1