Forum Discussion
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.
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
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"
6 Replies
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],
#"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"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
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
