Sort data from Microsoft SQL via powershell

Question

I have the following script that allows me to get MAC Addresses from MS SQL Db.

Function Get-MAC ($Server)
{
    $SQLServer = "TVEAPP02" #use Server\Instance for named SQL instances! 
    $SQLDBName = "EuropeanMacs"
    $SqlQuery = "select MAC from dbo.tbl_MAC WHERE HOST = '$Server'"

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd

    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)

    $SqlConnection.Close()

    $DataSet.Tables[0]
}

GET-MAC Computerrname

And i get for examples this result:

GET-MAC 1IT001 2

MAC

--- F4:CE:46:2C:21:FE
F4:CE:46:2C:21:FE

How can i get a 1st or a 2nd MAC from this result? I mean i want to have result for example in $MAC1 and $MAC2...

Sorry for my english and thank you for reading.

vonPryz · Accepted Answer · 2013-05-24 19:31:26Z

A SELECT statement will not return items in any specific order unless an ORDER BY is used in the query too. If you don't order the set, it is possible that running the query two times will return different values.

In order to retrieve nth element, first check the number of rows returned. A DataTable has property Rows that contains the count of, well, rows. Like so,

$DataSet.Tables[0].Rows.Count

After the row count is known (so you don't point to a non-existing row), accessing data is done via row index and column name. Assuming MAC address in row 3 is 46:2C:21:FE:F4:CE, retrieve it. The row index is zero-based, just like table index. Like so,

PS C:\> $DataSet.Tables[0].Rows[2].MAC
46:2C:21:FE:F4:CE

To put two MAC addresses in different variables, just use assignment operator. Like so,

PS C:\> $MAC1 = $DataSet.Tables[0].Rows[0].MAC
PS C:\> $MAC2 = $DataSet.Tables[0].Rows[1].MAC

Collectives™ on Stack Overflow

Sort data from Microsoft SQL via powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related