As of recently, I quite often receive Excel files with hundreds of IPs which need to be geolocated (Can you guess where they come from?). Sure, I could import them into our Elastic stack, but would that post then be titled “Dirty”?
So we need to come up with a different solution. While researching the topic, the first thing that became clear was that I need an offline solution. Although most of the time I just try to locate a handful of addresses, but this sometimes explodes to up to 5.000. In that case, it is almost impossible to find a suitable web API. The most generous free services only provide up to 1.000 lookups per week.
Luckily there is MaxMind. They are the company behind GeoIP, which also offers a free offline database. Simple register for an account on their website.
Then access your account and select “Download Files” in the left menu. From the list, select the “GeoLite2 City” database file. It’s important to use the GeoIP2 Binary format.
Finally, it’s time to get dirty: Download the script dirtyiplocate.py from Github or clone the whole repository. After that, the geoip2 modules needs to be installed (pip install geoip2).
After all that hard work, dirtyiplocate.py is ready to rumble. As shown in the following screenshot it’s pretty easy to use. Provide a text file with IPs you want to locate (the –ips argument) and specify the output CSV file (the –output parameter). In case you want the results to be appended to the output file instead of overwriting it, the –append argument can be used.
The following screenshot shows the output of dirtyiplocate.py. Excel’s VLOOKUP can now be used to incorporate this data into existing lists. Please always use the datatype TEXT for IP addresses. Otherwise a unicorn dies!
To summaries: I built a dirty script that can be used to do bulk IP geolocation. It can be downloaded here: https://github.com/fbogner/dirtyiplocate