Query to find duplicate Mac addresses in SCCM

found on consolidating here for my own records

Using SCCM to query the ConfigMgr database to find clients with duplicate MAC addresses.


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.


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.


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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.