“This row already belongs to this table” when addi

Question

Good afternoon Stack Overflow.

I am trying to automate some paper based processes using Powershell. We already had in place a Powershell solution where the output was an Excel Spreadsheet, but for planning for the future we felt that a CSV output was easier to work with than Excel.

What we are doing is pretty basic, we are reaching out to the owners of groups in AD, specified in a text file. For each group listed, we're looping through and adding the items to the table. Here's the problem, I am still very new to Powershell and am having issues looping through the 'for each' statements cleanly.

I am experiencing the following error:

    Exception calling "Add" with "1" argument(s): "This row already belongs to this table."
    At line:77 char:17
    + $table2.Rows.Add <<<< ($row2)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

I feel it's appropriate to share the code I am working with:

import-module activedirectory
$strGroupList = get-content "C:\Users\MYUSERNAME\Documents\Group Audit\audit.txt"
$strDate = Get-Date

foreach ($strGroup in $strGroupList)
{
$strGroupMember = Get-aduser -Identity $Group -property description
$tabName = "GroupAuditTable"

#Create the Table Object
$table = New-Object system.Data.DataTable "$tabName"
$table2 = New-Object system.Data.DataTable "$tabName2"

#define columns for row 1
$col1 = New-Object system.Data.DataColumn $strGroup,([string])
$col2 = New-Object system.Data.DataColumn $strDate,([string])

#define columns for row 2
$col3 = New-Object system.Data.DataColumn Name,([string])
$col4 = New-Object system.Data.DataColumn Description, ([string])
$col5 = New-Object system.Data.DataColumn NetworkID, ([string])
$col6 = New-Object system.Data.DataColumn Nested, ([string])

#Add the columns to row 1
$table.columns.add($col1)
$table.columns.add($col2)

#Add the columns to row 2
$table2.columns.add($col3)
$table2.columns.add($col4)
$table2.columns.add($col5)
$table2.columns.add($col6)

#create a row
$row = $table.NewRow()
$row2 = $table2.NewRow()

#create an additional row for the titles of columns
$rowTitles = $table.NewRow()
$rowTitles2 = $table2.NewRow()

$strGroupDetails = Get-ADGroupMember -identity $strGroup 
foreach ($Group in $strGroupDetails)
    {
##########################################################################  Check for nested groups        
        if($Group.ObjectClass -eq "group")
        {
        $strGroupDetails = Get-ADGroupMember -identity $Group #-Recursive
        $strNestedGroupName = $Group.name
##########################################################################  List members of nested groups
        foreach($strNestedGroup in $strGroupDetails)
            {
            #$strNestedGroupName = $Group.name
            $strGroupMember = Get-aduser -Identity $StrNestedGroup -property description

            $row2.Name = $strGroupMember.name
            $row2.Description = $strGroupMember.description
            $row2.NetworkID =  $strGroupMember.SamAccountName
            $row2.Nested = "(From NESTED GROUP:  " + $strNestedGroupName
            $table2.Rows.Add($row2)
            }
        }

        else
        {
#enter data into row 2

$row2.Name = $strGroupMember.name
$row2.Description = $strGroupMember.description
$row2.NetworkID =  $strGroupMember.SamAccountName
$row2.Nested = "Not Nested"

#Add the row to the table
$table.Rows.Add($row)

#Add the row to the second table
$table2.Rows.Add($row2)

#Display the table
$table | format-table -AutoSize
$table2 | format-table -AutoSize
}




}

}
$tabCsv = $table | export-csv C:\Users\MYUSERNAME\Desktop\Audit\GroupAudit.csv -noType
$tabCsv2 = $table2 | export-csv C:\Users\MYUSERNAME\Desktop\Audit\GroupAudit_2.csv -noType

That's it, pretty basic stuff.

The error is on line #77:

$table2.Rows.Add($row2)

This worked before I stuck the 'Nested Groups' For Each loop in there, and I am just puzzled as to why that would break it, unless I am missing something obvious here. When I remove this:

foreach ($Group in $strGroupDetails)
    {
##########################################################################  Check for nested groups        
        if($Group.ObjectClass -eq "group")
        {
        $strGroupDetails = Get-ADGroupMember -identity $Group #-Recursive
        $strNestedGroupName = $Group.name
##########################################################################  List members of nested groups
        foreach($strNestedGroup in $strGroupDetails)
            {
            #$strNestedGroupName = $Group.name
            $strGroupMember = Get-aduser -Identity $StrNestedGroup -property description

            $row2.Name = $strGroupMember.name
            $row2.Description = $strGroupMember.description
            $row2.NetworkID =  $strGroupMember.SamAccountName
            $row2.Nested = "(From NESTED GROUP:  " + $strNestedGroupName
            $table2.Rows.Add($row2)
            }
        }

        else
        {
#enter data into row 2

Things work as expected, what am I missing?

Answer 1

You are running a ForEach and the first thing is an If clause where you define $strGroupMember, which you reference in the Else clause for the same If/Then. Here, this is simplified:

IF($Group.ObjectClass -eq "group"){
    <do stuff>
    $strGroupMember = get-aduser $strnestedgroup <more parameters>
    $row2.stuff = $strgroupmember.stuff
}
Else{
    $row2.stuff = $strgroupmember.stuff
}

But you don't define $strgroupmember in the else scriptblock at all. So it's then adding the last one from the last group, and it already has that entry, so it throws an error.

With fake data.... Group is Administrators, it contains 1 group ("Payroll" which has 1 member, "John") and 2 users ("Jim" and "Sally").

ForEach($Item in get-adgroupmember "Administrators"){
    if($Item.ObjectClass -eq "group"){ #Payroll is a group, it gets processed here
        ForEach($User in $Item){
            $Userdata = Get-ADUser $User #Returns Jim's AD info
            $Row2.name = $Userdata.name
            $Row2.description = $Userdata.description
            $Table2.add($Row2) #Adds John to the table
        }
    }
Else{ #if not a group, process root users here, should process Jim and Sally
    $Row2.Name = $Userdata.name #$Userdata still contains John's info
    $Row2.Description = $Userdata.Description #$Userdata still contains John's info
    $Table2.add($Row2) #attempts to add John to the table again
}

Hopefully that all made sense. Or at least enough of it that you see the issue. The fix? Add one line to your Else scriptblock:

$strGroupMember = Get-aduser -Identity $Group -property description

Edit: I'd make an array, create user object and add them to the array, and then iterate through nested groups and add that group to each user object that it applies to, then push that data into a table so you'd end up with something like:

Name        Description        NetworkID        Nested
John        John Smith         JSmith           Payroll,HR
Mark        Mark Jones         MJones           Not Nested
Mary        Mary Anderston     MAnderson        HR

But that's me.

Edit2: Ok, round 2! I see changes, but there's still some issues for sure. The issue you are coming across is adding rows to the table. Ok, understandable. Here's some issues I see:

foreach($strNestedGroup in $strGroupDetails)
        {
        #$strNestedGroupName = $Group.name
        $strGroupMember = Get-aduser -Identity $StrNestedGroup -property description


        $row2.Nested = "(From NESTED GROUP:  " + $strNestedGroupName
        $table2.Rows.Add($row2)
        }

Ok, for each item in the nested group query AD and store the results in $strGroupMember. Then update $row2.nested to the current group's name, and add $row2 to the table as a new row. So let's just say we enter this loop with $row2 being this:

$row2.Name = "John Smith"
$row2.Description = "Director of HR"
$row2.NetworkID = "JSmith"
$row2.Nested = "Not Nested"

Ok, it's looping through the entries that it pulled for the nested group "Power Users". We'll make this easy and assume there are not additional groups nested within this nested group, that's a whole other issue.

Item 1 on that list is Marcia Winkle, she has an AD description of "Payroll", and a user name of MWinkle. The loop found that out and assigned all her info to $strGroupMember.

Next it updates $row2, and replaces the Nested value, replacing "Not Nested" with "From nested group: Power Users" (the group we are working with).

Then it adds $row2 to $table2, creating a row:

Name          Description       NetworkID       Nested
John Smith    Director of HR    JSmith          From nested group: Power Users

Ok, done! Next member of the group is Ethel Jones. She too has the description of Payroll, and her user name is EJones. The loop pulled that from AD, and replaced any existing values of $strGroupMember with them. Not that it matters, we didn't seem to ever use any of that info in the last loop anyway, but that's not the script's concern, it just powers through!

Next, we update $row2.Nested again, changing "From nested group: Power Users" to "From nested group: Power Users" ...huh, same thing as before, but ok, you're the boss. Moving on!

Now it attempts to add another row to the table, but here's where we get an error: It's the exact same data as last time since nothing in $row2 was really changed, it's still:

Name          Description       NetworkID       Nested
John Smith    Director of HR    JSmith          From nested group: Power Users

You see where I'm going here? Next issue I see is that you are doing an If/Then/Else cycle here, but you are trying to add rows in the Then section, but not in the Else section. Here, take this ideology and update your code accordingly:

If (Is a Group) Then
    For Each Member of the group, do this
        query AD to get user details
        Update the $row2 variable with the user's details and the current group
        Add $row2 to the table
    End For Each loop
Else (if it is NOT a group)
    query AD to get the user's details
    Update the $row2 variable with the user's details and the current group
    Add $row2 to the table
End Else section

This should clear up your current errors. It doesn't recurse more than one level of groups, so you will miss people if there's a group within a group, within a group. As in:

Main Group
--->Nested Group
    --->Another Nested Group
        ->Billy Mac

That will include the first nested group, and the second nested group, but unless he is represented somewhere else Billy Mac will not make it in your list.

As for my solution, I had most of this written up last night so I finished it up today. This should do what you want I think. It creates an empty array, then populates it with an object for every user found recursively. Then it cycles through all nested groups and adds that group's name to the Nested property of the object of each user in it. Then it outputs that array as a CSV file for each group processed.

import-module activedirectory

#Define a function to add a group's name to its members Nested field recursivly.
Function GetNestedMembers{
Param($Group)
    ForEach($Item in (Get-ADGroupMember $Group)){
    if($Item.ObjectClass -eq "group" -and $Global:SubGroups -inotcontains $Item.name){
        $Global:SubGroups += $Item.name.tostring()
        GetNestedMembers $Item
    }else{
        $AllMembers|?{$_.Name -match $Item.Name -and !($_.nested -match $group.name)}|%{$_.Nested = "$($_.Nested), $($Group.Name.tostring())"}
        }
    }
}

$GroupList = get-content "C:\Users\MYUSERNAME\Documents\Group Audit\audit.txt"

ForEach($Entry in $GroupList){

    $SubGroups = @()

    #Create an empty array
    $AllMembers = @()

    #Populate it with all recursive members of the group
    ForEach($Person in (Get-ADGroupMember $Entry -Recursive)){
        $User = Get-ADUser $Person -Property description
        $AllMembers += New-Object PSObject -Property @{
            Name = $Person.Name
            Description = $User.Description
            NetworkID = $Person.SamAccountName
            Nested = $Null
        }
    }    

    $CurrentGroup = Get-ADGroupMember $Entry

    #Mark root members as direct group members in the Nested field
    $AllMembers|?{($CurrentGroup | ?{$_.ObjectClass -ne "group"}).name -contains $_.Name}|%{$_.Nested = "Direct Member"}

    #Iterate through all nested groups
    $CurrentGroup | ?{$_.ObjectClass -eq "group"} | %{GetNestedMembers $_}

    #If the output path doesn't exist, make it quietly.
    If(!(Test-Path "C:\Users\MYUSERNAME\Documents\Group Audit\groups\")){$null = New-Item "C:\Users\MYUSERNAME\Documents\Group Audit\groups\" -ItemType directory}

    #Output to CSV
    $AllMembers |%{if($_.nested){$_.nested = $_.nested.TrimStart(", ")};$_} | Select Name, Description, NetworkID, Nested | Export-csv "C:\Users\MYUSERNAME\Documents\Group Audit\groups\$Entry.csv" -NoTypeInformation
}