Trouble with a small foreach loop with 2 cmdlets, Get-ADuser and Invoke-Sqlcmd

Tagged: 

This topic contains 7 replies, has 2 voices, and was last updated by Profile photo of Charles Downing Charles Downing 4 weeks, 1 day ago.

  • Author
    Posts
  • #57215
    Profile photo of Jordan Obas
    Jordan Obas
    Participant

    I've created a small foreach loop with a goal of getting users in a particular AD group using get-aduser for their GivenName, Surname, Samaccountname, and Department, run a SQL query for the user which value has a multi row result and export it all the results to a CSV with the results spanning 5 cells, 4 for the Users info and the 5th for the query results. The issue is that each cmdlet works on its own in the loop, but when both are added in, the SQL queries results come up as $NULL but with the correct amount of $NULL's to how many correct results should have been shown.

    I've created a post over on reddit/r/powershell with more details of what I have tried so far and what a helpful fellow has tried to help me make it work to no avail.

    Here is the post with my code and corrections along the way: https://www.reddit.com/r/PowerShell/comments/5buvvc/trouble_with_a_basic_foreach_loop_containing_2/

  • #57232
    Profile photo of Charles Downing
    Charles Downing
    Participant

    Looks like you figured it out with help from Reddit... What was the final script/solution?

    • #57235
      Profile photo of Jordan Obas
      Jordan Obas
      Participant

      The code the user proposed had a typo in the CustomObject block, instead of SYS_USER_CLASSES he typed SYS_USER_CLASS and returned the correct values instead of $NULL. The results aint pretty but it works! Here is the final script:

      $UIUsers = Get-ADGroupMember Test_Group  
      $SQLServerName = "Server"  
      $SQLDBName = "Database" 
      
      $outputObject = New-Object System.Collections.Generic.List[object]
      
      foreach ($User in $UIUsers) {
      
          $Username = $User.SamAccountName.ToUpper()  
      
          $userResult = Get-ADuser -Properties Department -identity $Username
      
          $sqlResult = Invoke-Sqlcmd -ServerInstance $SQLServerName -Database $SQLDBName -Query "
              SELECT SYS_USER_CLASSES
              from OPERS_LS
              where SYS_USER_ID in ('$Username')
          "
      
          $outputObject.Add(
              [pscustomobject]@{
              GivenName = $userResult.GivenName;
              Surname   = $userResult.Surname;
              SamAccountName = $userResult.SamAccountName;
              Department     = $userResult.Department;
              SysUserClass   = $sqlResult.SYS_USER_CLASSES -join '","';
              })
      
          }  
      
          $outputObject | Export-CSV cmdlet to Export to a CSV

      I didn't know how to have each result of the query occupy its own cell so I settled for doing some cleanup after the file generated.

      SysUserClass   = $sqlResult.SYS_USER_CLASSES -join '","';

      This would separate the values of the results like

      RESULT1","RESULT2","RESULT3","....

      all into one cell in the CSV file and like

      RESULT1"",""RESULT2"",""RESULT3"",""....

      when looking at it in notepad. So I opened it up and did a good ol' find and replace of "","" to ",".

    • #57244
      Profile photo of Charles Downing
      Charles Downing
      Participant

      Cool! Pushing each of the results of the SQL query to a different cell probably wouldn't be too difficult, especially if you know how many results you're going to get.

      "$sqlResult.SYS_USER_CLASSES" will just be an array, so you can reference each element in the array and assign each to a different key in your new PSCustomObject.

      ...
      $outputObject.Add(
           [pscustomobject]@{
           GivenName = $userResult.GivenName;
           Surname   = $userResult.Surname;
           SamAccountName = $userResult.SamAccountName;
           Department     = $userResult.Department;
           SysUserClass1   = $sqlResult.SYS_USER_CLASSES[0];
           SysUserClass2   = $sqlResult.SYS_USER_CLASSES[1];
           SysUserClass3   = $sqlResult.SYS_USER_CLASSES[2];
           })
      ...
      

      If you don't know how many results you're going to get back from the query, that will make it a little more complicated...

    • #57251
      Profile photo of Jordan Obas
      Jordan Obas
      Participant

      Sadly that is the case, some users have 1 result, while others have up to 20+ but that is an interesting though I will play around with to see if I can at least make it friendlier to edit since I will be reusing this script often.

    • #57254
      Profile photo of Charles Downing
      Charles Downing
      Participant

      I'm not testing this code, so don't use it verbose. But you should be able to do something like this, and I think that Export-CSV will handle it:

      $obj = [pscustomobject]@{
                  GivenName = $userResult.GivenName;
                  Surname   = $userResult.Surname;
                  SamAccountName = $userResult.SamAccountName;
                  Department     = $userResult.Department;
              }
      
              $i = 0
              $sqlResult.SYS_USER_CLASSES | 
                  ForEach-Object {
                      $colName = "SysUserClass_$i"
                      $obj | Add-Member -NotePropertyName $colName -NotePropertyValue $_
                      $i++
                  }
      
              $outputObject.Add($obj)
      
    • #57265
      Profile photo of Jordan Obas
      Jordan Obas
      Participant

      So this is what I have which includes your snippet at the bottom (not sure if that was the optimal spot for it). I somewhat understand your codes logic but it fails at the Add-Member part saying there's already a member with that name. I'm still learning PowerShell so this should be a fun exercise to figure out.

      $UIUsers = Get-ADGroupMember Test_Group  
      $SQLServerName = "Server"  
      $SQLDBName = "Database" 
      
      $outputObject = New-Object System.Collections.Generic.List[object]
      
      foreach ($User in $UIUsers) {
      
          $Username = $User.SamAccountName.ToUpper()  
      
          $userResult = Get-ADuser -Properties Department -identity $Username
      
          $sqlResult = Invoke-Sqlcmd -ServerInstance $SQLServerName -Database $SQLDBName -Query "
              SELECT SYS_USER_CLASSES
              from OPERS_LS
              where SYS_USER_ID in ('$Username')
          "
      
          $outputObject.Add(
              [pscustomobject]@{
              GivenName = $userResult.GivenName;
              Surname   = $userResult.Surname;
              SamAccountName = $userResult.SamAccountName;
              Department     = $userResult.Department;
              SysUserClass   = $sqlResult.SYS_USER_CLASSES -join '","';
              })
      
              $obj = [pscustomobject]@{
                  GivenName = $userResult.GivenName;
                  Surname   = $userResult.Surname;
                  SamAccountName = $userResult.SamAccountName;
                  Department     = $userResult.Department;
              }
      
              $i = 0
              $sqlResult.SYS_USER_CLASSES | 
                  ForEach-Object {
                      $colName = "SysUserClass_$i"
                      $obj | Add-Member -NotePropertyName $colName -NotePropertyValue $_
                  }
      
              $outputObject.Add($obj)
          }  
      
          $outputObject | Export-CSV cmdlet to Export to a CSV
    • #57271
      Profile photo of Charles Downing
      Charles Downing
      Participant

      Heh... oops! That's because I didn't increment my counter! I've edited my last post to include the very small, but vastly important "$i++" line!

You must be logged in to reply to this topic.