cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
reno1 Frequent Visitor
Frequent Visitor

IP address mapping

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. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
Super User

Re: IP address mapping

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.

 

Power BI - Invoke Custom Function.png

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.

Delete later - IP Address Table.png

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

You should then get the details for each IP Address.

Delete later - IP Address Table.png

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

 

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



Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"
Super User
Super User

Re: IP address mapping

Hi @reno1

 

The first step that you need to do, is to create a Blank Query.

Then go into the Advanced Editor for the Blank Query you just created.

Once in the Advanced Editor the copy and paste the code.

 

Once done you should see the function with the option to put in the value



Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"
22 REPLIES 22
Super User
Super User

Re: IP address mapping

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.



Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"
reno1 Frequent Visitor
Frequent Visitor

Re: IP address mapping

@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?

dkay84_PowerBI New Contributor
New Contributor

Re: IP address mapping

I assume the subnet doesn't change the location of the ip range, correct?

 

Can you share the IP locations database? Or provide some sample data that we can play with?

Super User
Super User

Re: IP address mapping

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.



Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"
reno1 Frequent Visitor
Frequent Visitor

Re: IP address mapping

@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.

 

Highlighted
Super User
Super User

Re: IP address mapping

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.

 

Power BI - Invoke Custom Function.png

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.

Delete later - IP Address Table.png

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

You should then get the details for each IP Address.

Delete later - IP Address Table.png

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

 

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



Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"
reno1 Frequent Visitor
Frequent Visitor

Re: IP address mapping

@GilbertQ, thanks for the detailed answer! Really helpful. 

 

I cant seem to get it to work for me though. 

 

Firstly I go into the query editor, right click on the table containing the IPs, and select "Create Function....". Then I get this error message:

 

1.png

 

Then I go 'Create' > Enter the name of the function > Paste the code into the box > Continued with this

dialogue:
 
 2.png

NOTE: My table is called 'LocationTest'.

 

I then go to where my data is in the query editor and try to invoke custom function. In this dialogue, I cannot select anything for 'Function Query':

 

3.png

 

Have I done something wrong here? I have a feeling that I'm not creating the custom function correctly. 

 

Thanks again for all your help.

 

Super User
Super User

Re: IP address mapping

Hi @reno1

 

The first step that you need to do, is to create a Blank Query.

Then go into the Advanced Editor for the Blank Query you just created.

Once in the Advanced Editor the copy and paste the code.

 

Once done you should see the function with the option to put in the value



Did I answer your question? Mark my post as a solution!

"Proud to be a Datanaut!"
reno1 Frequent Visitor
Frequent Visitor

Re: IP address mapping

@GilbertQ, managed to get it working great thanks to your help! Thanks heaps Smiley Happy