Follow the procedure below in order to update your existing Geo Location dataset to use in Configuring Geolocation Based Statistics.
- Download the latest CSV file from here.
Download the geoip-2-csv-converter from https://github.com/maxmind/geoip2-csv-converter/releases according to your operating system.
Prepare the database entries
- Unzip the latest CSV file and the geoip-2-csv-converter your downloaded in step 1 and 2 above.
Run update-geolocation-data.sh file withbelow command.
sh update-geolocation-data.sh
Enter the path to extracted GeoLite2-City-Blocks-IPv4 directory which you downloaded first as the response for “Enter path to GeoLite2-City-Blocks-IPv4 directory: “
E.g : /<PATH_TO>/GeoLite2-City-CSV_20171107
Enter path to geoip2-csv-converter directory as the response for “Enter path to geoip2-csv-converter home directory:"
E.g : /<PATH_TO>/geoip2-csv-converter-v1.0.0
After script execution, you will find the final.csv file inside the current directory you are in.
Code Block ** get first column form original ** change column name to ‘network_cidr’ ** Extract ip address data ** change column name to ‘network_blocks’ ** extract entries from original cut: /home/chamalee/xxx/geoip2-csv-converter-v1.0.0/GeoLite2-City-Blocks-IPv4-converted.csv: No such file or directory ** change column name to ‘network’ ** change column name to ‘broadcast’ ** merge csv files
- Shut down both APIM and APIM-Analytics servers if you are running them already.
- Truncate BLOCKS and LOCATION tables from GEO_LOCATION_DATA database.
| Note |
|---|
Alternatively you can drop the tables in the GEO_LOCATION_DATA database and create new tables. |
Importing Data
Import the created final.csv file into BLOCKS table.
Code Block load data local infile '[PATH_TO_FINAL.CSV]/final.csv' into table BLOCKS fields terminated by ',' enclosed by '"' lines terminated by '\n' (network_cidr, network, broadcast, geoname_id, registered_country_geoname_id, represented_country_geoname_id, is_anonymous_proxy, is_satellite_provider, postal_code, latitude, longitude, network_blocks);
Import GeoLite2-City-Locations-en.csv which is located inside the extracted geoip-2-csv-converter directory (e.g geoip-2-csv-converterGeoLite2-City-CSV_2017110) into LOCATION table.
Code Block load data local infile '[PATH_TO_GeoLite2-City-Locations-en]/GeoLite2-City-Locations-en.csv' into table LOCATION fields terminated by ',' enclosed by '"' lines terminated by '\n' (geoname_id, locale_code, continent_code, continent_name, country_iso_code, country_name, subdivision_1_iso_code, subdivision_1_name, subdivision_2_iso_code, subdivision_2_name, city_name, metro_code, time_zone);
Restart WSO2 API Manager and WSO2 APIM-Analytics servers.
...