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

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/

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

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 “,”.

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…

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.

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)

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

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!