Lookup who owns an IP address

Oracle APEX and Oracle REST Data Services on a busy system can generate a lot of access data. This data has of course the remote ip address that is accessing it. Those IP addresses are known and assigned to companies. My thought was to find that data and get it into the database where SQL could be used to join to the access data.

MaxMind - ASN database

Autonomous System Number simply is the number assigned to companies that own ip address ranges. The full details are on the wiki page. The important thing is MaxMind has a csv version of all this data which they make public and under Creative Commons Attribution-Share A like 4.0

Great news GeoLite2 customers! We just added a GeoLite2 ASN database. You can find details here: https://t.co/n5hTJJhSEj.

— MaxMind (@maxmind) May 9, 2017

Github

Here’s the github repo where I started this oracle-maxmind. The project’s README has the details all outlined. There’s a small install script that uses SQLcl’s LOAD command to load up the CSV file. Then a plsql package to convert the IPs into numbers to make it easier to lookup and join.

Putting it to use with the APEX_ACTIVITY_LOG

Next Steps

The next things to do is add Country,City. and IPv6 which is in the csv files