AMT / ConfigMgr SQL Query: Mismatched Hostnames
Posted by Trevor Sullivan on 2009/07/22
In IT environments where device naming standards may be course, or where users can freely rename their systems at will, you may experience problems managing these clients’ AMT firmwares. Since, in order to maintain proper AMT functionality, the OS and AMT hostnames must match, an IT administrator or engineer would likely be interested in finding out which machines do not meet this criteria.
With that in mind, I’ve written a simple SQL query, that can be run against your Configuration Manager database, to determine what devices have mismatching OS and AMT hostnames. I’ve pasted the text below, but if you want a more nicely formatted version, please see this link at PasteBin.
Author: Trevor Sullivan
Date: Tuesday, July 21st, 2009
Purpose: Identify devices whose AMT hostname and OS hostname mismatch
in the Configuration Manager database
-- 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]
-- Join v_GS_Computer_System to allow us to retrieve make/model information
join v_GS_Computer_System [cs] on [sys].[ResourceID] = [cs].[ResourceID]
-- 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]