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

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

I recently had to pull together some information on several servers and I made used of ConfigMgr in order to collate the information.  Below is the ConfigMgr Query that I used and also it’s SQL equivalent.

Below is the ConfigMgr Query that will pull out the Computer Name, Serial Number, BIOS Version, IP and MAC Address.

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

When you look at your query statement window, it should look like this.

ConfigMgr Query

 

 

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.SMBIOSVersion0, 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 right away I would suggest take a copy of it and storing it.  You never know when this might come in handy, or it could be the start of a query that you need.

 

 

Please follow and like us:
0
Comments are closed.