SCCM – IP Addresses

A dirty little secret of Microsoft, and why you don’t see IP address columns in the SCCM console, is that MS stores all the IP addresses of a machine into a single row, into an array. So, when viewed, you’ll see 1-6 (or more) IP addresses all crammed together. Not great.

 

Here are some common methods for dealing with IP addresses. I normally just run the queries directly on the SQL DB, but you can also use SCCM Reporting and SCCM Queries under Monitoring.

 

SCCM REPORT TO LIST IP ADDRESSES – LITE VERSION

select distinct
A.Name0,c.IPAddress0,
D.IP_Subnets0
from v_R_System A
inner join v_FullCollectionMembership B on A.ResourceID=B.ResourceID
Inner join v_GS_NETWORK_ADAPTER_CONFIGUR C ON A.ResourceID=C.ResourceID
Inner Join v_RA_System_IPSubnets D ON A.ResourceID=D.ResourceID
where CollectionID=@COLLID and C.IPEnabled0='1'
group by A.Name0,c.IPAddress0 ,D.IP_Subnets0
order by A.Name0,c.IPAddress0 ,D.IP_Subnets0

 

SCCM REPORT TO LIST IP ADDRESSES – FULL VERSION

SELECT distinct
CS.name0 as 'Server Name',
OS.Caption0 as 'OS',
CU.Manufacturer0 as 'Manufacturer',
CU.Model0 as 'Model',
RAM.TotalPhysicalMemory0/1024 as [RAM (MB)],
processor.Name0 as 'Processor',
BIOS.ReleaseDate0 as 'BIOS Manufacture Date',
OS.InstallDate0 as 'OS Install Date',
IP.IP_Addresses0 AS 'IP Address'

from
v_R_System CS
FULL join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
FULL join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID
FULL join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
FULL JOIN v_GS_PROCESSOR Processor on Processor.ResourceID=CS.ResourceID
FULL join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
FULL join v_GS_COMPUTER_SYSTEM CU on CU.ResourceID = CS.ResourceID
join v_RA_System_IPAddresses IP on IP.ResourceID = CS.ResourceID

WHERE CS.Operating_System_Name_and0 LIKE '%nt%server%'
AND IP.IP_Addresses0 NOT LIKE '192.168%'
AND IP.IP_Addresses0 NOT LIKE '172.10%'
AND IP.IP_Addresses0 NOT LIKE '%:%'
AND IP.dhcpenabled0 = 0

group by
CS.Name0,
OS.Caption0,
CU.Manufacturer0,
CU.Model0,
RAM.TotalPhysicalMemory0,
BIOS.ReleaseDate0,
OS.InstallDate0,
Processor.Name0,
BIOS.ReleaseDate0,
IP.IP_Addresses0
Order by CS.Name0

 

IP ADDRESSES SINGLE COLUMN

Select Distinct

SD.Name0,

IP.IpAddress0

From v_Gs_System SD

Join v_Gs_Network_Adapter_Configur IP

On SD.ResourceId = IP.ResourceId

Where IP.DefaultIPGateway0 Is Not NULL

And IP.IPAddress0 Is Not NULL

And IP.IPAddress0 <> '0.0.0.0'

Order By SD.Name0

 

DISTINCT ADDRESSES, multiple rows

SELECT v_RA_System_ResourceNames.Resource_Names0 AS [Resource name],
v_RA_System_IPAddresses.IP_Addresses0 AS [IP Address]
FROM v_RA_System_MACAddresses INNER JOIN
v_RA_System_ResourceNames ON v_RA_System_MACAddresses.ResourceID = v_RA_System_ResourceNames.ResourceID INNER JOIN
v_RA_System_IPAddresses ON v_RA_System_MACAddresses.ResourceID = v_RA_System_IPAddresses.ResourceID

SINGLE IP ADDRESS
SELECT DNSHostName0 AS [NetBIOS Name],
CASE WHEN IPAddress0 like '%,%' THEN left(IPAddress0,CHARINDEX(',',IPAddress0)-1)
ELSE IPAddress0 END AS [IP Address]
FROM v_GS_NETWORK_ADAPTER_CONFIGUR
WHERE ([dbo].[v_GS_NETWORK_ADAPTER_CONFIGUR].IPAddress0 not like 'fe%')
and ([dbo].[v_GS_NETWORK_ADAPTER_CONFIGUR].IPAddress0 IS NOT NULL)
and ([dbo].[v_GS_NETWORK_ADAPTER_CONFIGUR].IPAddress0 not like '169.254.%')
and ([dbo].[v_GS_NETWORK_ADAPTER_CONFIGUR].DefaultIPGateway0 IS NOT NULL)
and ([dbo].[v_GS_NETWORK_ADAPTER_CONFIGUR].IPEnabled0 = '1')

 

SCCM QUERY RETURN IPs TO ONE COLUMN

select Name, IPAddresses,
LastLogonUserDomain,
LastLogonUserName,
ResourceType,
NetbiosName,
ClientType
from sms_r_system
where Client = 1 and SMS_R_System.Name = SMS_R_System.Name