SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need some help with retrieving correct Device IP Address


Need some help with retrieving correct Device IP Address

Author
Message
denis.gendera
denis.gendera
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 95
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
Kingston Dhasian
Kingston Dhasian
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5818 Visits: 5279
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/
denis.gendera
denis.gendera
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 95
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.
Lowell
Lowell
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67391 Visits: 40891
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
denis.gendera
denis.gendera
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 95
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search