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

Check IPs with a range of IP's (from datacenters list)

Hello Everyone

 

I have a problem and I'm not sure what is the best way to solve it.... and also correctly...

 

I have a list with all datacenters in the world (found at https://github.com/client9/ipcat/blob/master/datacenters.csv if anyone want this).

 

example of the list/table is as follows:

 

Ip FromIp ToDatacenter Provider
13.64.0.013.71.255.255Microsoft Azure
13.72.64.013.72.127.255Microsoft Azure

 

 

And I also have many IP's that I want to compare with that list/table so that I can figure from which datacenter this IP comes from.

For example an IP 13.64.12.12 will be within the range of the list (as its within the 13.64.0.0 to 13.71.255.255) so it will be Microsoft Azure

While the IP 13.72.63.0 its not in the list (as its below the value 13.72.64.0) thus it will be "Not Datacenter"

 

I would like to note that I found saw this solution https://community.powerbi.com/t5/Desktop/Turn-IP-Range-into-list-of-IP-s/m-p/538212 however in my case is not applicable as is not only the last "." and also if you see the list that I have it will create millions of entries!

It will need very heavy processing!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: Check IPs with a range of IP's (from datacenters list)

Hi @aabi ,

 

Please check the following steps as below.

 

1. Split the ip columns by "." in power query.

Capture.PNG

2. To create calculated columns as below.

 

ip = iplist[ip.1]*100000000+iplist[ip.2]*1000000+iplist[ip.3]*1000+iplist[ip.4]
ipfrom = 'Table'[Ip From.1]*100000000+'Table'[Ip From.2]*1000000+'Table'[Ip From.3]*1000+'Table'[Ip From.4]
ipto = 'Table'[Ip To.1]*100000000+'Table'[Ip To.2]*1000000+'Table'[Ip To.3]*1000+'Table'[Ip To.4]

3. After that, we can achieve our goal by a calculated column in ip table.

isornot = 
VAR cr =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', 'Table'[ipfrom] <= iplist[ip] && 'Table'[ipto] >= iplist[ip] )
    )
RETURN
    IF ( ISBLANK ( cr ), "Not Datacenter", "Datacenter" )

2.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support Team
Community Support Team

Re: Check IPs with a range of IP's (from datacenters list)

Hi @aabi ,

 

Please check the following steps as below.

 

1. Split the ip columns by "." in power query.

Capture.PNG

2. To create calculated columns as below.

 

ip = iplist[ip.1]*100000000+iplist[ip.2]*1000000+iplist[ip.3]*1000+iplist[ip.4]
ipfrom = 'Table'[Ip From.1]*100000000+'Table'[Ip From.2]*1000000+'Table'[Ip From.3]*1000+'Table'[Ip From.4]
ipto = 'Table'[Ip To.1]*100000000+'Table'[Ip To.2]*1000000+'Table'[Ip To.3]*1000+'Table'[Ip To.4]

3. After that, we can achieve our goal by a calculated column in ip table.

isornot = 
VAR cr =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', 'Table'[ipfrom] <= iplist[ip] && 'Table'[ipto] >= iplist[ip] )
    )
RETURN
    IF ( ISBLANK ( cr ), "Not Datacenter", "Datacenter" )

2.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

aabi Frequent Visitor
Frequent Visitor

Re: Check IPs with a range of IP's (from datacenters list)

Hi Frank

 

Thank you for this! You helped alot!

 

While this works as you made it, and I can differiantiate between "Datacenters" and "Not Datacenters".

It's not what I need, because the results that I want is the actual datacenter name. Just my example might not have been that good as I only had Azure as datacenter.

 

I really appreciated the idea/logic of using COUNTROWS, to find the range as my main issue when I was trying to make a filter was that I could not select the table (by using normal count) and I got stuck!

 

I changed the formula as follows and now I'm getting the result that I want!

 

Hosted by =
CALCULATE (
ALLSELECTED(datacenters[Datacenter]),
FILTER ( 'datacenters', 'datacenters'[IP from into Decimal] <= 'Ip''s'[IP into Decimal] && datacenters[IP to into Decimal] >= 'Ip''s'[IP into Decimal] ))
 
Keep it simple!:)

 

P.S. Also the idea for using the splitextbydelimiter was very good! It never crossed my mind of doing that and instead I spent time to make a formula that reads the "." with MID and FIND to make the breakdown of the IP!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)