SCCM 2012: Query to find Clients with the same MAC Address

Summary:

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

SCCM_DuplicateMACAddresss_Reports_03062015

Option 2

  1. Open the SCCM (ConfigMgr) console
  2. Click the Monitoring tab
  3. Create new Query wizard
    > Right click Queries
    > Create Query
    SCCM_QueryUserLastLoggedOn_CreateQuery_29042015
  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
    SCCM_DuplicateMACAddresss_GeneralQuerySettings_03062015
  5. Query Statement
    > Click Show Query Language
    SCCM_QueryUserLastLoggedOn_ShowQueryLanguage_29042015
    > 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
    SCCM_DuplicateMACAddresss_SQLQuery_03062015

  6. Summary
    > Click Close
  7. Test your new query
    > Right click new query
    > Click Run
    SCCM_DuplicateMACAddresss_RunQuery_03062015
  8. MAC Address prompt
    > Enter the MAC Address you would like to query
    > Click Ok
    SCCM_DuplicateMACAddresss_MACPrompt_03062015
  9. You will be presented with the results for a particular MAC address
    SCCM_DuplicateMACAddresss_Results_03062015

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s