Mapping IP address towards IP subnets

[pade]

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.

IP Address:
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)

Subnet, IP-Address
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

[Anonymous]

@padecould you send me the pbix file too?

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

[pade]

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

[v-ljerr-msft]

@pade

---

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

[pade · 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],
#"Promoted Headers" = Table.PromoteHeaders(#"IP Ranges"),
#"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],
#"Added Custom" = Table.AddColumn(output, "IP Address", each Text.Split([IP Addresses],",")),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"IP Addresses"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns1", "IP Address")
in
#"Expanded Custom"

[cnschulz]

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)

[dvlas]

Hey pade, 

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