correct, I have some fields without ipv6 and therefore no comma. seems to be impossible with charindex function. the temp table solution works, that's not bad at all many thanks for your time!
Von: [email protected] [mailto:[email protected]] Im Auftrag von Chad Simmons Gesendet: Freitag, 31. Juli 2015 16:17 An: [email protected] Betreff: RE: [mssms] SQL guru needed... I'm pretty sure the issue is related to the multiple IPAddresses in the field. In my lab all computers have both an IPv4 and IPv6 addresses, thus each IPAddress0 field has more than 1 value and includes a comma. I played around with some UNION, CASE, and IIF statements to handle this but the SQL is getting challenging to read as it is. After a few minutes I realized it would be almost impossible to read 5 seconds after I wrote it. If a real DBA is on this email speak up... don't be shy. The temp table + derived table solution works. Modifying the Trivial Solution to work with temp table data which only has a single IP in the IPAddress0 field would be... trivial. If you want to keep hacking at the Trivial Solution without a temp table, perhaps we can take this conversation offline then post the working solution back to the email list. 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: Friday, July 31, 2015 8:30 AM To: '[email protected]' <[email protected]<mailto:[email protected]>> Subject: AW: [mssms] SQL guru needed... Chad, I get this message for the 'trivial solution': Msg 537, Level 16, State 3, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function. Thx Reto Von: [email protected]<mailto:[email protected]> [mailto:[email protected]] Im Auftrag von Chad Simmons Gesendet: Freitag, 31. Juli 2015 05:05 An: [email protected]<mailto:[email protected]> Betreff: RE: [mssms] SQL guru needed... 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]> [mailto:[email protected]] On Behalf Of Kehl Reto Sent: Thursday, July 30, 2015 1:28 AM To: '[email protected]' <[email protected]<mailto:[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?
