found on Configmonkey.co.uk consolidating here for my own records
Using SCCM to query the ConfigMgr database to find clients with duplicate MAC addresses.
Issue:
We discovered an issue on a client site where multiple distinct clients could have the same MAC address. In this case the end-point would not behave as expected as the deployments the client would receive may not be the ones you expected.
Resolution:
There are many ways to skin this particular cat but this seemed like a viable scenario to demo a how to query the ConfigMgr database to scan for instances where a client had a particular MAC Address.
Implementation:
Update 04/06/2015:
Having a browse around turns out this functionality already exists!
- Open the SCCM (ConfigMgr) console
- Click the Monitoring tab
- Click Reports
- Search for mac
Option 2
- Open the SCCM (ConfigMgr) console
- Click the Monitoring tab
- Create new Query wizard
> Right click Queries
> Create Query
- General Query Settings
> Name: All Systems – Find Clients with a given MAC Address
> Comments: Brief description of what the query is for
> Click Edit Query Statement
- Query Statement
> Click Show Query Language
> Paste the following query into the Query StatementSELECT SMS_R_System.Name, SMS_R_System.MACAddresses FROM SMS_R_System WHERE SMS_R_System.MACAddresses = ##PRM:SMS_R_System.MACAddresses##
- Summary
> Click Close - Test your new query
> Right click new query
> Click Run
- MAC Address prompt
> Enter the MAC Address you would like to query
> Click Ok
- You will be presented with the results for a particular MAC address
Query Statement Explained
SELECT SMS_R_System.Name, SMS_R_System.MACAddresses
The SELECT statement is used to decide what information you would like retrieved by the Query in this case the following:
SMS_R_System.Name | Name of client |
SMS_R_System.MACAddresses | MAC Address of Client |
You can use the query builder to add/remove fields to your preference.
FROM SMS_R_System
FROM statement indicates which table the information is stored in, in this case SMS_R_SYSTEM
WHERE SMS_R_System.MACAddresses = ##PRM:SMS_R_System.MACAddresses##
The WHERE statement is the condition by which results are filtered.
##PRM: ## will prompt the user to enter information, the message prompt and data type will match that of the field you have targeted.
In this case System.Resource.MACAddresses and data type text