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.

bra_racing · Accepted Answer · 2020-07-09 06:37:22Z

12

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

edited Jul 9, 2020 at 6:37

bra_racing

6221 gold badge10 silver badges35 bronze badges

answered Jun 14, 2012 at 1:27

Chad Miller

42.4k4 gold badges33 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CallumStyan Over a year ago

Everything seems to work as I intended, thanks! I've made sure to declare and return $compArray as an actual array, and using out-null to get rid of the .Fill data. Whats the difference between selecting just Property versus selecting ExpandProperty?

Chad Miller Over a year ago

Selecting the property i.e. "...| Select Nebios_Name0" returns an object with single property while -expandproperty returns an array of strings.

CallumStyan Over a year ago

Ahh, that's pretty handy. Thanks.

Aaron Jensen · Accepted Answer · 2012-06-14 04:59:54Z

4

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)

answered Jun 14, 2012 at 4:59

Aaron Jensen

27.2k18 gold badges85 silver badges93 bronze badges

1 Comment

Chad Miller Over a year ago

I'd suggest piping to out-null as I did below for the call to Fill method

Collectives™ on Stack Overflow

Powershell and SQL: data from sql query to an array

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related