Forum Discussion

KenDavies's avatar
KenDavies
Qrew Trainee
8 years ago

IP Address and Location

I am trying to match up IP addresses to determine the location of the IP address.  I am primarily concerned with flagging IP addresses that are from outside the U.S.  

I was able to download a file that shows a range of IP addresses by Country. This file is >200K records.  My thought was to just use the US IP ranges (19K records) and just flag any that came from outside the US.

Below is an example of 3 records for US IP Ranges:
IP Address Range is 18332160 - 18332415
IP Address Range is 34603008 - 34603263
IP Address Range is 34607872 - 34608127

With the new Quick Base Audit records I can get the IP addresses for log-ins and use that in a table, but have not figured a way to take the IP address and cross-reference it against the list of 19K ranges.  I tried a formula field with an If statement similiar to ([IP address] > 18332160 AND [IP Address]<18332415) OR ([IP address] > 34603008 AND [IP Address]<34603263) ... repeat for 19K rows, but the formula is too large.

I am trying this with not only the Quick Base audit log-in records, but for other systems we utilize which we'd like to verify log-ins are only from US IP addresses.  

Thank you in advance.  
Ask about Quickbase functionality
Formulas and functions

1 Reply

Sort By
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    8 years ago
    IP addresses come in two varieties IPv4 and IPv6. You are expressing your IP addresses as decimal numbers which is uncommon for IPv4 and never done for IPv6. So I don't think your blacklist / whitelist technique is going to work based on saving the IP addresses in a table and using a formula to test for inclusion or exclusion. Moreover, IP addresses don't map cleanly to location and can easily be defeated by using a proxy of VPN.

    If you want to prevent access from specific IP addresses or other geolocation information you probably have to use script and access a geolocation API service. 

    I do use a geo-location service in my Pastie database to record where users are located. If you want to pursue a IP address or geo-location solution fee free to contact me off-world using the information in my profile:

    https://getsatisfaction.com/people/dandiebolt/