June 20, 2013 at 6:43 am
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
MachineIdIPAddress
1143.1.96.29
1192.168.0.12
2143.34.96.0
2172.20.10.0
3155.119.212.212
4192.168.1.55
5172.20.25.55
610.1.55.212
6155.119.232.22
7192.168.2.55
7192.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
MachineIdIPAddress
1143.1.96.29
2143.34.96.0
3155.119.212.212
4192.168.1.55
5172.20.25.55
610.1.55.212 NOK (This should be the 155.119.232.22)
7192.168.2.55 NOK (This should be 192.44.55.212)
Thx for all the help
June 20, 2013 at 6:54 am
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?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 20, 2013 at 6:57 am
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.
June 20, 2013 at 7:12 am
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
June 20, 2013 at 9:29 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply