Powershell - Combine output of 2 cmdlets into CSV

1etherer

Fully Optimized
Messages
1,878
Location
Earth
Another PS script im stuck with!

I have created these from scratch and cannot figure out how to either;
1. combine the scripts together
2. just combine the output into 1 CSV

Code:
#Based on OU - gets username and groups they are in
Get-ADUser -LDAPFilter "(name=*)" -SearchScope Subtree `
 -SearchBase "OU=User Accounts,DC=Domain,DC=local" | %{

  $user = $_
   $user | Get-ADPrincipalGroupMembership | 
 Select @{N="User";E={$user.sAMAccountName}},@{N="Group";E={$_.Name}}
}| Select User,Group | export-csv 




#Based on OU - gets group descriptions (copy the description into the above extract to have complete report (dirty work around))
Get-ADUser -LDAPFilter "(name=*)" -SearchScope Subtree `
 -SearchBase "OU=User Accounts,DC=domain,DC=local" | %{
  $user = $_
   $user | Get-ADPrincipalGroupMembership | 
Get-ADGroup -Properties * | select description} | export-csv
 
UPDATE -

Ive managed to find and mod a script that works - BUT cannot export to CSV. whatever I try I get errors of all different sorts.

Code:
Get-ADUser -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "DC=Domain,DC=local" | select samaccountname -expand samaccountname | ForEach-object { 
$User = $_
$GroupName = $user | Get-ADPrincipalGroupMembership 
$GroupDes = $user | Get-ADPrincipalGroupMembership | Get-ADGroup -Properties description

New-Object -TypeName PSObject -Property @{
UsersSamAccountName = $user
UserInGroup = $groupName | select name -expand name
ADGroupDes = $groupDes | select description -expand description

    }
}
 
Woah dude, going overboard with the pipes there :p to be honest I'm a little confused about a couple lines e.g. the GroupDes variable assignment, but I'll take a look at that tomorrow when I'm at work lol

edit: ohh right I think I gotcha now :grin:

Code:
$Users = Get-ADUser -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "DC=Domain,DC=local"
Foreach ($User in $Users){
	$GroupName = $user.samaccountname | Get-ADPrincipalGroupMembership 
	$GroupDes = $user.samaccountname | Get-ADPrincipalGroupMembership | Get-ADGroup -Properties description
	
	$UserInfo = New-Object -TypeName PSObject -Property @{
		UsersSamAccountName = $user
		UserInGroup = $groupName | select name -expand name
		ADGroupDes = $groupDes | select description -expand description
    }
	$UserInfo | export-csv -Append -Path "C:\my\favourite\csv\file\<3.csv"
}
 
Last edited:
Woah dude, going overboard with the pipes there :p to be honest I'm a little confused about a couple lines e.g. the GroupDes variable assignment, but I'll take a look at that tomorrow when I'm at work lol

edit: ohh right I think I gotcha now :grin:

Code:
$Users = Get-ADUser -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "DC=Domain,DC=local"
Foreach ($User in $Users){
	$GroupName = $user.samaccountname | Get-ADPrincipalGroupMembership 
	$GroupDes = $user.samaccountname | Get-ADPrincipalGroupMembership | Get-ADGroup -Properties description
	
	$UserInfo = New-Object -TypeName PSObject -Property @{
		UsersSamAccountName = $user
		UserInGroup = $groupName | select name -expand name
		ADGroupDes = $groupDes | select description -expand description
    }
	$UserInfo | export-csv -Append -Path "C:\my\favourite\csv\file\<3.csv"
}
I tried the above already, but not with append. I'll give that a go later.

Without append it works but I don't get full info just (3 dots) ... Once the text becomes to long.

I'll post back output once I run it.
 
Last edited:
Ah right, probably need to expand + join the objects being returned. I'll fix that up in a sec

edit: ok so this works, but damn it looks messy as hell :p there might be a better way of outputting multiple descriptions etc into one cell, right now it just dumps them all comma delimited.

Code:
$Users = Get-ADUser -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "DC=domain,DC=local"
Foreach ($User in $Users){
	$GroupName = $user.samaccountname | Get-ADPrincipalGroupMembership | select -ExpandProperty name
	$GroupDes = $user.samaccountname | Get-ADPrincipalGroupMembership | Get-ADGroup -Properties description | select -ExpandProperty description
	
	$UserInfo = New-Object -TypeName PSObject -Property @{
		UsersSamAccountName = $user.SamAccountName
		UserInGroup = $groupName -join ','
		ADGroupDes = $groupDes -join ','
    }
	$UserInfo | export-csv -Append -Path "C:\temp\test.csv"
}
 
Last edited:
Ah right, probably need to expand + join the objects being returned. I'll fix that up in a sec

edit: ok so this works, but damn it looks messy as hell :p there might be a better way of outputting multiple descriptions etc into one cell, right now it just dumps them all comma delimited.

Code:
$Users = Get-ADUser -LDAPFilter "(name=*)" -SearchScope Subtree -SearchBase "DC=domain,DC=local"
Foreach ($User in $Users){
    $GroupName = $user.samaccountname | Get-ADPrincipalGroupMembership | select -ExpandProperty name
    $GroupDes = $user.samaccountname | Get-ADPrincipalGroupMembership | Get-ADGroup -Properties description | select -ExpandProperty description
    
    $UserInfo = New-Object -TypeName PSObject -Property @{
        UsersSamAccountName = $user.SamAccountName
        UserInGroup = $groupName -join ','
        ADGroupDes = $groupDes -join ','
    }
    $UserInfo | export-csv -Append -Path "C:\temp\test.csv"
}

What Ver does -append work on?

I get an error "Export-Csv : A parameter cannot be found that matches parameter name 'Append'."

UPDATE

I believe it works only on v3 or v4 and up

doesnt work on v2, but works on v4.

Also, that fixed the dots... issue. Thank you Soul - you are a great asset here at TF :cool:

Ill see if I can find a way to try output into cell each rather than the entire csv dump in one cell.

Cheers though!
 
Last edited:
SOUL where are you?!

the script below works now -Im starting to use it as a template for others, but output in CSV is not right;

@{name=blahblah}

@{managedby=blahblbah}

etc..

shouldnt "@{expression={" stop that?

Cant figure out how to remove this

Code:
$list = Get-ADGroup -Filter * -SearchBase 'DC=Domain,DC=local'
Foreach($SG in $list){
$SGProps1 = get-adgroup $SG.samaccountname -properties * | Select name
$SGProps2 = get-adgroup $SG.samaccountname -properties * | select description
$SGProps3 = get-adgroup $SG.samaccountname -properties * | select whencreated
$SGProps4 = get-adgroup $SG.samaccountname -properties * | select managedby
$SGProps5 = $SG.samaccountname | Select-Object @{Expression={$SG.Name};Label='Group Name'},@{Expression={@(Get-ADGroupMember -Identity $SG |Select -Expand Name) -join ';'};Label='Name'}

$SGPropResults = New-object -TypeName PSObject -Property @{
SGName = $SGProps1 -join ','
SGDescription = $SGProps2 -join ','
SGWhenCreated = $SGProps3 -join ','
SGManagedby = $SGProps4 -join ','
SGMembers = $SGProps5 -join ','
}
$SGPropResults | export-csv -Append -Path "\\" -NoTypeInformation
}
 
I'll have a look in a few hours :p gotta get some **** sorted at work first

edit:

Ok, you shouldn't need to run that get-adgroup command 5 times in a row, and you also shouldn't need to do a -join for parameters which will only have one value (i.e. there can only be one "name" for an AD Group, one description, etc).

Try this

Code:
$list = Get-ADGroup -Filter * -SearchBase 'DC=domain,DC=local'
Foreach($SG in $list){
$SGProps = Get-ADGroup $SG.samaccountname -properties * | Select name, description, whencreated, managedby
$SGMembers = (Get-ADGroupMember -Identity $SG |Select -ExpandProperty Name) -join ';'

$SGPropResults = New-object -TypeName PSObject -Property @{
SGName = $SGProps.name
SGDescription = $SGProps.description
SGWhenCreated = $SGProps.whencreated
SGManagedby = $SGProps.managedby
SGMembers = $SGMembers
}
$SGPropResults | export-csv -Append -Path "c:\temp\0test.csv" -NoTypeInformation
}
 
Last edited:
I'll have a look in a few hours :p gotta get some **** sorted at work first

edit:

Ok, you shouldn't need to run that get-adgroup command 5 times in a row, and you also shouldn't need to do a -join for parameters which will only have one value (i.e. there can only be one "name" for an AD Group, one description, etc).

Try this

Code:
$list = Get-ADGroup -Filter * -SearchBase 'DC=domain,DC=local'
Foreach($SG in $list){
$SGProps = Get-ADGroup $SG.samaccountname -properties * | Select name, description, whencreated, managedby
$SGMembers = (Get-ADGroupMember -Identity $SG |Select -ExpandProperty Name) -join ';'

$SGPropResults = New-object -TypeName PSObject -Property @{
SGName = $SGProps.name
SGDescription = $SGProps.description
SGWhenCreated = $SGProps.whencreated
SGManagedby = $SGProps.managedby
SGMembers = $SGMembers
}
$SGPropResults | export-csv -Append -Path "c:\temp\0test.csv" -NoTypeInformation
}

Ah, I thought "-join" was needed so that if the character length is to long it still captures it?

THAT WORKS?! what caused the @{} to show up? I cant really see the difference besides the -joins not being used??
 
Last edited:
This bit:
Code:
$SGProps5 = $SG.samaccountname | Select-Object @{Expression={$SG.Name};Label='Group Name'},@{Expression={@(Get-ADGroupMember -Identity $SG |Select -Expand Name) -join ';'};Label='Name'}
vs
Code:
$SGMembers = (Get-ADGroupMember -Identity $SG |Select -ExpandProperty Name) -join ';'

-join is just for when you want to combine multiple strings into a single string. In our case we had an array of strings that we wanted to convert to a single string, so only a single value has to be written to the excel cell
 
Back
Top Bottom