ConfigMgr Query for Name, Serial Number, BIOS Version, IP Address and MAC Address

ConfigMgr Feb 22, 2017

These queries were last tested with Configuration Manager version 2107

I recently had to pull together some information on several servers, and I used ConfigMgr to collate the information.  Below is the ConfigMgr Query that I used and its SQL equivalent.   This query will pull out the Computer Name, Serial Number, BIOS Version, IP and MAC Address.

The ConfigMgr query is:

select distinct SMS_R_System.Name, SMS_G_System_PC_BIOS.SerialNumber, SMS_G_System_PC_BIOS.SMBIOSBIOSVersion, SMS_R_System.IPAddresses, SMS_R_System.MACAddresses from  SMS_R_System inner join SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceID = SMS_R_System.ResourceId
The query inside ConfigMgr
The query inside ConfigMgr

Here is the SQL equivalent if you wish to use that instead to pull the information out of your ConfigMgr database:

SELECT DISTINCT SYS.Name0, IP.IP_Addresses0, NET.MACAddress0, BIOS.SMBIOSBIOSVersion0, BIOS.SerialNumber0 FROM dbo.v_RA_System_IPSubnets IPSUB INNER JOIN dbo.v_R_System SYS ON IPSUB.ResourceID = SYS.ResourceID INNER JOIN dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NETW ON SYS.ResourceID = NETW.ResourceID INNER JOIN dbo.v_GS_NETWORK_ADAPTER NET ON SYS.ResourceID = NET.ResourceID INNER JOIN dbo.v_RA_System_IPAddresses IP ON IPSUB.ResourceID = IP.ResourceID INNER JOIN dbo.v_GS_PC_BIOS BIOS on BIOS.ResourceID = SYS.ResourceID

Conclusion

Even if you can't use this query immediately, I would suggest taking a copy and storing it.  You never know when this might come in handy, or it could be the start of a query you need.

Tags

Great! You've successfully subscribed.
Great! Next, complete checkout for full access.
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.