Updating a contact's Location after Maxmind database download

Product Support
1

I realised my Maxmind database file wasn’t updating. After adding in my license key, I can now download the maxmind database file no problem. But I’m left wondering - is there a way to update the Location (country/city) of existing contacts in my system?

There doesn’t seem to be a cron method available for this. But I have over 8000 contacts with the city/country fields missing. Now maxmind is properly set up, I’d like to populate these fields.

Thanks

My Mautic version is: 3.2.4
My PHP version is: v7.3.27
My Database type and version is: Ver 15.1 Distrib 10.3.27-MariaDB

2

Anonymous contacts I assume and not your identified contacts? I’m not aware of a way to retroactively update IP location.

3

Thanks for the reply EJL.

I’m referring to identified contacts. Specifically, 1k+ contacts who’ve already submitted a ‘sign up for a newsletter’ form, with name +email.

4

@pjrobertson Interesting problem!

I think there is no ready to run solution in mautic.

The geocoding for the ip address is only done during the page hit processing:

The geocoding is only happening if you disabled ip anonymization (anonymize_ip) and the geocoding details are persisted in the ip_addresses Database Table (under ip_details).

I got a similar problem last time and I used an external tool to geocode just the ip adresses to the countries and then imported the data back into the database.

(i used Bulk IP address lookup | Batch IP checker | WhoisXML API for the geocoding but there are others )

Or maybe you use a tool like this on github GitHub - lexmartinez/ip-geocoder: Find geocoding information from IP address - Powered by ipapi.co and script your own solution.

Greetings
Sebastian

2 Likes
5

Thanks for the follow up @sfahrenkrog1 !

I just looked into the ip_addresses and lead_ips_xref tables. I thought I could use your script idea to replace the ip_details content in the ip_addresses table, but the majority of the IPs seem to be set at 127.0.0.1. Strange (I don’t have ‘IP anonymization’ set up):

MariaDB> select count(*) from ip_addresses where ip_details = "N;";
+----------+
| count(*) |
+----------+
| 7615 |
+----------+

MariaDB> select count(*) from ip_addresses where ip_details = "N;" and ip_address != "127.0.0.1";
+----------+
| count(*) |
+----------+
|      249 |
+----------+

So while I thought the initial problem was that my IP addresses weren’t getting geo-populated, it seems like a bigger issue is that a large majority of IPs are set to 127.0.0.1. I thought it might be because of an nginx front end acting as reverse proxy (see this) but I don’t think so (although I have changed servers in the past 6 months, and things appear to be working now).

6

Do you use php fpm in your setup?

7

Not using php-fpm. I’m using CGI/FastCGI. My first thought was that it was a proxy issue, but that doesn’t seem to be the case.

8

You will most likely need to pass through the original client IP address - also the case if you are using something like Cloudflare.

Maybe this could help: Using the Forwarded header | NGINX ?