Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Data4920
Frequent Visitor

List location based on IP address from a range in another table

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!

1 ACCEPTED 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"

 

 

 

lbendlin_0-1595462661481.png

 

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

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!

 

LocationNetwork IDIPStartIPEndIPStartNumIPEndNum
Site ASite A_11.20.50.11.20.50.25410200500011020050254
Site ASite A_22.35.40.12.35.43.25420350400012035043254
Site ASite A_32.40.40.62.40.40.720400400062040040007

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"

 

 

 

lbendlin_0-1595462661481.png

 

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.