1 Reply Latest reply: Oct 31, 2017 7:37 AM by Michele De Nardi RSS

    Get Geolocation Data from IP by using QlikSense Rest Connector

    Michele De Nardi

      Hi Experts,

      is there someone who know if is it possible to geo-localize at country level a list of IPs by using WebServices and QlikSense Rest connector ?

       

      I've got a list of public IPs and i would like to know from which country they are from.

       

      Thanks

        • Re: Get Geolocation Data from IP by using QlikSense Rest Connector
          Michele De Nardi

          By using the free service IP Geolocation online API which support Rest connection i have built this call:

           

          set vMyApyKey='xxxxxxxxx';
          set vIpAddress='xxx.xxx.xxx.xxx';
          
          let vConnection='Provider=QvRestConnector.exe;url=http://api.db-ip.com/v2/'& vMyApyKey & '/'& vIpAddress &';timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;PaginationType=None;XUserId=TestUse;XPassword=TestUserPsw;';
          
          
          CUSTOM CONNECT TO "$(vConnection)";
          
          RestConnectorMasterTable:
          SQL SELECT
          "ipAddress",
          "continentCode",
          "continentName",
          "countryCode",
          "countryName",
          "stateProv",
          "city"
          FROM JSON (wrap on) "root";
          
          [root]:
          LOAD
          [ipAddress],
          [continentCode],
          [continentName],
          [countryCode],
          [countryName],
          [stateProv],
          [city]
          RESIDENT RestConnectorMasterTable;
          
          DROP TABLE RestConnectorMasterTable;
          
          

           

           

          after this solution i have found also another easier way by using http://freegeoip.net:

           

          set vIpAddress='xxx.xxx.xxx.xxx';
          
          let vConnection='Provider=QvRestConnector.exe;url=http://freegeoip.net/csv/'& vIpAddress &
          ';timeout=30;method=GET;autoDetectResponseType=true;keyGenerationStrategy=0;useWindowsAuthentication=false;useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;PaginationType=None;XUserId=XXX;XPassword=XXX;';
          
          CUSTOM CONNECT TO "$(vConnection)";
          
          RestConnectorMasterTable:
          SQL SELECT
          "col_1",
          "col_2",
          "col_3",
          "col_4",
          "col_5",
          "col_6",
          "col_7",
          "col_8",
          "col_9",
          "col_10",
          "col_11"
          FROM CSV (header off, delimiter ",", quote """") "CSV_source";
          
          [CSV_source]:
          LOAD [col_1],
          [col_2],
          [col_3],
          [col_4],
          [col_5],
          [col_6],
          [col_7],
          [col_8],
          [col_9],
          [col_10],
          [col_11]
          RESIDENT RestConnectorMasterTable;
          
          DROP TABLE RestConnectorMasterTable;
          
          

           

          By using both soultion, the result is the same:

          2017-10-31 12_36_25-_GeoIP _ Sistema di visualizzazione modello dati - Qlik Sense.png