Powershell and SQL: data from sql query to an array

active-directory, data-binding, powershell, sql

Solution

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)
}

Problem

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.

Original source