Identify geolocation retrospectively in Mautic

Nothing is more sad then a Mautic Contact page with missing little colorful flags. It just doesn’t look organized.

And the worst is: all this information needed to populate the country, city, state data is already available in Mautic. This happens if Mautic starts to collect data before the Maxmind database is installed. Once the setup is done, and ip is being matched, the state / city / country information will be available, and all the nice flags will appear.

But what can we do retrospectively if we’d like to populate this data?

I created a little shell script, that loops through all the missing contact information, taps into the Maxmind DB and makes your Mautic database complete again. It even fills out your postcode.

1. Go to the Mautic folder (or any folder here you keep shell scripts)

2. Create a file

nano geoupdate.sh

3. Copy in the content

4. The script will use your Maxmind and your Database credentials based on your Mautic config. You don’t need to add it one more time. Just make sure you have the right Mautic path defined:

MAUTICPATH="/var/www/html/mautic"

5. Make it executable

chmod +x geoupdate.sh

6. That’s it, you can run it now. It will populate all the missing country, state and city information.

/bin/sh geoupdate.sh

The result should look like this:

2023.03.05. Update:

Thanks to Sebastian Fahrenkrog, there is an update to this script. Additions:

– Using https://www.shellcheck.net/ and fix the things I he with escaping

– Adapt the sql query to skip ip addresses with wildcards (anonymized ips) and local addresses

– change the error handling in the mmdlookup tool (skip errors while lookup ip)

– make it the updates more incremental (sometimes only country is present .. )

– suppress mysql warnings

– change the way the mysql queries are done

Thank you so much Sebastian 🙂 for feeding this back to me.