Filter results by IP subnet

[zaradell]

Good afternoon to you all

I need your help

I´m using Microsoft Access with a connector to a MYSQL database to retrieve some networking results.

One of the result columns is the IP address of the machine that performed the networking tests.

The problem is that i need to filter the results by IP that are in certain ranges.

For instance, imagine that i only want to view results from the ranges 10.231.3.0/24 and 10.231.4.0/24...

Can someone post a simple script in VB that i can incorporate in Access?

The format of the IP column is "Text"

Any help is appreciated.

Thank you

 

[PHV]

Why not simply use the LIKE operator in SQL ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[zaradell]

PHV

Thank you for your reply

The person who accesses the SQL database isn´t allowed access to the Linux machine where the SQL database is hosted.

We created a specific user with view privileges for the specific purpose of our client accessing the SQL DB by an ODBC connector in Access, so basically our client access is limited to Microsoft Access.

Is there a way to filter the results by IP addresses and specific subnets, using SQL operators in Access?

 

[PHV]

Example in JetSQL (ie a Query in Access)

SELECT * FROM yourLinkedTable WHERE yourIPfield LIKE '10.231.3.*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[zaradell]

PHV

The IP addresses that I´m trying to filter are not classful.

I have to filter by their correspondent CIDR

for instante

I want to filter IP addresses by the 158.80.0.0/17 network; the IP range addresses is as follow:

158.80.0.1 - 158.80.127.254

that means that i can´t use the LIKE '158.80.*' because the address 158.80.128.3 is not from that network range, but still falls on the LIKE filter.

Is there any syntax in SQL where i can filter results by subnet addresses (CIDR)?

 

[PHV]

In a standard code module create the following function:

Public Function ip2num(ip)
Dim i, a, n
a = Split(ip, ".")
n = CDbl(0)
For i = 0 To UBound(a)
  n = n * 256 + a(i)
Next i
ip2num = n
End Function

Now the query for 158.80.0.0/17:

SELECT * FROM yourLinkedTable WHERE ip2num(yourIPfield) BETWEEN ip2num("[!]158.80.0.0[/!]") And (ip2num("158.80.0.0")+2^(32-[!]17[/!])-1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[zaradell]

PHV

Thkx...it worked

I´m still checking if there´s any bugs, mas at a first impression it seems to work fine!