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
– kometen, Commented Feb 10, 2021 at 17:30
@kometen, you cannot simply concatenate multiple CSV files with >>, since each file has a header row. — mklement0
– mklement0, Commented Feb 10, 2021 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
– kometen, Commented Feb 10, 2021 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
– user2165827, Commented Feb 10, 2021 at 19:44

mklement0 · Accepted Answer · 2021-02-12 14:13:12Z

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.

4 Comments

user2165827 Over a year ago

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?

mklement0 Over a year ago

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 Over a year ago

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 Over a year ago

You can use a single pipeline with multiple commands to create a single output file, using the following idiom: & { <command1>; <command2>; <command3> } | Export-Csv ...

Collectives™ on Stack Overflow

Combining Powershell Output to Single csv

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related