Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

micheledenardi
Valued Contributor

Get Geolocation Data from IP by using QlikSense Rest Connector

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

1 Solution

Accepted Solutions
micheledenardi
Valued Contributor

Re: Get Geolocation Data from IP by using QlikSense Rest Connector

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

1 Reply
micheledenardi
Valued Contributor

Re: Get Geolocation Data from IP by using QlikSense Rest Connector

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