Share via

New view - Real IP address based on v_GS_NETWORK_ADAPTER_CONFIGURATION and calculated Subnet field (not a subnet Mask!!!)

  • Article

 

Another custom query and a function for me. Basically, IP address is a mutivalue property, and sometimes discovery finds supernets if it can't figure out the subnet mask. That means, if you break your 10.0.0.0 subnet, it still assumes class A network, so under

v_RA_System_IPAddresses and  v_RA_System_IPSubnets you'll see 10.0.0.0 and 255.0.0.0, when actually it's a class C subnet. So, the only reliable source of this information is inventory, v_gs_network_adapter_configuration. There are two problems with this inventory class that reflects Win32_NetworkAdapterConfiguration class:

  1. It got all possible virtual adapters and it's very "noisy"
  2. It does not contain the subnet, you'll have to do the math yourself, under "Sunbet" field it has the subnet mask

 

So, here is the solution.

First is the view, I called it v_gs_RealIPv4 , it tries to do it's best trying to find the only one and real IP address per machine. It gives priority to one that comes from DHCP, then it takes static ones with DNS Suffix, then it takes any valid IP address with gateway, then any address at all. If there are multiple IP addresses, it gives priority to the one with max adapter ID (kind of randomization between equal adapters).

 The second one is the scalar function CustomGetSubnet that takes IP address and Subnet Mask and returns IP Subnet.

I strongly recommend NOT to create those objects in production CM Database! Create a side database on the same SQL instance, and call it CM_Supplemental, for example. Then you can refrence it in your reports or queries like this:

 

 

 SELECT Name0, v_gs_RealIPv4.* FROM 
 v_r_system inner join CM_Supplemental..v_gs_RealIPv4 
 ON v_r_system.ResourceID = v_gs_RealIPv4.ResourceID

 

 

 

 

Here is the code. Change P01 to your side code and execute this T-SQL code.

 CREATE VIEW [dbo].[v_gs_RealIPv4]
 AS
 
 SELECT 
 NAC.ResourceID,
 CASE
 WHEN 
 PATINDEX('%,%',NAC.IPAddress0) > 0 THEN SUBSTRING(NAC.IPAddress0, 1, PATINDEX('%,%', NAC.IPAddress0)-1)
 ELSE NAC.IPAddress0 
 END AS IPAddress,
 CASE
 WHEN 
 PATINDEX('%,%',NAC.IPSubnet0) > 0 THEN SUBSTRING(NAC.IPSubnet0, 1, PATINDEX('%,%', NAC.IPSubnet0)-1)
 ELSE NAC.IPSubnet0 
 END AS SubnetMask,
 dbo.CustomGetSubnet(NAC.IPAddress0, NAC.IPSubnet0) AS Subnet,
 NAC.ServiceName0
 from CM_P01..v_GS_NETWORK_ADAPTER_CONFIGURATION NAC INNER JOIN
 (
 SELECT ResourceID, MAX(Index0) as MxIndex FROM 
 (
 -- First - list those with default gateway and and DHCPServer
 select * from CM_P01..v_GS_NETWORK_ADAPTER_CONFIGURATION 
 where IPEnabled0 = 1
 and IPAddress0 is not NULL
 and DefaultIPGateway0 is not Null
 and DHCPServer0 is not NULL
 and DNSDomain0 is not NULL
 -- Second - thos that don't have default gateway and those are only adapters on the system, i.e. no other adapter with a gateway
 UNION
 SELECT * from CM_P01..v_GS_NETWORK_ADAPTER_CONFIGURATION A where
 IPEnabled0 = 1
 and IPAddress0 is not NULL
 and not exists (
 select * from CM_P01..v_GS_NETWORK_ADAPTER_CONFIGURATION B where
 A.ResourceID=B.ResourceID
 and IPAddress0 is not NULL
 and DefaultIPGateway0 is not Null
 )
 -- Static IPs with default gateway
 UNION
 select * from CM_P01..v_GS_NETWORK_ADAPTER_CONFIGURATION where
 IPEnabled0 = 1 AND
 IPAddress0 is not NULL
 and DefaultIPGateway0 is not Null
 AND ResourceID in (
 (
 select ResourceID from CM_P01..v_R_System
 where 
 ResourceID not in
 (
 select ResourceID from CM_P01..v_GS_NETWORK_ADAPTER_CONFIGURATION 
 where IPEnabled0 = 1
 and IPAddress0 is not NULL
 and DefaultIPGateway0 is not Null
 and DHCPServer0 is not NULL
 and DNSDomain0 is not NULL
 )
 ))
 ) A
 group By ResourceID
 ) D ON NAC.ResourceID = D.ResourceID and NAC.Index0 = D.MxIndex

 And here is the function that calculates the subnet:

 

 CREATE FUNCTION [dbo].[CustomGetSubnet](@IPAddress varchar(255), @IPSubnet varchar(255))
 RETURNS varchar(255)
 AS
 BEGIN
 
 DECLARE @Return varchar(255)
 
 --Split out IP v6
 IF PATINDEX('%,%', @IpAddress) > 0
 SELECT @IPAddress = SUBSTRING(@IPAddress, 1, PATINDEX('%,%', @IpAddress)-1)
 
 IF PATINDEX('%,%', @IPSubnet) > 0
 SELECT @IPSubnet = SUBSTRING(@IPSubnet, 1, PATINDEX('%,%', @IPSubnet)-1)
 
 IF PATINDEX('%.%', @IpAddress) > 0 AND PATINDEX('%.%', @IPSubnet) > 0
 BEGIN
 -- First Octet
 SELECT @Return = CAST(SUBSTRING(@IPAddress,1,PATINDEX('%.%',@IPAddress)-1) as Integer) & CAST(SUBSTRING(@IPSubnet,1,PATINDEX('%.%',@IPSubnet)-1) as Integer)
 SELECT @IPAddress = SUBSTRING(@IPAddress, PATINDEX('%.%', @IPAddress)+1, 1024)
 SELECT @IPSubnet = SUBSTRING(@IPSubnet, PATINDEX('%.%', @IPSubnet)+1, 1024)
 ---Second Octet
 
 SELECT @Return = @Return + '.' + CAST(CAST(SUBSTRING(@IPAddress,1,PATINDEX('%.%',@IPAddress)-1) as Integer) & CAST(SUBSTRING(@IPSubnet,1,PATINDEX('%.%',@IPSubnet)-1) as Integer) as varchar(max))
 SELECT @IPAddress = SUBSTRING(@IPAddress, PATINDEX('%.%', @IPAddress)+1, 1024)
 SELECT @IPSubnet = SUBSTRING(@IPSubnet, PATINDEX('%.%', @IPSubnet)+1, 1024)
 --Third Octet
 
 SELECT @Return = @Return + '.' + CAST(CAST(SUBSTRING(@IPAddress,1,PATINDEX('%.%',@IPAddress)-1) as Integer) & CAST(SUBSTRING(@IPSubnet,1,PATINDEX('%.%',@IPSubnet)-1) as Integer) as varchar(max))
 SELECT @IPAddress = SUBSTRING(@IPAddress, PATINDEX('%.%', @IPAddress)+1, 1024)
 SELECT @IPSubnet = SUBSTRING(@IPSubnet, PATINDEX('%.%', @IPSubnet)+1, 1024)
 --Fourth octet
 
 SELECT @Return = @Return + '.' + CAST(CAST(@IPAddress as Integer) & CAST(@IPSubnet as Integer) as varchar(max))
 SELECT @IPAddress = SUBSTRING(@IPAddress, PATINDEX('%.%', @IPAddress)+1, 1024)
 SELECT @IPSubnet = SUBSTRING(@IPSubnet, PATINDEX('%.%', @IPSubnet)+1, 1024)
 
 END
 
 Return @Return
 
 END

Comments

  • Anonymous
    March 25, 2013
    Great stuff, thanks for sharing