Kehl
Try this for the select statement:
SELECT CAST(SUBSTRING(IPAddress0, p3 + 1, 3) AS tinyint) as Octet4,
IPAddress0, DNSHostName0, ResourceID
If you like the "Trivial Solution" method better, this should take care of
it.
SELECT Left(IPAddress0,CHARINDEX(',',IPAddress0)-1) as FirstIP
, CAST(SUBSTRING(Left(IPAddress0,CHARINDEX(',',IPAddress0)-1),
CHARINDEX('.', Left(IPAddress0,CHARINDEX(',',IPAddress0)-1), CHARINDEX('.',
Left(IPAddress0,CHARINDEX(',',IPAddress0)-1), CHARINDEX('.',
Left(IPAddress0,CHARINDEX(',',IPAddress0)-1)) + 1) + 1) + 1, 3) AS tinyint)
as Octet4
, IPAddress0, DNSHostName0, ResourceID 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(Left(IPAddress0,CHARINDEX(',',IPAddress0)-1), CHARINDEX('.',
Left(IPAddress0,CHARINDEX(',',IPAddress0)-1), CHARINDEX('.',
Left(IPAddress0,CHARINDEX(',',IPAddress0)-1), CHARINDEX('.',
Left(IPAddress0,CHARINDEX(',',IPAddress0)-1)) + 1) + 1) + 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: Thursday, July 30, 2015 1:28 AM
To: '[email protected]' <[email protected]>
Subject: AW: [mssms] SQL guru needed...
Hi Chad, yes this works. many thanks for your help.
although the other query would be easier for me to adjust/extend
to make it work I "only" need a way to get the part between the third dot
and the comma:
192.168.2.124, fe80::284c:ddc5:as0b:8dc8:61df
maybe someone has a solution
Von: [email protected] <mailto:[email protected]>
[mailto:[email protected]] Im Auftrag von Chad Simmons
Gesendet: Mittwoch, 29. Juli 2015 20:23
An: [email protected] <mailto:[email protected]>
Betreff: RE: [mssms] SQL guru needed...
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]>
[mailto:[email protected]] On Behalf Of Kehl Reto
Sent: Wednesday, July 29, 2015 9:52 AM
To: '[email protected]' <[email protected]
<mailto:[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?
