“该行已经属于该表”当使用POWERSHELL向表中添加数据时 [英] "This row already belongs to this table" when adding data to a table using POWERSHELL
问题描述
我正在尝试使用Powershell自动执行一些基于纸张的流程。我们已经有一个Powershell解决方案,其中的输出是一个Excel电子表格,但是为了规划未来,我们认为CSV输出比Excel更容易使用。
我们正在做的是非常基本的,我们正在接触到一个文本文件中指定的AD中的组的所有者。对于列出的每个组,我们循环并将项添加到表中。这是问题,我对Powershell仍然很新,并且正在通过for each语句进行循环遍历。
我遇到以下错误:
异常调用添加用1参数:这行已经属于这个表。
在行:77 char:17
+ $ table2.Rows.Add<<<<< ($ row2)
+ CategoryInfo:NotSpecified:(:) [],MethodInvocationException
+ FullyQualifiedErrorId:DotNetMethodException
我觉得分享我正在使用的代码是合适的:
import-module activedirectory
$ strGroupList = get-contentC:\Users\MYUSERNAME\Documents\Group Audit\audit.txt
$ strDate = Get-Date
foreach( $ strGroup in $ strGroupList
{
$ strGroupMember = Get-aduser -Identity $ Group -property描述
$ tabName =GroupAuditTable
#创建表对象
$ table =新对象system.Data.DataTable$ tabName
$ table2 =新对象system.Data.DataTable$ tabName2
#define列对于行1
$ col1 =新对象system.Data.DataColumn $ strGroup,([string])
$ col2 =新对象system.Data.DataColumn $ strDate,([string])
第2行的#define列
$ col3 = New-Object system.Data。 DataColumn Name,([string])
$ col4 = New-Object system.Data.DataColumn描述,([string])
$ col5 =新对象system.Data.DataColumn NetworkID,([string ])
$ col6 =新对象system.Data.DataColumn嵌套,([string])
#将列添加到行1
$ table.columns.add( $ col1)
$ table.columns.add($ col2)
#将列添加到行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列的标题的另外一行
$ rowTitles = $ table.NewRow()
$ rowTitles2 = $ table2.NewRow()
$ strGroupDetails = Get-ADGroupMember -identity $ strGroup
foreach($ group in $ strGroupDetails)
{
######################################## #########################检查嵌套组
if($ Group.ObjectClass -eq组)
{
$ strGroupDetails = Get-ADGroupMember -identity $ Group#-Recursive
$ strNestedGroupName = $ Group.name
################ ################################################## #######列出嵌套组的成员
foreach($ strGroupDetails中的$ strNestedGroup)
{
#$ strNestedGroupName = $ Group.name
$ strGroupMember = Get- aduser -Identity $ StrNestedGroup -property描述
$ row2.Name = $ strGroupMember.name
$ row2.Description = $ strGroupMember.description
$ row2.NetworkID = $ strGroupMember。 SamAccountName
$ row2.Nested =(从NESTED GROUP:+ $ strNestedGroupName
$ table2.Rows.Add($ row2)
}
}
else
{
#enter数据到行2
$ row2.Name = $ strGroupMember.name
$ row2.Description = $ strGroupMember.description
$ row2.NetworkID = $ strGroupMember.SamAccountName
$ row2.Nested =Not Nested
#将行添加到表
$ table.Rows.Add($ row)
#将行添加到第二个表
$ table2.Rows.Add($ row2)
#显示表
$ table |格式表-AutoSize
$ table2 |格式表-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
这就是它的基本原理。
错误位于第77行:
$ table2。 Rows.Add($ row2)
在我将嵌套组在那里,我只是很困惑,为什么会打破它,除非我在这里缺少一些明显的东西。当我删除这个:
foreach($ group in $ strGroupDetails)
{
#### ################################################## #####################检查嵌套组
if($ Group.ObjectClass -eqgroup)
{
$ strGroupDetails = Get-ADGroupMember -identity $ Group#-Recursive
$ strNestedGroupName = $ Group.name
###################### ################################################## #列出嵌套组的成员
foreach($ strGroupDetails中的$ strNestedGroup)
{
#$ strNestedGroupName = $ Group.name
$ strGroupMember = Get-aduser -Identity $ StrNestedGroup - 属性描述
$ row2.Name = $ strGroupMember.name
$ row2.Description = $ strGroupMember.description
$ row2.NetworkID = $ strGroupMember.SamAccountName
$ row2.Nested =(从NESTED GROUP:+ $ strNestedGroupName
$ table2.Rows.Add($ row2)
}
}
else
{
#enter数据到行2
事情按预期工作,我缺少什么?
IF($ Group.ObjectClass -eqgroup){
< do东西>
$ strGroupMember = get-aduser $ strnestedgroup< more parameters>
$ row2.stuff = $ strgroupmember.stuff
}
Else {
$ row2.stuff = $ strgroupmember.stuff
}
但是,您并没有在else脚本块中定义$ strgroupmember。所以,然后添加最后一个组中的最后一个组,它已经有该项,所以它会抛出一个错误。
使用假数据....组是管理员,它包含1个组(Payroll,其中有1个成员,John)和2个用户(Jim和Sally)。
code> ForEach($ item in get-adgroupmemberAdministrators){
if($ Item.ObjectClass -eqgroup){#Payroll是一个组,它在这里处理
ForEach ($用户$项){
$ Userdata = Get-ADUser $ User#返回Jim的AD信息
$ Row2.name = $ Userdata.name
$ Row2.description = $ Userdata。描述
$ Table2.add($ Row2)#将John添加到表
}
}
否则{#if不是一个组,在这里处理root用户,应该处理Jim和Sally
$ Row2.Name = $ Userdata.name#$ Userdata还包含约翰的信息
$ Row2.Description = $ Userdata.Description#$ Userdata还包含约翰的信息
$ Table2.a dd($ Row2)#尝试将John添加到表中
}
希望都有道理或至少足够你看到这个问题。修复?添加一行到您的Else脚本块:
$ strGroupMember = Get-aduser -Identity $ Group -property描述
编辑:我将创建一个数组,创建用户对象并将其添加到数组中,然后迭代嵌套组,并将该组添加到其应用于的每个用户对象,然后将该数据推送到表中,以便最终得到以下结果:
名称描述NetworkID嵌套
John John Smith JSmith工资单HR
Mark Mark Jones MJones不嵌套
Mary Mary Anderston MAnderson HR
但这是我。
Edit2:
foreach($ strGroupDetails中的$ strNestedGroup)
{
#$ strNestedGroupName = $ Group.name
$ strGroupMember = Get-aduser -Identity $ StrNestedGroup -property描述
$ row2.Nested =(从NESTED GROUP:+ $ strNestedGroupName
$ table2.Rows.Add($ row2)
}
嵌套组中的每个项目查询AD,并将结果存储在 $ strGroupMember
中,然后将 $ row2.nested
更新为当前组的名称,并将 $ row2
添加到表中作为新行,所以我们来说说我们用 $ row2
是这样的:
$ row2.Name =John Smith
$ row2.Description =Director的人力资源
$ row2.NetworkID =JSmith
$ row2.Nested =不嵌套
好的,它循环通过它为嵌套组Power提取的条目用户。我们将使这很容易,并假设这个嵌套组中没有嵌套的其他组,这是另一个问题。
该列表中的项目1是Marcia Winkle,她具有Payroll的AD描述和MWinkle的用户名。循环发现,将所有信息分配给 $ strGroupMember
。
接下来更新 $ row2
,并替换嵌套
值,将未嵌套替换为从嵌套组:高级用户(我们正在工作的组) $)
然后将 $ row2
添加到 $ table2
,创建一行:
名称描述NetworkID嵌套
John Smith人力资源总监JSmith从嵌套组:电源用户
好的,完成!该组的下一个成员是Ethel Jones。她也有工资单的描述,她的用户名是EJones。循环从AD中拉出,并将它们替换为 $ strGroupMember
的任何现有值。不是这么重要,我们似乎在最后一个循环中似乎没有使用任何信息,但这不是剧本的关注,它只是通过!
接下来,我们再次更新 $ row2.Nested
,将从嵌套组:高级用户更改为从嵌套组:高级用户...与以前相同的事情,但是,你是老板。继续前进!
现在,它尝试向表中添加另一行,但这里是我们收到错误的地方:与上一次完全相同的数据,因为$ row2中没有真的改变了,它仍然是:
名称描述NetworkID嵌套
John Smith人力资源总监JSmith从嵌套组:电源用户
你看到我要去哪里?我看到的下一个问题是你在这里做一个If / Then / Else循环,但是你尝试在Then部分添加行,而不是在Else部分。在这里,采取这种意识形态并相应地更新您的代码:
如果(是一个组)然后
对于每个成员该组,执行此
查询AD以获取用户详细信息
更新$ row2变量与用户的详细信息和当前组
添加$ row2到表
结束对于每个循环
否则(如果不是一个组)
查询AD以获取用户的详细信息
更新$ row2变量与用户的详细信息和当前组
将$ row2添加到表
结束其他部分
这应该会清除您当前的错误。它不会递归多个级别的组,所以如果组内有组,则会在组中怀念人。如下:
主组
--->嵌套组
--->另一个嵌套组
- > Billy Mac
这将包括第一个嵌套组,第二个嵌套组,但除非他代表其他地方,否则Billy Mac将不会在您的列表中。
至于我的解决方案,我昨天写的大部分所以我今天完成了。这应该做你想要的想法。它创建一个空数组,然后用递归发现的每个用户填充一个对象。然后它循环遍历所有嵌套组,并将该组的名称添加到其中每个用户的对象的Nested属性。然后它将该数组作为处理的每个组的CSV文件输出。
import-module activedirectory
#定义一个函数来向其成员添加一个组的名称Nested field recursivly。
函数GetNestedMembers {
Param($ Group)
ForEach($ Item in(Get-ADGroupMember $ Group)){
if($ Item.ObjectClass -eqgroup和$ Global:SubGroups -inotcontains $ Item.name){
$ Global:SubGroups + = $ Item.name.tostring()
GetNestedMembers $ Item
} else {
$ AllMembers |?{$ _。Name -match $ Item.Name -and!($ _。nested -match $ group.name)} |%{$ _。嵌套=$($ _。嵌套),$($ group.Name.tostring())}
}
}
}
$ GroupList = get-contentC:\Users\MYUSERNAME\ Documents\Group Audit\audit.txt
ForEach($ Entry in $ GroupList){
$ SubGroups = @()
#创建一个空数组
$ AllMembers = @()
#Populate它与组的所有递归成员
ForEach($ Person in(Get-ADGroupMember $ Entry -Recursive )){
$ User = Get-ADUser $ Person -Property描述
$ AllMembers + =新对象PSObject -Property @ {
名称= $ Person.Name
描述= $ User.Description
NetworkID = $ Person.SamAccountName
嵌套= $ Null
}
}
$ CurrentGroup = Get-ADGroupMember $ Entry
#将嵌套字段中的直接组成员的根成员标记为
$ AllMembers |?{($ CurrentGroup | ?{$ _。ObjectClass -negroup})。name -contains $ _。Name} |%{$ _。Nested =Direct Member}
#通过所有嵌套组b $ b $ CurrentGroup | ?{$ _。ObjectClass -eqgroup} | %{GetNestedMembers $ _}
#如果输出路径不存在,请保持静音。
如果(!(Test-PathC:\Users\MYUSERNAME\Documents\Group Audit\groups\)){$ null = New-ItemC:\Users\ MYUSERNAME\Documents\Group Audit\groups\-ItemType directory}
#Output to CSV
$ AllMembers |%{if($ _。nested){$ _。嵌套= $ _。nested.TrimStart(,)}; $ _} |选择名称,说明,NetworkID,嵌套| Export-csvC:\Users\MYUSERNAME\Documents\Group Audit\groups\ $ Entry.csv-NoTypeInformation
}
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?
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
}
这篇关于“该行已经属于该表”当使用POWERSHELL向表中添加数据时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!