cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

## Mapping IP address towards IP subnets

This has been challenging me for a while

I have a list with all our network connected devices (a lot of them...), and I have the entire corperate (as good as it can be) IP Subnet list, see tables below.

I would like to map the IP-Address e.g. 192.168.2.4 into a subnet, e.g. 192.168.2.0/28 subnet.

192.168.1.5
192.168.2.3
192.168.2.4
192.168.2.5

etc...

Subnets:
192.168.1.0/24
192.168.2.0/28
etc…

Anyone that have done this before?

One way is to re-calculate the IP address (192.168.1.0) into a decimal number (3'232'235'776) as 192*256^3+168*256^2+1*256^1+0*256^0, and calculate the subnet low and high value the same way based on the number of adresses defined by the CD (256 for a CDIR of 24 based on 192.168.1.0/24). So the High IP range for 192.168.1.0/24 will then be 3'232'236'031 (3'232'235'776 + (256-1))

I can then either try to discover if the IP Address 192.168.1.65 (3'232'236'096) is within that range.

Subnet IP, Base address, CIDR, Netmask, # of Addresses, IP Low, IP High, IP Low [Decimal], IP High [Decimal]
192.168.1.0/24, 192.168.1.0, 24, 255.255.255.0, 256, 192.168.1.0, 192.168.1.255, 3232235776, 3232236031
192.168.2.0/28, 192.168.2.0, 28, 255.255.255.240, 16, 192.168.2.0, 192.168.2.15, 3232236032, 3232236047

Or I can create a table with all IP addresses within all my subnets, and the do a lookup between my IP-Address table and the Subnet table (that includes all IP adresses within all my subnets). But this table could be quite big since I have some large subnets (/16)

192.168.1.0/24, 192.168.1.0
192.168.1.0/24, 192.168.1.1
192.168.1.0/24, 192.168.1.2
192.168.1.0/24, 192.168.1.3
192.168.1.0/24, 192.168.1.4
192.168.1.0/24, 192.168.1.5
192.168.1.0/24, etc…
192.168.2.0/28, 192.168.2.0
192.168.2.0/28, 192.168.2.1
192.168.2.0/28, 192.168.2.2
192.168.2.0/28, etc…

As I'm quite new to Power BI and DAX i figured out that this will take me quite a long time, and I will probably choose the wrong solution as well. Anyone done this before, or have an idea for the best solution. If so, I guess it will be reused by others since this is quite a common problem in IT

1 ACCEPTED SOLUTION

So I did, and ended up with this code. I utilized the R Script library "iptools" for this, not a perfect solution since it requires R installed, but it works. Since I'm not a "native writing" programmer for neither R, Power Query, or DAX, this code might not be the best, but

let
Source = Excel.Workbook(File.Contents("C:\xxx\IP_Networks.xlsm"), null, true),
#"IP Ranges" = Source{[Item="IP Ranges",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Subnets", type text}}),
#"Run R Script" = R.Execute("# 'dataset' holds the input data for this script#(lf)library(iptools)#(lf)output <- dataset#(lf)for (t in 1:dim(dataset)[1]) {output[t,""IP Addresses""]=paste0(range_generate(as.character(dataset[t,""Subnets""])),collapse="","")}",[dataset=#"Changed Type"]),
output = #"Run R Script"{[Name="output"]}[Value],
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns1", "IP Address")
in
#"Expanded Custom"

6 REPLIES 6
Anonymous
Not applicable

@padecould you send me the pbix file too?

Let me know and I'll send you my email.

Sorry @Anonymous . It was so long ago, and I can't find the file anymore

Microsoft

As I'm quite new to Power BI and DAX i figured out that this will take me quite a long time, and I will probably choose the wrong solution as well. Anyone done this before, or have an idea for the best solution. If so, I guess it will be reused by others since this is quite a common problem in IT

I would suggest you to use the second solution you have mentioned above. After creating a table with all IP addresses within all the subnets, you can just use column IP-Address to create a relationship between IP-Address table and Subnet table.

As the IP-Address is already mapped into the Subnet through the relationship, no any other lookups between the IP-Address table and the Subnet table is needed.

Regards

So I did, and ended up with this code. I utilized the R Script library "iptools" for this, not a perfect solution since it requires R installed, but it works. Since I'm not a "native writing" programmer for neither R, Power Query, or DAX, this code might not be the best, but

let
Source = Excel.Workbook(File.Contents("C:\xxx\IP_Networks.xlsm"), null, true),
#"IP Ranges" = Source{[Item="IP Ranges",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Subnets", type text}}),
#"Run R Script" = R.Execute("# 'dataset' holds the input data for this script#(lf)library(iptools)#(lf)output <- dataset#(lf)for (t in 1:dim(dataset)[1]) {output[t,""IP Addresses""]=paste0(range_generate(as.character(dataset[t,""Subnets""])),collapse="","")}",[dataset=#"Changed Type"]),
output = #"Run R Script"{[Name="output"]}[Value],
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns1", "IP Address")
in
#"Expanded Custom"

Helper IV

Thank you. Thank you. Thank you. 🙂

2020 Issues I found:

1. You need to use R 3.5.0

2. Even if you have iptools installed under 4.0 and then install 3.5.0, you *must* uninstall iptools and the reinstall it to make sure that it is installed under 3.5.0 (even though it will recognise it in R, Power BI will not find it)

New Member

Can you share the powerbi file please, because I am trying to implement the code you shared in my project, and I still get errors 😞

Thanks.

dorin.vlas "at" gmail.com

DV

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors