Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I'm trying to match up a location to a specific asset based on the assets IP address given a list of ranges from another table. Neither table has a unique column that will allow me to join them together.
The ASSET table lists a single IP address per asset however it may be listed many times based on the number of attributes it might have.
ASSET Table |
IP |
<IP1> |
<IP2> |
<IP2> |
<IP2> |
<IP3> |
<IP3> |
<IP3> |
<IP3> |
The NETWORK table lists all of the starting and ending IP's per range for all existing subnets at that location.
NETWORK Table |
|
| |
Location | Network ID | Beginning IP | Ending IP |
Site 1 | N_ID1 |
|
|
Site 1 | N_ID2 |
|
|
Site 1 | N_ID3 |
|
|
Site 2 | N_ID4 |
|
|
Site 2 | N_ID5 |
|
|
What I've done so far (which I'm happy to change if it results in a working query) is to convert both the beginning and ending IP's in the NETWORK table and the IP in the ASSET table to a single number by splitting them up by '.' delimiters then adding them back together in Power Query by using this ("IPStart", each [IP Start.1]*1000000000 + [IP Start.2]*1000000 + [IP Start.3]*1000 + [IP Start.4], type number)). To add the location to the Asset table, I was hoping to do something like IF ASSET[IP] is >= IPStart AND ASSET[IP] <= IPEnd then list location but I’m not sure how to make the connection between tables and list the correct location. Any help would be greatly appreciated!
Solved! Go to Solution.
Not sure if I am oversimplifying this but here's a version. Calculated Column in the IPs table. No relationship between tables.
NetworkID =
var n = IPs[IPNum]
return CALCULATE(max(Networks[Network ID]),Networks[IPStartNum]<=n,Networks[IPEndNum]>=n)
Here are the definitions for the queries I used:
Networks:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dYy7CoAwDEV/RTqXcJs07ew3OEpxcnD3/zF9gAURMpxDuGff3Xbd57I6P+AIhoEYpKCZWWM1MKAAwiv1U/y3xIZMohR7qbOMEkMUcZS6yF9J2toydmni3KYWqZ00S3alPA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, #"Network ID" = _t, IPStart = _t, IPEnd = _t, IPStartNum = _t, IPEndNum = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"IPStartNum", Int64.Type}, {"IPEndNum", Int64.Type}})
in
#"Changed Type"
IPs:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcnBCcAwDEPRXXwOQlbi0swSsv8alQtfB/HOidxCPi8msmJEavuRk7532CGiXCNFFr2fhMWuSVzsmu4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IP = _t, IPNum = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"IPNum", Int64.Type}})
in
#"Changed Type"
Do you have last digit subnet granularity or is it sufficient to look at the first three digits? IE do your list ranges ever specify anything besides 0 and 255 in the fourth digit?
Are the site address ranges overlapping? ie can an asset potentially be mapped to multiple ranges, or are all your ranges mutually exclusive?
What should happen if the IP doesn't fall into any of the listed subnets?
The calculated column would be relatively simple - for each IP/Asset you would check if any subnet start is smaller than the IP or equal, and then from that result list you check which subnet end is higher or equal than the IP.
The entire range would need to be evaluated since all 4 digits could vary even within a single site given the different networks, especially in some larger data centers. I've tried to provide another example below from the Network table with some dummy IP's including the converted IP number that I created to attemp the lookup. Each range is identified by the Network ID and is continuous from IPStart to IPEnd. The IP's that I need to lookup from the Asset table are in the same format as IPStartNum and once PBI has identified that it falls within one of the Network ID ranges, I need the location to be displayed in the Asset in a new column. Now there are some assets with a common 192.168.X.X IP's that you might see on a home or guest network but those would not be mapped to a location since they are not officially company ranges so I'd probably list the location as Unknown along with anything else not found. Aside from that the ranges are mutually exclusive.
The calculated column would be relatively simply but without the tables being joined in anyway, I'm not sure how to do write it so that the Asset IP scans every row in the Network table until it finds a match. TIA!
Location | Network ID | IPStart | IPEnd | IPStartNum | IPEndNum |
Site A | Site A_1 | 1.20.50.1 | 1.20.50.254 | 1020050001 | 1020050254 |
Site A | Site A_2 | 2.35.40.1 | 2.35.43.254 | 2035040001 | 2035043254 |
Site A | Site A_3 | 2.40.40.6 | 2.40.40.7 | 2040040006 | 2040040007 |
Not sure if I am oversimplifying this but here's a version. Calculated Column in the IPs table. No relationship between tables.
NetworkID =
var n = IPs[IPNum]
return CALCULATE(max(Networks[Network ID]),Networks[IPStartNum]<=n,Networks[IPEndNum]>=n)
Here are the definitions for the queries I used:
Networks:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dYy7CoAwDEV/RTqXcJs07ew3OEpxcnD3/zF9gAURMpxDuGff3Xbd57I6P+AIhoEYpKCZWWM1MKAAwiv1U/y3xIZMohR7qbOMEkMUcZS6yF9J2toydmni3KYWqZ00S3alPA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, #"Network ID" = _t, IPStart = _t, IPEnd = _t, IPStartNum = _t, IPEndNum = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"IPStartNum", Int64.Type}, {"IPEndNum", Int64.Type}})
in
#"Changed Type"
IPs:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcnBCcAwDEPRXXwOQlbi0swSsv8alQtfB/HOidxCPi8msmJEavuRk7532CGiXCNFFr2fhMWuSVzsmu4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IP = _t, IPNum = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"IPNum", Int64.Type}})
in
#"Changed Type"
Thanks worked for me
Using the DAX provided produced an error "Cannot convert value 'Site A_1' of type Text to type Integer", where Site A_1 is a valid Network ID. All of the Network ID's are alphanumeric (i.e. ABCD1, BCDE3) and listed in the column as type text. Does that change the 'Calculate(Max' approach or is there away to do some kind of conversion during the query? I feel like that would work if the network ID was strictly numeric.
Try my solution in a blank Power BI Desktop file. The DAX error must be elsewhere.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
11 | |
10 | |
8 |
User | Count |
---|---|
62 | |
20 | |
11 | |
11 | |
11 |