Topic: 2 ForEach loops with output to SQL Server DB

This topic has 7 replies, 4 voices, and was last updated 8 months, 3 weeks ago by

GlynLamb75

Participant
.

Viewing 7 reply threads

Author

Posts

May 5, 2020 at 6:22 am #225588

GlynLamb75

Participant

Topics: 1

Replies: 3

Points: 19

Rank: Member

I have a butchered version of Dan Belcher’s ‘Active Directory to SQL’

Which gets a list of ADGroups , Filtered in one loop Then Gets the ADGroupMembers for each group and output to SQL Server database.

Getting the ADGroups , works , Getting the ADGroupMembers works however when writing to the SQL ServerDatabase it inserts each Group as expected but the members list is always the GroupMembers from the last Group.

If I have 4 Groups each with varying number of members the output always show 4 groups all with the same member list.

here is an abridged version of the script

&lt;#Setting up object variables
to be used for AD lookup#&gt;
$TIME = [datetime]::today.adddays(-$DAYS)
$Groups = (Get-AdGroup -filter * | Where {$_.name -like "*I&amp;O*"} )
$Arrayofmembers = Get-ADGroupMember -identity $Group.name -recursive | Get-ADUser -Properties samaccountname, displayname, objectclass, name, enabled, UserPrincipalName
&lt;#Connect and cleanup the AD table
Connection remains open for writting#&gt;
&lt;#Begin loop through the ADARRAY for
Variables and begin inserting Rows to table#&gt;

ForEach($Group in $Groups){

foreach ($Member in $Arrayofmembers) {
$GroupName = $Group.Name
$Name = $Member.name
$ObjectClass=$Member.Objectclass
#$DisplayName = $Member.Displayname
$DisplayName = $Member.DisplayName.replace("'","''")
$sam = $Member.samaccountname
$email = $Member.UserPrincipalName.replace("'","''")
$enabled = $Member.Enabled

$GroupName
$Name
$ObjectClass
$DisplayName
$sam
$Enabled
$email
}}

<#Setting up object variables

to be used for AD lookup#>

$TIME = [datetime]::today.adddays(-$DAYS)

$Groups = (Get-AdGroup -filter * | Where {$_.name -like "*I&O*"} )

$Arrayofmembers = Get-ADGroupMember -identity $Group.name -recursive | Get-ADUser -Properties samaccountname, displayname, objectclass, name, enabled, UserPrincipalName

<#Connect and cleanup the AD table

Connection remains open for writting#>

<#Begin loop through the ADARRAY for

Variables and begin inserting Rows to table#>

ForEach($Group in $Groups){

foreach ($Member in $Arrayofmembers) {

$GroupName = $Group.Name

$Name = $Member.name

$ObjectClass=$Member.Objectclass

#$DisplayName = $Member.Displayname

$DisplayName = $Member.DisplayName.replace("'","''")

$sam = $Member.samaccountname

$email = $Member.UserPrincipalName.replace("'","''")

$enabled = $Member.Enabled

$GroupName

$Name

$ObjectClass

$DisplayName

$sam

$Enabled

$email

}}

This topic was modified 8 months, 3 weeks ago by GlynLamb75.

May 5, 2020 at 6:24 am #225594

Olaf

Participant

Topics: 10
Replies: 2481
Points: 6,543
Rank: Community MVP

Glyn, welcome to Powershell.org. Please take a moment and read the very first post on top of the list of this forum: Read Me Before Posting! You’ll be Glad You Did!.

When you post code or error messages or sample data or console output format it as code, please.
In the “Text” view you can use the code tags “PRE“, in the “Visual” view you can use the format template “Preformatted“. You can go back edit your post and fix the formatting – you don’t have to create a new one.
Thanks in advance.

May 5, 2020 at 7:02 am #225612

Doug Maurer

Participant

Topics: 9

Replies: 707

Points: 2,848

Rank: Community Hero

Seems you just need to move your assignment of members inside the groups loop?

&lt;#Setting up object variables
to be used for AD lookup#&gt;
$TIME = [datetime]::today.adddays(-$DAYS)
$Groups = (Get-AdGroup -filter * | Where {$_.name -like "*I&amp;O*"} )
&lt;#Connect and cleanup the AD table
Connection remains open for writting#&gt;
&lt;#Begin loop through the ADARRAY for
Variables and begin inserting Rows to table#&gt;

ForEach($Group in $Groups){
    $Arrayofmembers = Get-ADGroupMember -identity $Group.name -recursive | Get-ADUser -Properties samaccountname, displayname, objectclass, name, enabled, UserPrincipalName
    foreach ($Member in $Arrayofmembers) {
        $Name = $Member.name
        $ObjectClass=$Member.Objectclass
        #$DisplayName = $Member.Displayname
        $DisplayName = $Member.DisplayName.replace("'","''")
        $sam = $Member.samaccountname
        $email = $Member.UserPrincipalName.replace("'","''")
        $enabled = $Member.Enabled
        
        $GroupName
        $Name
        $ObjectClass
        $DisplayName
        $sam
        $Enabled
        $email
    }
}

<#Setting up object variables

to be used for AD lookup#>

$TIME = [datetime]::today.adddays(-$DAYS)

$Groups = (Get-AdGroup -filter * | Where {$_.name -like "*I&O*"} )

<#Connect and cleanup the AD table

Connection remains open for writting#>

<#Begin loop through the ADARRAY for

Variables and begin inserting Rows to table#>

ForEach($Group in $Groups){

$Arrayofmembers = Get-ADGroupMember -identity $Group.name -recursive | Get-ADUser -Properties samaccountname, displayname, objectclass, name, enabled, UserPrincipalName

foreach ($Member in $Arrayofmembers) {

$Name = $Member.name

$ObjectClass=$Member.Objectclass

#$DisplayName = $Member.Displayname

$DisplayName = $Member.DisplayName.replace("'","''")

$sam = $Member.samaccountname

$email = $Member.UserPrincipalName.replace("'","''")

$enabled = $Member.Enabled

$GroupName

$Name

$ObjectClass

$DisplayName

$sam

$Enabled

$email

}

May 5, 2020 at 7:28 am #225615

GlynLamb75

Participant

Topics: 1

Replies: 3

Points: 19

Rank: Member

Not exactly sure what you mean

do you mean

ForEach($Group in $Groups){ 
$GroupName = $Group.Name
$Name = $Member.name
$ObjectClass=$Member.Objectclass
#$DisplayName = $Member.Displayname
$DisplayName = $Member.DisplayName.replace("'","''")
$sam = $Member.samaccountname
$email = $Member.UserPrincipalName.replace("'","''")
$enabled = $Member.Enabled

$GroupName 
$Name 
$ObjectClass
$DisplayName 
$sam
$Enabled
$email

$X = $X + 1 
foreach ($Member in $Arrayofmembers) {

}

}

ForEach($Group in $Groups){

$GroupName = $Group.Name

$Name = $Member.name

$ObjectClass=$Member.Objectclass

#$DisplayName = $Member.Displayname

$DisplayName = $Member.DisplayName.replace("'","''")

$sam = $Member.samaccountname

$email = $Member.UserPrincipalName.replace("'","''")

$enabled = $Member.Enabled

$GroupName

$Name

$ObjectClass

$DisplayName

$sam

$Enabled

$email

$X = $X + 1

foreach ($Member in $Arrayofmembers) {

}

May 5, 2020 at 7:33 am #225618

GlynLamb75

Participant

Topics: 1
Replies: 3
Points: 19
Rank: Member

Sorry, I reread you post and I think you have solved it.

Thanks
May 5, 2020 at 4:26 pm #225771

Doug Maurer

Participant

Topics: 9
Replies: 707
Points: 2,848
Rank: Community Hero

Good. Yeah I think you had everything you needed, just needed to move the $arrayofmembers assignment inside the group loop. I wasn’t sure what the variables at the bottom were, I assumed placeholder for where you write to SQL.

May 5, 2020 at 6:56 pm #225801

Rob Simmers

Participant

Topics: 17

Replies: 1958

Points: 4,020

Rank: Community Hero

Also, put the output in the PSObject:

&lt;#Setting up object variables
to be used for AD lookup#&gt;
$TIME = [datetime]::today.adddays(-$DAYS)
$Groups = (Get-AdGroup -filter * | Where {$_.name -like "*I&amp;O*"} )
&lt;#Connect and cleanup the AD table
Connection remains open for writting#&gt;
&lt;#Begin loop through the ADARRAY for
Variables and begin inserting Rows to table#&gt;

$results = ForEach($Group in $Groups){
    $Arrayofmembers = Get-ADGroupMember -identity $Group.name -recursive | Get-ADUser -Properties samaccountname, displayname, objectclass, name, enabled, UserPrincipalName
    foreach ($Member in $Arrayofmembers) {
       [PSCustomObject]@{
              Name        = $Member.name
              ObjectClass = $Member.Objectclass
              DisplayName = $Member.DisplayName.replace("'","''")
              Sam         = $Member.samaccountname
              Email       = $Member.UserPrincipalName.replace("'","''")
              Enabled     = $Member.Enabled
       }
    }
}

$results

<#Setting up object variables

to be used for AD lookup#>

$TIME = [datetime]::today.adddays(-$DAYS)

$Groups = (Get-AdGroup -filter * | Where {$_.name -like "*I&O*"} )

<#Connect and cleanup the AD table

Connection remains open for writting#>

<#Begin loop through the ADARRAY for

Variables and begin inserting Rows to table#>

$results = ForEach($Group in $Groups){

$Arrayofmembers = Get-ADGroupMember -identity $Group.name -recursive | Get-ADUser -Properties samaccountname, displayname, objectclass, name, enabled, UserPrincipalName

foreach ($Member in $Arrayofmembers) {

[PSCustomObject]@{

Name = $Member.name

ObjectClass = $Member.Objectclass

DisplayName = $Member.DisplayName.replace("'","''")

Sam = $Member.samaccountname

Email = $Member.UserPrincipalName.replace("'","''")

Enabled = $Member.Enabled

}

$results

May 6, 2020 at 3:10 am #225867

GlynLamb75

Participant

Topics: 1
Replies: 3
Points: 19
Rank: Member

Thanks for your responses. All fixed and working

Author

Posts

Viewing 7 reply threads

The topic ‘2 ForEach loops with output to SQL Server DB’ is closed to new replies.

2 ForEach loops with output to SQL Server DB

Sign in to your account