Untitled

/*
        Author: Trevor Sullivan

        Date: Tuesday, July 21st, 2009

        Purpose: Identify devices whose AMT hostname and OS hostname mismatch
                        in the Configuration Manager database

*/

SELECT
        -- Active Directory site name
        [AD_Site_Name0] AS 'AD SiteName'
        -- AMT hostname (in provisioning record)
        , [amt].[HostName] AS 'AMT HostName'
        -- OS hostname (should match AMT firmware)
        , [sys].[Name0] AS 'OS Hostname'
        -- Retrieve UserID to identify device owner
        , [UserName0] AS 'UserID'
        -- Hardware vendor
        , [cs].[Manufacturer0] AS 'Vendor'
        -- Device model
        , [cs].[Model0] AS 'Model0'

FROM v_AMTMachineInfo [amt]

-- Join v_R_System to retrieve AD Site Name field
JOIN v_R_System [sys] ON [sys].[ResourceID] = [amt].[MachineID]
-- Joinv_GS_Computer_System to allow us to retrieve make/model information
JOIN v_GS_Computer_System [cs] ON [sys].[ResourceID] = [cs].[ResourceID]

WHERE
        -- We only want current resource records from ConfigMgr
        [sys].[Obsolete0] = 0
        -- This condition determines the mismatching hostname in the v_R_System and v_AMTMachineInfo SQL views
        AND [sys].[Name0] <> [amt].[HostName]