Sorting ConfigMgr data by IP Address

If you’ve ever wanted to report on ConfigMgr client IPAddress you know it can be a challenge.  ConfigMgr stores IP Address information in the v_GS_NETWORK_ADAPTER_CONFIGURAITON SQL view (and other places) but it is actually an array of both IPv4 and IPv6 addresses.  Generally, all we care about is the first IPv4 address.  With that in mind, here is some T-SQL code to return a Computer’s name, ResourceID, and IP Address properly by IP.

T-SQL

[read-more-redirect urltext=”CatapultSystems.com” url=”https://www.catapultsystems.com/blogs/sorting-configmgr-data-by-ip-address”]

--Create an in-memory temp table with all desired data
--using only the leftmost IP address from the IPAddress array
CREATE TABLE #cmIPs(resourceID int not null, IPAddress0 varchar(15) NOT NULL, DNSHostName0 nvarchar(255));
INSERT INTO #cmIPs -- (resourceID, IPAddress0)
select ResourceID, IPAddress0, DNSHostName0 from v_GS_NETWORK_ADAPTER_CONFIGURATION
WHERE v_GS_NETWORK_ADAPTER_CONFIGURATION.IPEnabled0=1 and CHARINDEX(',',IPAddress0)=0
UNION
select ResourceID, Left(IPAddress0,CHARINDEX(',',IPAddress0)-1), DNSHostName0
from v_GS_NETWORK_ADAPTER_CONFIGURATION
WHERE v_GS_NETWORK_ADAPTER_CONFIGURATION.IPEnabled0=1 and CHARINDEX(',',IPAddress0)>0

--Use the Derived Tables solution from http://sqlmag.com/t-sql/sorting-ip-addresses to sort by IPv4 IPAddress
SELECT IPAddress0, DNSHostName0, ResourceID
FROM (SELECT *, CHARINDEX('.', IPAddress0, p2+1) AS p3
      FROM (SELECT *, CHARINDEX('.', IPAddress0, p1+1) AS p2
            FROM (SELECT IPAddress0, CHARINDEX('.', IPAddress0) AS p1, DNSHostName0, ResourceID
                  FROM #cmIPs) AS D1) AS D2) AS D3
ORDER BY
  CAST(SUBSTRING(IPAddress0, 1,      p1 - 1     ) AS tinyint),
  CAST(SUBSTRING(IPAddress0, p1 + 1, p2 - p1 - 1) AS tinyint),
  CAST(SUBSTRING(IPAddress0, p2 + 1, p3 - p2 - 1) AS tinyint),
  CAST(SUBSTRING(IPAddress0, p3 + 1, 3          ) AS tinyint);

Thanks to Itzik Ben-Gan of SQL Server Pro for the pseudo code.

Thanks Kehl Reto for the blog inspiration.

[/read-more-redirect]