unable to export data from loop

[matthew moore]

I want to export the username and what group it was removed from to csv. I can not figure out the proper syntax to do this .

 

Here is the code .  I have  tried to comment as much as i could. The code removes the users from groups and should add the results to the csv file. i have the write host that is telling me it is doing it but the results are not going to csv. No matter where i put the code or how i do it. Since this is a list it will be multiple users that will have to be in the csv file.

 

 

 

PowerShell

$users = Import-Csv -Path “C:\users\public\disabledUserRemoveFromGroup\DisabledUsers.CSV” Foreach ($username in $users) { #$username = $users.SamAccountName # Get all group memberships $groups = get-adprincipalgroupmembership $username.SamAccountName; # Loop through each group $data = foreach ($group in $groups) { # Exclude Domain Users group if ($group.name -ne "domain users") { # Remove user from group get-adgroup $group | remove-adgroupmember -member $username.SamAccountName -Confirm:$false # Write progress to screen write-host $username.SamAccountName "have been remove from the group - " $group.name } $data |export-csv c:\Users\public\results.csv -Append } }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

$users = Import-Csv -Path “C:\users\public\disabledUserRemoveFromGroup\DisabledUsers.CSV”   Foreach ($username in $users) {   #$username = $users.SamAccountName   # Get all group memberships $groups = get-adprincipalgroupmembership $username.SamAccountName;   # Loop through each group $data = foreach ($group in $groups) {   # Exclude Domain Users group if ($group.name -ne "domain users") {   # Remove user from group get-adgroup $group | remove-adgroupmember -member $username.SamAccountName -Confirm:$false   # Write progress to screen write-host $username.SamAccountName "have been remove from the group - " $group.name       } $data |export-csv c:\Users\public\results.csv -Append   } }

• This topic was modified 9 months, 3 weeks ago by matthew moore. Reason: make the question clearer

[Doug Maurer]

Hello Matthew,

First thing is the way you are assigning the output of everything in the foreach to $data, you may end up with unexpected information. You may not even know it’s happening without digging deeper. What I recommend is collecting each user/group combination that you act on in a new PSObject and then at the end exporting it all to csv at the end.

PowerShell

$users = Import-Csv -Path “C:\temp\DisabledUsers.CSV” $data = New-Object System.Collections.ArrayList Foreach ($username in $users) { #$username = $users.SamAccountName # Get all group memberships $groups = get-adprincipalgroupmembership $username.SamAccountName; # Loop through each group foreach ($group in $groups) { # Exclude Domain Users group if ($group.name -ne "domain users") { [void]$data.add($( new-object -typename psobject -property @{ Group = $($Group.name) User = $username.samaccountname })) # Remove user from group #get-adgroup $group | remove-adgroupmember -member $username.SamAccountName -Confirm:$false # Write progress to screen write-host $username.SamAccountName "have been remove from the group - " $group.name } } } $data | Export-Csv c:\Users\public\results.csv -Append -NoTypeInformation

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

$users = Import-Csv -Path “C:\temp\DisabledUsers.CSV”   $data = New-Object System.Collections.ArrayList   Foreach ($username in $users) {   #$username = $users.SamAccountName   # Get all group memberships $groups = get-adprincipalgroupmembership $username.SamAccountName;   # Loop through each group foreach ($group in $groups) {   # Exclude Domain Users group if ($group.name -ne "domain users") {   [void]$data.add($( new-object -typename psobject -property @{ Group = $($Group.name) User = $username.samaccountname })) # Remove user from group #get-adgroup $group | remove-adgroupmember -member $username.SamAccountName -Confirm:$false   # Write progress to screen write-host $username.SamAccountName "have been remove from the group - " $group.name   }   } } $data | Export-Csv c:\Users\public\results.csv -Append -NoTypeInformation

Here is the CSV from my test

PowerShell

<table style="width: 38.5714%;height: 222px"> <tbody> <tr> <td>User</td> <td>Group</td> </tr> <tr> <td>test</td> <td>Monitor C Drive</td> </tr> <tr> <td>test</td> <td>Target</td> </tr> <tr> <td>btest</td> <td>DG-Management</td> </tr> <tr> <td>btest</td> <td>Monitor C Drive</td> </tr> </tbody> </table>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

<table style="width: 38.5714%;height: 222px"> <tbody> <tr> <td>User</td> <td>Group</td> </tr> <tr> <td>test</td> <td>Monitor C Drive</td> </tr> <tr> <td>test</td> <td>Target</td> </tr> <tr> <td>btest</td> <td>DG-Management</td> </tr> <tr> <td>btest</td> <td>Monitor C Drive</td> </tr> </tbody> </table>

 

I hope this helps.

• This reply was modified 9 months, 3 weeks ago by Doug Maurer.

[Doug Maurer Participant Topics: 9 Replies: 707 Points: 2,848Rank: Community Hero]

Hi Matthew,

I’m not sure if my browser messed up or if my answer is being moderated. Just in case i’ll post it again. The way you’re assigning all the output of foreach group to $data, you may end up for more than you bargained for. What I recommend is controlling what is put in the $data variable via a new PSObject that contains each user/group combo that was acted upon. After collecting all the results you can export to CSV.

PowerShell

$users = Import-Csv -Path “C:\temp\DisabledUsers.CSV” $data = New-Object System.Collections.ArrayList Foreach ($username in $users) { #$username = $users.SamAccountName # Get all group memberships $groups = get-adprincipalgroupmembership $username.SamAccountName; # Loop through each group foreach ($group in $groups) { # Exclude Domain Users group if ($group.name -ne "domain users") { [void]$data.add($( new-object -typename psobject -property @{ Group = $Group.name User = $username.samaccountname })) # Remove user from group #get-adgroup $group | remove-adgroupmember -member $username.SamAccountName -Confirm:$false # Write progress to screen write-host $username.SamAccountName "have been remove from the group - " $group.name } } } $data | Export-Csv c:\Users\public\results.csv -Append -NoTypeInformation

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

$users = Import-Csv -Path “C:\temp\DisabledUsers.CSV”   $data = New-Object System.Collections.ArrayList   Foreach ($username in $users) {   #$username = $users.SamAccountName   # Get all group memberships $groups = get-adprincipalgroupmembership $username.SamAccountName;   # Loop through each group foreach ($group in $groups) {   # Exclude Domain Users group if ($group.name -ne "domain users") {   [void]$data.add($( new-object -typename psobject -property @{ Group = $Group.name User = $username.samaccountname })) # Remove user from group #get-adgroup $group | remove-adgroupmember -member $username.SamAccountName -Confirm:$false   # Write progress to screen write-host $username.SamAccountName "have been remove from the group - " $group.name   }   } } $data | Export-Csv c:\Users\public\results.csv -Append -NoTypeInformation

Here is the CSV content from my test

PowerShell

Get-Content C:\users\Public\results.csv "User","Group" "test","Monitor C Drive" "test","Target" "btest","DG-Management" "btest","Monitor C Drive"

1 2 3 4 5 6

Get-Content C:\users\Public\results.csv "User","Group" "test","Monitor C Drive" "test","Target" "btest","DG-Management" "btest","Monitor C Drive"

I hope this helps.

[matthew moore Participant Topics: 6 Replies: 23 Points: 35Rank: Member]

Wow. I have been working on this all morning and your solution is simple. Thank you so much.

I guess i will have to do some reading on array list and how to use it. I have never seen it done this way before.

 

Thank you again.

[matthew moore]

i have one more quick queston. Since this is new too me I added another property that i wanted – DN. it shows up on the report but i wanted to be in a different order. I guess the OCD in me.

the report now shows – Group , Username , DN

I would like to show up as Username , Group, DN

I tried changing the order as below but that did not help. Thank you in advance. I am not sure what happen to the users reply either. ODD

 

 

PowerShell

[void]$data.add($( new-object -typename psobject -property @{ User = $username.samaccountname Group = $($Group.name) DN = $username.DistinguishedName }))

1 2 3 4 5 6

[void]$data.add($( new-object -typename psobject -property @{ User = $username.samaccountname Group = $($Group.name) DN = $username.DistinguishedName }))

• This reply was modified 9 months, 3 weeks ago by matthew moore. Reason: thanking
• This reply was modified 9 months, 3 weeks ago by matthew moore.

[Doug Maurer Participant Topics: 9 Replies: 707 Points: 2,848Rank: Community Hero]

What you need is a ordered hashtable, which I believe didn’t come about until version 3.

PowerShell

$oht = [ordered]@{ User = $username.samaccountname Group = $Group.name DN = $username.distinguishedname } [void]$data.add($(new-object -typename psobject -property $oht))

1 2 3 4 5 6

$oht = [ordered]@{ User = $username.samaccountname Group = $Group.name DN = $username.distinguishedname } [void]$data.add($(new-object -typename psobject -property $oht))

This should do what you want, as long as you’ve populated DisabledUsers.csv with a column of data titled DistinguishedName

 

[matthew moore]

Odd. Now it is only showing the last result on the list only. If i have more than one it will not show it. Maybe it is replication. I will wait a bit. Thank you so much. This is helping me automate a lot of stuff.

• This reply was modified 9 months, 3 weeks ago by matthew moore.

[matthew moore Participant Topics: 6 Replies: 23 Points: 35Rank: Member]

thanks mate. It was replication. It is worked as expected.

[Doug Maurer Participant Topics: 9 Replies: 707 Points: 2,848Rank: Community Hero]

No worries, I’ll get you the address where you can automate a portion of your income my way. 😉

[Author]

Posts