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.
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
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.