IP address mapping

[reno1]

I have a 'DirectQuery' table set up in Power BI with a field containing IP adresses. Ideally, I would like to visualize the locations of all the IPs using the Map. When I use the 'IP' field as a data input for the Map, I do get some IP addresses that show as a location. The issue is, that these IP addresses are mapped at the incorrect location. Most of the IPs don't even show on the map. I was wondering how others were able to map locations of IPs? I need a method that can automatically locate IPs with no user action, as new data is constantly being added to my table and I need the visualizations to keep in sync.

Any help would be greatly appreciated. 

[GilbertQ · Accepted solution]

Hi @reno1

You can do it with the following steps below.

You need to create a function with the following code below with the name of fn_GetIPAddress

let
    Source = (#"IP Address" as text) => let
        Source = Json.Document(Web.Contents("http://freegeoip.net/json/" & #"IP Address")),
        #"Converted to Table" = Record.ToTable(Source),
        #"Transposed Table" = Table.Transpose(#"Converted to Table"),
        #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")
    in
        #"Promoted Headers"
in
    Source

The function will return the output into a table.

Then what you do is where your data is in the Query Editor you click on Invoke Custom Function, then put in the required fields as shown below.

NOTE: Make sure to select your column above that has got the IP Addresses

Then once you click Ok you will see the table as shown below.

Click on the Expand Table button, leave all the defaults and click Ok.

You should then get the details for each IP Address.

NOTE: There are more columns but I snipped them off.

Please let me know if this works or you get stuck.

[GilbertQ]

Hi @reno1,

From my understanding of IP Addresses you first need to do a lookup to an IP Address database or IP Address file in which you can then find out where the IP Address is allocated from. And then get the output from the IP Address database or file and use that to put it into a map.

I would assume you would need to get the IP Address database or file and then due to the structure of the file, you would need to do some process to find out where the IP Address sits within the range of allocated IP Addresses. Once that is done it would be able to link the location of the IP Address and use that in a map?

It would be a bit of a process to get this working.

[reno1]

@GilbertQ thanks for the response. 

I have found an IP address database and successfully imported it into power BI. What I am unsure about now is how to find where each IP address sits within the range of IP addresses in the IP database. The IP addresses I have are in the format '8.8.8.8', and the corresponding row in the IP database is '8.8.8.0/16'. Do you have any idea how to do this in Power BI, or where I can go to for information?

[GilbertQ]

Hi @reno1, yes that would be the next challenge to convert it into the range of IP Addresses.

Another thought is if it is possible to pass the IP Address to a Online Lookup Service which would then return the location of the IP Address. And then store the value?

@dkay84_PowerBI, you are correct in that the Subnet does not affect the IP Address due to the IP Addresses being in ranges.

[reno1]

@dkay84_PowerBI, I got the database from 'http://dev.maxmind.com/geoip/geoip2/geolite2/'. I downloaded the csv files and added them to my SQL Server database. I then imported the tables into Power BI.

@GilbertQ, I also had this thought before posting the question. I was planning to use the site 'http://freegeoip.net/' to get the location. For example, when I go to the URL:

http://freegeoip.net/json/8.8.8.8

it returns the JSON:

{"ip":"8.8.8.8","country_code":"US","country_name":"United States","region_code":"CA","region_name":"California","city":"Mountain View","zip_code":"94035","time_zone":"America/Los_Angeles","latitude":37.386,"longitude":-122.0838,"metro_code":807}

This provides all the information I need, though I cannot figure out how to do this automatically for each IP address in Power BI, leading me to ask this question.

[GilbertQ · Accepted solution]

Hi @reno1

You can do it with the following steps below.

You need to create a function with the following code below with the name of fn_GetIPAddress

let
    Source = (#"IP Address" as text) => let
        Source = Json.Document(Web.Contents("http://freegeoip.net/json/" & #"IP Address")),
        #"Converted to Table" = Record.ToTable(Source),
        #"Transposed Table" = Table.Transpose(#"Converted to Table"),
        #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")
    in
        #"Promoted Headers"
in
    Source

The function will return the output into a table.

Then what you do is where your data is in the Query Editor you click on Invoke Custom Function, then put in the required fields as shown below.

NOTE: Make sure to select your column above that has got the IP Addresses

Then once you click Ok you will see the table as shown below.

Click on the Expand Table button, leave all the defaults and click Ok.

You should then get the details for each IP Address.

NOTE: There are more columns but I snipped them off.

Please let me know if this works or you get stuck.

[Anonymous]

Hello,

I make all steps but I have a problem :

"DataSource.Error: The downloaded data is of type HTML, which is not the expected type. The URL may be wrong, or you may not have provided the correct credentials to the server.".

How could I fix it please ?

Thx

[Anonymous]

Hi 

I'm getting the following error while trying to invoke a custom function

"Formula.Firewall: Query 'Final Table' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

Thank you

[dkay84_PowerBI]

Look up “formula firewall error power bi”. This is thoroughly documented online.

[Anonymous]

getting this error:

DataSource.Error: Web.Contents failed to get contents from 'https://ipapi.co/json/*************' (404): Not Found

Please help me 😞

[jnickell]

Well their web resource is still working (for me at least).  Maybe the way your Web.content is structured is incorrect? 

[Anonymous]

I am new to Power BI aswell, i really dont understand what you are saying.
well, i have tried the way it is guided in this article. The code i am using for the function:

(#"IP Address" as text) =>
let
Source = Json.Document(Web.Contents("https://ipapi.co/json/" & #"IP Address")),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")
in
#"Promoted Headers"

Tried below code aswell, same error:

let
Source = (#"IP Address" as text) => let
Source = Json.Document(Web.Contents("https://ipapi.co/json/" & #"IP Address")),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")
in
#"Promoted Headers"
in
Source

[jnickell]

Take a look at their API documenation on how to get a JSON result.  https://ipapi.co/#api

You need to re-order how you are building the Web.Contents string.

You're currently doing this:

Source = Json.Document(Web.Contents("https://ipapi.co/json/" & #"IP Address")),

You should try this:

Source = Json.Document(Web.Contents("https://ipapi.co/" & #"IP Address" &"/json/")),

[Anonymous]

Thanks alot sir. It worked, thanks once again.

[xerxel]

This answer from guavaq  is totally awesome! Amazing! Thanks!

🙂

[jkaemmerling]

Uh... is this legal!? But seriously, good solution, thanks!

Also side-question: how do you reconfigure security/permissions for this function (when connected to DB as your source)?

Power BI asked me to designate security levels(Public, Organizational, Private) between the database i'm connected to and this freegeoip.net.

I marked my database as organizational and freegeoip.net as public which returned a Firewall error.

OR should I ask my admin? 

[GilbertQ]

Hi there, I do not think it is an issue, might need to read the details on their website?

Yes that would be because you are mixing data sources, which then asks about privacy settings.

What I would suggest doing is unless you have very strict security policies in place change the Security Level to Off, so that the two datasets can work together. It is controlled within Power BI Desktop only.

[Anonymous]

Hey @GilbertQ I'm having issues logging into the database because my access is restricted is there a way around this? Or is this purely down to my organizations firewall? 

[GilbertQ]

Hi there

If you cannot log in, then I would assume it would be from where your On-Premise Gateway server is installed to your Database.

[Anonymous]

Sorry, 

I'm a little bit of a tech newbie, could you explain?  It says "Access to the resource is forbidden" but having spoken to my IT department thats because the website has now changed to api.stack.com. They have a new method but being so new to M not sure how to incorporate my access key as well the IP address into the code you wrote above? Could you please advise?  

https://ipstack.com/documentation

[jnickell]

I stumbled on this thread because of a limitation I was seeing in the ESRI ARC GIS map (unable to control zoom) as the dataset changed.

I was able to follow GilbertQ's directions and get things setup and functioning.  As you mentioned they method of access has changed a bit.  

You'll need to setup a Parameter First (IP address)

Add a Web Data Source

Switch to Advanced Mode so you can specify multiple parts

For the first part supply their website address upto and including the "/"

For the second part select to use your parameter

For the third part specify ?access_key=.... for you access key

From there you can convert it to a function and should be able to follow his guide.