2 ForEach loops with output to SQL Server DB

Welcome Forums General PowerShell Q&A 2 ForEach loops with output to SQL Server DB

Viewing 7 reply threads
  • Author
    Posts
    • #225588
      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

      <#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 4 weeks, 1 day ago by GlynLamb75.
    • #225594
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      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.

    • #225612
      Participant
      Topics: 3
      Replies: 342
      Points: 1,130
      Helping Hand
      Rank: Community Hero

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

      <#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
          }
      }
      
    • #225615
      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) {
      
      }
      
      }
    • #225618
      Participant
      Topics: 1
      Replies: 3
      Points: 19
      Rank: Member

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

      Thanks

       

    • #225771
      Participant
      Topics: 3
      Replies: 342
      Points: 1,130
      Helping Hand
      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.

    • #225801
      Participant
      Topics: 12
      Replies: 1623
      Points: 2,565
      Helping Hand
      Rank: Community Hero

      Also, put the output in the PSObject:

      <#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
      
    • #225867
      Participant
      Topics: 1
      Replies: 3
      Points: 19
      Rank: Member

      Thanks for your responses. All fixed and working

Viewing 7 reply threads
  • You must be logged in to reply to this topic.