Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need some help with retrieving correct Device IP Address Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 6:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 04, 2013 10:31 AM
Points: 25, Visits: 55
All, we got Inventory system that runs in our environment collecting hardware/software information. I like to setup system, that checks the workstation IP Address/IP Subnet and matches that against our Location table and retrieve the location based on IP Information. With people working from home; Virtual Machines, etc.. it's not that straight forward, as devices now have multiple IP Addresses assigned.
Find below DDL, and some scenario's that I need help with
DECLARE @IPInfo TABLE
(
MachineId int NOT NULL,
IPAddress varchar ( 255 ) NOT NULL
);

-- Add some sample data
INSERT INTO @IPInfo
(
MachineId,
IPAddress
)
VALUES
( 1, '143.1.96.29' ),
( 1, '192.168.0.12' ),
( 2, '143.34.96.0' ),
( 2, '172.20.10.0' ),
( 3, '155.119.212.212' ),
( 4, '192.168.1.55' ),
( 5, '172.20.25.55' ),
( 6, '10.1.55.212' ),
( 6, '155.119.232.22'),
( 7, '192.168.2.55'),
( 7, '192.44.55.212')

select * from @IPInfo
MachineId IPAddress
1 143.1.96.29
1 192.168.0.12
2 143.34.96.0
2 172.20.10.0
3 155.119.212.212
4 192.168.1.55
5 172.20.25.55
6 10.1.55.212
6 155.119.232.22
7 192.168.2.55
7 192.44.55.212

I've tried this using GROUP BY with MIN(IPAddress) Option.
this works to some extend, but not in all scenario's.
I try to filter out what is known as RFC1918 addresses but only if workstation has multiple IP Addresses, we do have in our environment Desktops and they in most cases only have 1 IP Address.
The RFC1918 range is 10.0.0.0/8 172.16.0.0/12 192.168.0.0/24

What I try to do is following
WHen Machine has multiple IPAddresses, and need to filter out the RFC1918 addresses somehow, the option with MIN(IPAddress) works for 80% but still need also to
have that 20% resolved.

SELECT MachineId, Min(IPAddress) As IPAddress FROM @IPInfo
GROUP BY MachineId
ORDER BY MachineId

MachineId IPAddress
1 143.1.96.29
2 143.34.96.0
3 155.119.212.212
4 192.168.1.55
5 172.20.25.55
6 10.1.55.212 NOK (This should be the 155.119.232.22)
7 192.168.2.55 NOK (This should be 192.44.55.212)

Thx for all the help
Post #1465652
Posted Thursday, June 20, 2013 6:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 5:09 AM
Points: 2,567, Visits: 4,654
Do you want to exclude the IP addresses only when a machine has more than 1 assigned?
What if there is a machine which has only one IP address assigned and that falls in your exclusion range?



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1465665
Posted Thursday, June 20, 2013 6:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 04, 2013 10:31 AM
Points: 25, Visits: 55
Correct I only want to exclude these IP Addresses if machine has > 1 IP Address.
if I have machine with 1 IP Address and it's inside the exclusion range, then I would like to report that IP Address.

Post #1465668
Posted Thursday, June 20, 2013 7:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:27 PM
Points: 12,744, Visits: 31,080
i have always converted IP's to big ints so that i could do cmparison and ranges;
something like this in your table setup can help:
DECLARE @IPInfo TABLE
(
MachineId int NOT NULL,
IPAddress varchar ( 255 ) NOT NULL,
IPAsBigInteger AS convert(bigint,parsename(IPAddress,4)) * 256 * 256 * 256 +
convert(bigint,parsename(IPAddress,3)) * 256 * 256 +
convert(bigint,parsename(IPAddress,2)) * 256 +
convert(bigint,parsename(IPAddress,1))
);

then you can test if the value is between 10.0.0.1 (bigint =167772161 and 10.255.255.255(bigint = 184549375) ?(is that right? outta practice with networking and subnets)


declare @ipAddress varchar(20)
SET @ipAddress='10.255.255.255'
SELECT
convert(bigint,parsename(@ipAddress,4)) * 256 * 256 * 256 +
convert(bigint,parsename(@ipAddress,3)) * 256 * 256 +
convert(bigint,parsename(@ipAddress,2)) * 256 +
convert(bigint,parsename(@ipAddress,1))



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1465677
Posted Thursday, June 20, 2013 9:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 04, 2013 10:31 AM
Points: 25, Visits: 55
I've managed to create 2 queries that retrieve the data I need

--Query 1: Return machines with 1 IPAddress
select MachineId, MAX(IPAddress) as IPAddress from @IPInfo
group by MachineID
having COUNT(MachineID) = 1

--Query 2: Return machines that have #IPAddresses, and only show valid ip address
select MachineId, ipaddress from @IPInfo
where MachineId in (SELECT MachineId FROM @IPInfo
GROUP BY MachineId
having COUNT(MachineID) > 1) and IPAddress not like '192.168.%' and IPAddress not like '172.20.%' and IPAddress not like '10.%'

(I still need to fine tune the IPAddress filter a bit more, but for the sample data it's working)

when I try to combine this into 1 big query, it does not return any rows !!!
select machineid, ipaddress from @IPInfo
inner join (select q1t.MachineId, MAX(q1t.IPAddress) as IPAddress from @IPInfo as q1t
group by q1t.MachineID
having COUNT(q1t.MachineID) = 1) As Q1 on Q1.MachineId = machineID
inner join (select MachineId, ipaddress from @IPInfo
where MachineId in (SELECT MachineId FROM @IPInfo
GROUP BY MachineId
having COUNT(MachineID) > 1) and IPAddress not like '192.168.%' and IPAddress not like '172.20.%' and IPAddress not like '10.%') As Q2 on Q2.MachineId = machineID and Q2.IPAddress = IPAddress

Other option I'm looking at is creating 2 Temp tables and then use Union query, need to figure that out still
Post #1465783
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse