Using Geolocation Based Statistics

Follow the procedure below in order to configure WSO2 IS Analytics Server to show the regions of the users authenticated though WSO2 Identity Server.

1. Download the Geolocation data from here.
2. Unzip the file you downloaded.

3. Create the database by executing one of the scripts in the Geolocation Data/dbscripts directory. In this example, mysql.sql is executed.

   This can be done using the MySQL Workbench.

   For detailed instructions to run the database script, see MySQL Documentation - The Workbench Scripting Shell.

4. Restore data to the BLOCKS and LOCATION tables by importing data from BLOCKS.csv and LOCATION.csv in .Geolocation Data/data directory of the extracted zip using below commands.
   
   

   • Importing Geolocation Data/data/LOCATION.csv


     mysqlimport -u root -p --ignore-lines=2 --fields-terminated-by=, --fields-optionally-enclosed-by='"' --local GEO_LOCATION_DATA <path_to_folder_location>/GeolocationData/data/LOCATION.csv 

   • Importing Geolocation Data/data/BLOCKS.csv

     mysqlimport -u root -p --ignore-lines=2 --fields-terminated-by=, --fields-optionally-enclosed-by='"' --local GEO_LOCATION_DATA <Extracted_location>/GeolocationData/data/BLOCKS.csv

   For more information, see MySQL Documentation - Data Export and Import.

5. Check whether your imported dataset is properly working using executing following query in MySQL Command Line.

   SELECT loc.country_name,loc.subdivision_1_name FROM BLOCKS block , LOCATION loc WHERE block.network_blocks = '<Network_part_of_ipv4>' AND
    <Long_value_of_publilc_IP> BETWEEN block.network AND block.broadcast AND block.geoname_id=loc.geoname_id;

   Sample Query

   SELECT loc.country_name,loc.subdivision_1_name FROM BLOCKS block , LOCATION loc WHERE block.network_blocks = '221.192' AND 3720398641 
   BETWEEN block.network AND block.broadcast AND block.geoname_id=loc.geoname_id;

6. Download a JDBC provider depending on the database you are using (MySQL in this example), and copy it to <IS_ANALYTICS_HOME>/repository/components/lib directory.

7. Configure datasource in the <IS_ANALYTICS_HOME>/repository/conf/datasources/geolocation-datasources.xml file as follows.

   <datasources-configuration xmlns:svns="http://org.wso2.securevault/configuration">
       <datasources>
           <datasource>
               <name>GEO_LOCATION_DATA</name>
               <description>The datasource used for Geo location database</description>
               <jndiConfig>
                   <name>jdbc/GEO_LOCATION_DATA</name>
               </jndiConfig>
               <definition type="RDBMS">
                   <configuration>
                       <url>jdbc:mysql://localhost:3306/GEO_LOCATION_DATA</url>
                       <username>wso2carbon</username>
                       <password>wso2carbon</password>
                       <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                       <maxActive>50</maxActive>
                       <maxWait>60000</maxWait>
                       <testOnBorrow>true</testOnBorrow>
                       <validationQuery>SELECT 1</validationQuery>
                       <validationInterval>30000</validationInterval>
                       <defaultAutoCommit>false</defaultAutoCommit>
                   </configuration>
               </definition>
           </datasource>
       </datasources>
   </datasources-configuration>

8. Restart WSO2 IS Analytics server.