?
Solved

IP address within range query

Posted on 2005-04-19
11
Medium Priority
?
4,446 Views
Last Modified: 2008-03-03
I want to use joins or where clauses to see whether an IP address matches within a range.

This is for mapping locations to ip addresses

For example

Table: LocIP
LocationName      IPRange
Wales                 192.168.1.%
England               192.168.2.1-64
Scotland              192.168.2.65-96
Ireland                192.168.2.96-254

Table: PC
ComputerName          IPAddress        LocationName
COMP1                     192.168.2.70    NULL
COMP2                     192.168.2.99    NULL
COMP3                     192.168.1.5    NULL

The % rule is easy enough.

update pc
set LocationName = LocIP.LocationName
from pc inner join LocIP on pc.IPAddress LIKE LocIP.Range


Any ideas on how to match the more complex rules using sql?


thanks
0
Comment
Question by:plq
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13817112
If your IP Range is composed of 2 fields, namely StartRange and EndRange, this will be easier.  And your IPRange is just the prefix (for example, 192.168.1).  If this is the case, you can use this query:

UPDATE PC
SET LocationName = LocIP.LocationName
FROM PC INNER JOIN LocIP
ON LEFT(PC.IPAddress, LEN(LocIP.IPPrefix)) = LocIP.IPPrefix AND
     RIGHT(PC.IPAddress, CHARINDEX('.', REVERSE(PC.IPAddress))) BETWEEN LocIP.StartRange AND LocIP.EndRange
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 13817134
Are the "variables" only in the last node?  

If so, I suggest using from and to values for that node rather than a single range:

LocationName      IPBase   From  To
Wales                 192.168.1   0      255
England               192.168.2   1      64
Scotland              192.168.2   65    96
Ireland                192.168.2   96    254


You can use computed columns to get the IPFrom and IPTo:

on pc.IPAddress LIKE BETWEEN IPFrom AND IPTo
0
 
LVL 5

Expert Comment

by:obahat
ID: 13817150
For better solutions:
Please see an article provided in this month's SQL server magazine, regarding handling IP address.
http://www.windowsitpro.com/SQLServer/Article/ArticleID/44942/SQLServer_44942.html

You can save/sort/join the IP addresses as 4 tinyints, 1 bigint, and other cool methods. Please refer to the paper for detailed information.

Personall recommendation - don't store your IP addresses as strings. Strings are only needed for the presentation of addresses. For all calculation matter, store the IP as bigint or a combo of tinyints.

Hope this helps.

Omri.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13817157

ALTER TABLE LocIP
ADD IPFrom AS IPBase + '.' + From,
    IPTo AS IPBase + '.' + To
0
 
LVL 8

Author Comment

by:plq
ID: 13817358
I could have the ip range as two fields. Thats a new table so I can control the db design. I can live with part III of the IP address being a single value, so users would have to enter a record for each 255 range - thats ok

The IP address on the PC record must be stored as a single string, that cannot be changed

The problem with a "between" is that the IPs are text, so if you;ve got 192.168.1.1-29 thats going to include 192.168.1.100 isn't it

Perhaps with scotts model:

ON pc.IPAddress LIKE IPBase + '%' AND SubString(replace(pc.IPAddress, IPBase, ''), 2, 255) BETWEEN IPFrom and IPTo  

??
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1600 total points
ID: 13817558
I suggest you use the PARSENAME() T-SQL function to extract the individual parts out. That way you are not killing yourself using CHARINDEX and other cumbersome string functions.  For example,

Declare @IPAddress char(15)
Set @IPAddress = '192.168.2.70'
Select PARSENAME(@IPAddress, 4), PARSENAME(@IPAddress, 3), PARSENAME(@IPAddress, 2), PARSENAME(@IPAddress, 1)

192      168      2      70  

You can then use CAST to tinyint to get the right range.
0
 
LVL 8

Author Comment

by:plq
ID: 13818249
Cool - didn't know about parsename. Researching...
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13818487
>> The problem with a "between" is that the IPs are text <<

D'OH, quite right, I should have thought of that
0
 
LVL 8

Author Comment

by:plq
ID: 13818605
Well you're not half as daft as I am, otherwise I'd be answering not asking. :)))

Just coming up with a solution now using these ideas,... back soon

thanks

0
 
LVL 8

Author Comment

by:plq
ID: 13818663
Yer tiz...

select a.PCID, a.IP , b.TargetValue1
from pc as a
inner join maptable as b on b.MappingType = 'iptolocation'
and parsename(a.IP, 4) = parsename(b.SourceValue1, 4)
and parsename(a.IP, 3) = parsename(b.SourceValue1, 3)
and parsename(a.IP, 2) = parsename(b.SourceValue1, 2)
and convert(int,parsename(a.IP, 1)) between convert(int, parsename(b.SourceValue1, 1)) AND convert(int, parsename(b.SourceValue2, 1))
where specificationid = 258

This just works on a 255 subnet, but is easily extended to the other parts of the address

Got fooled by parsename working backwards, element 1 is the rightmost, but this seems to work ok

thanks for helping. Points coming up
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13819061
You don't need to convert to int, CASTing to tinyint is enough to get the range.

Thanks for the points.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question