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.
Once done, search for the Mac Address of the computer name by entering it under Search window.
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.
This will open Create Query Wizard, provide the name as “Find Mac Address of Device”and click on Edit Query Statement.
This will open the General Property of the Query Statement, click on Show Query Language.
Under Query Language, provide the following WQL Query Statement:
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
where
SMS_R_System. Macaddresses = ##PRM:SMS_R_System. Macaddresses ##
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.
Input Query Value Window will appear and is prompting to enter Mac Address, provide the value and hit OK.
And here you can see the results of one of the most important information related to device.
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
- SMBIOSGUID
- SMSAssignedSites
- SMSInstalledSites
- SMSResidentSites
- SMSUniqueIdentifier
- SMSUUIDChangeDate
- SystemContainerName
- SystemGroupName
- SystemOUName
- SystemRoles
- UserAccountControl
- VirtualMachineHostName
- VirtualMachineType
