Try this. it worked in my ConfigMgr 2012 lab
--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);
Chad Simmons | Microsoft System Center Configuration Manager Consultant |
linkedin.com/in/chadsimmons
From: [email protected] [mailto:[email protected]]
On Behalf Of Kehl Reto
Sent: Wednesday, July 29, 2015 9:52 AM
To: '[email protected]' <[email protected]>
Subject: AW: [mssms] SQL guru needed...
that's the one I'm using ("Trivial Solution") but it is not working when
there is an ipv6 address
the length of the last octet should end at the comma
192.168.2.4, fe80::284c:ddc5:as0b:8dc8:61df
Von: [email protected] <mailto:[email protected]>
[mailto:[email protected]] Im Auftrag von Chad Simmons
Gesendet: Mittwoch, 29. Juli 2015 16:40
An: [email protected] <mailto:[email protected]>
Betreff: RE: [mssms] SQL guru needed...
Checkout http://sqlmag.com/t-sql/sorting-ip-addresses which has several
methods of accomplishing it. Hopefully one will work for you.
Chad Simmons | Microsoft System Center Configuration Manager Consultant |
linkedin.com/in/chadsimmons
From: [email protected] <mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Kehl Reto
Sent: Wednesday, July 29, 2015 9:23 AM
To: [email protected] <mailto:[email protected]>
Subject: [mssms] SQL guru needed...
need sql query for ip addresses sorted ascending but can't get the 4th octet
SELECT
IPAddress0, DNSHostName0
FROM v_GS_NETWORK_ADAPTER_CONFIGURATION
WHERE (v_GS_NETWORK_ADAPTER_CONFIGURATION.IPEnabled0 = 1)
ORDER BY
CAST(SUBSTRING(IPAddress0, 1, CHARINDEX('.', IPAddress0) - 1) AS tinyint),
CAST(SUBSTRING(IPAddress0, CHARINDEX('.', IPAddress0) + 1, CHARINDEX('.',
IPAddress0, CHARINDEX('.', IPAddress0) + 1) - CHARINDEX('.', IPAddress0) -
1) AS tinyint),
CAST(SUBSTRING(IPAddress0, CHARINDEX('.', IPAddress0, CHARINDEX('.',
IPAddress0) + 1) + 1, CHARINDEX('.', IPAddress0,
CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) + 1)
- CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) -
1) AS tinyint)
--CAST(SUBSTRING(IPAddress0, CHARINDEX('.', IPAddress0, CHARINDEX('.',
IPAddress0, CHARINDEX('.', IPAddress0) + 1) + 1) + 1, 3) AS tinyint)
the last CAST(SUBSTRING(.. gives error when activated.
I can't get it, way too complicated for me..
anybody?
