In this post I will show you how to create SCCM Query to find Mac Address of device.

How to find Mac address of the device

Usually it’s a simple process to find the Mac address of the device which can be done by opening Configuration Manager console and navigating to \Assets and Compliance\Overview\Devices. Just right click the column displaying properties and select MAC Address.

enable MAC Address Devices sccm

Once done, search for the Mac Address of the computer name by entering it under Search window.

Devices Search Results

Though this looks like a good way to find the Mac address. However, we have another approach as well by creating the query inside the SCCM console and get the results. I like this approach and uses this quite often.

Create Query to find Mac Address in SCCM console

Navigate to \Monitoring\Overview\Queries, this is the place where you will find list of all inbuilt queries created as part of Configuration Manager installation. This is a powerful feature to create your own query.

The query could be very simple to get the report displayed or we can also create a prompt to enter the value and get the report based upon the value provided.

Under \Monitoring\Overview\Queries, right click the Queries and select Create Query.

SCCM Create Query

This will open Create Query Wizard, provide the name as “Find Mac Address of Device”and click on Edit Query Statement.

Find Mac Address of Device

This will open the General Property of the Query Statement, click on Show Query Language.

Under Query Language, provide the following WQL Query Statement:

Show Query Language
select SMS_R_System.Name, SMS_R_System.Macaddresses, SMS_R_System.Build, SMS_R_System.BuildExt, SMS_R_System.OperatingSystemNameandVersion
from SMS_R_System
SMS_R_System. Macaddresses = ##PRM:SMS_R_System. Macaddresses ##
Query Language for Mac Address

Click OK

Click Next and complete the Create Query Wizard.

You can see the “where” query is using ##PRM: and ending with ##. This is the logic used to prompt for Macaddress which means it will ask you to end the value to get the results.

Query is not created. Right click the Query “Find Mac Address of Device” and click on Run.

Run SCCM Query

Input Query Value Window will appear and is prompting to enter Mac Address, provide the value and hit OK.

Search for mac Address SCCM

And here you can see the results of one of the most important information related to device.

CreateSCCMQueryMac 09

This is a pretty simple query which is pulling few properties. You have option to get more details to pick from sms_r_system table or you can even  join it with other tables to get more complex results.

Few of the other values which can be used for sms_r_system table if you wish to be included:

  • Active
  • ADSiteName
  • AgentName
  • AgentSite
  • AgentTime
  • AlwaysInternet
  • Build
  • BuildExt
  • Client
  • ClientEdition
  • ClientType
  • ClientVersion
  • CloudPCInfo
  • CPUType
  • CreationDate
  • Decommissioned
  • DeviceOwner
  • DistinguishedName
  • DNSForestGuid
  • DotNetRelease
  • ESUValue
  • FullDomainName
  • HardwareID
  • InternetEnabled
  • IPAddresses
  • IPSubnets
  • IPv6Addresses
  • IPv6Prefixes
  • IsAOACCapable
  • IsAssignedToUser
  • IsMachineChangesPersisted
  • IsPortableOperatingSystem
  • IsVirtualMachine
  • IsWriteFilterCapable
  • LastLogonTimestamp
  • MACAddresses
  • ManagementAuthority
  • Name
  • NetbiosName
  • ObjectGUID
  • Obsolete
  • OperatingSystemNameandVersion
  • OSBranch
  • PreviousSMSUUID
  • PrimaryGroupID
  • ResourceDomainORWorkgroup
  • ResourceId
  • ResourceNames
  • ResourceType
  • SecurityGroupName
  • SID
  • SMSAssignedSites
  • SMSInstalledSites
  • SMSResidentSites
  • SMSUniqueIdentifier
  • SMSUUIDChangeDate
  • SystemContainerName
  • SystemGroupName
  • SystemOUName
  • SystemRoles
  • UserAccountControl
  • VirtualMachineHostName
  • VirtualMachineType