Powershell and SQL: data from sql query to an array

Question

The issue is with the SQL query that is used to find computers that the selected user has logged into. I execute the query, use an adapter to fill the results into a table, and then read the table objects into an array. I don't fully understand the syntax and functionality of an array in Powershell so I may be doing this part wrong. Inside the function where the table is read into the array, the contents of the array seem fine (just the computer name) but when I pass that array out of the function and assign it to a variable, the array has the following data: {#, compName}. It appears to be the number of computers found, then the names, if one computer is found the array is {1, ComputerName}. However if multiple computers are found, 2 or more, the array is {2, ComputerNameComputerName}.

Here is the function with the SQL query and the function where the user selects the computer.

Function GetComputerList {
    param ($u)
    #use the display name of the user to get their login name
    $queryUser = Get-ADUser -f{DisplayName -eq $u} #-Properties sAMAccountname | Select sAMAccountname
    $queryName = "'"
    $queryName += $queryUser.SamAccountName
    $queryName += "'"
    $query = "SELECT SYS.Netbios_Name0 FROM v_R_System SYS WHERE User_Name0 = $queryName ORDER BY SYS.User_Name0, SYS.Netbios_Name0"

    $connection = new-object system.data.sqlclient.sqlconnection( "Data Source=SERVER;Initial Catalog=DATABASE;Integrated Security=SSPI;")
 
    $adapter = new-object system.data.sqlclient.sqldataadapter ($query, $connection)

    $table = new-object system.data.datatable
    
    $adapter.Fill($table)

    $i = 1
    foreach($object in $table) {
        <#Write-Host "$i. $($object.Netbios_Name0)"
        $i++#>
        $compArray += $object.Netbios_Name0
    }
    foreach($object in $compArray) {
        Write-Host "$i. $($object)"
    }
    
    return @($compArray)
}


Function SelectComputer {
    param ($a)
    $computerNum = Read-Host "Please select a computer. (by number)"
    $computer = ($a[$computerNum])
    return $computer
}

And they are called like this:

$computerArray = GetComputerList -u $selectedUser
$selectedComputer = SelectComputer -a $computerArray

I'm totally lost, any input is appreciated.

Answer 1

You could simplify your GetComputerList function which in my testing produce the desired results:

Function GetComputerList {
    param ($u)
    #use the display name of the user to get their login name
    $queryUser = Get-ADUser -f{DisplayName -eq $u} #-Properties sAMAccountname | Select sAMAccountname
   
    $query = @"
        SELECT SYS.Netbios_Name0 
        FROM v_R_System SYS 
        WHERE User_Name0 = '$($queryUser.SamAccountName)'
        ORDER BY SYS.User_Name0, SYS.Netbios_Name0
    "@
    $connection = new-object system.data.sqlclient.sqlconnection( "Data Source=SERVER;Initial Catalog=DATABASE;Integrated Security=SSPI;") 
    $adapter = new-object system.data.sqlclient.sqldataadapter ($query, $connection)
    $table = new-object system.data.datatable
    $adapter.Fill($table) | out-null
    $compArray = @($table | select -ExpandProperty Netbios_Name0)

    return @($compArray)
}

Answer 2

First, you need to specify that $compArray is, in fact, an array. You never declare it, so PowerShell treats it like a string, thus you get the computer names appended to each other. Somewhere in GetComputerList declare $compArray to be an array:

$compArray = @()

Second, the first number in the array is actually the return value from a .NET function that is getting added to your pipeline. The most likely culprit is $adapter.Fill($table). Assign its return value to a variable:

$numRows = $adapter.Fill($table)