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

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.