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!

1. Open the SCCM (ConfigMgr) console
2. Click the Monitoring tab
3. Click Reports
4. Search for mac

Option 2

1. Open the SCCM (ConfigMgr) console
2. Click the Monitoring tab
3. Create new Query wizard
   > Right click Queries
   > Create Query
   
4. 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
   
5. Query Statement
   > Click Show Query Language
   
   > Paste the following query into the Query Statement

   SELECT SMS_R_System.Name, SMS_R_System.MACAddresses FROM SMS_R_System WHERE SMS_R_System.MACAddresses = ##PRM:SMS_R_System.MACAddresses##

   > Click Ok
   

6. Summary
   > Click Close
7. Test your new query
   > Right click new query
   > Click Run
   
8. MAC Address prompt
   > Enter the MAC Address you would like to query
   > Click Ok
   
9. 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