ConfigMgr: SQL Query für MAC Addresse

SystemCenter Logo

Ich hab hier mal eine kurze, ausformulierte Query gebastelt, die das Auffinden eines Rechners mittels MAC Adresse erleichtern kann.

declare @MAC nvarchar(17);
set @MAC = '98:E7:F4:F7:12:0C'

SELECT     
    v_RA_System_ResourceNames.Resource_Names0 AS [Resource name], 
    v_RA_System_MACAddresses.MAC_Addresses0 AS [MAC Address],
    v_RA_System_IPAddresses.IP_Addresses0 AS [IP Address]
FROM         
    v_RA_System_MACAddresses 
INNER JOIN v_RA_System_ResourceNames ON v_RA_System_MACAddresses.ResourceID = v_RA_System_ResourceNames.ResourceID 
INNER JOIN v_RA_System_IPAddresses ON v_RA_System_MACAddresses.ResourceID = v_RA_System_IPAddresses.ResourceID
WHERE v_RA_System_MACAddresses.MAC_Addresses0 = @MAC

SQL Abfrage per Powershell

Sehr nützliche Funktion um Queries auf einer SQL Datenbank laufen zu lassen:

function Invoke-SQL 
{
    param(
    [string] $dataSource = 'Server-Name',
    [string] $database = "Database-Name",
    [string] $sqlCommand = $(throw "Please specify a query.")
        )
                                          
    $connectionString = "Data Source=$dataSource; " + 
                    "Integrated Security=SSPI; " + 
                    "Initial Catalog=$database"
                                                                      
    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()
                                                                                  
    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null
                                                                                              
    $connection.Close()
    $dataSet.Tables
                                                                                                      
}