时间戳

首页 > 解决方案 > PowerBI/DAX 计算列从特定机器/网络 ip 和掩码计算网络 CIDR

networking - PowerBI/DAX 计算列从特定机器/网络 ip 和掩码计算网络 CIDR

问题描述

我的公司服务器上只安装了“基本”PBI，因此我需要使用 DAX 或 PowerQuery 找到解决方案。没有 Python，没有 R ...：/

我有两张带有网络 IP 和掩码的表，另一张带有机器 IP 和掩码的表。

我想设置两个表之间的关系。

我的想法是在每个表中计算一个新列。它将是 CIDR 表示法中的网络 ip 和掩码，我将使用它作为设置关系的通用键：

机器--N:1--> 网络

Networks Table
==
netip,        netmask          -> netcidr *
10.10.1.32,   255.255.255.224  -> 10.10.1.32/27


Machines Table
==
machineip,    netmask          -> netcidr *
10.10.1.35,   255.255.255.224  -> 10.10.1.32/27
10.10.1.38,   255.255.255.224  -> 10.10.1.32/27

我已经用 Python 函数完成了，但我需要在 DAX 或 PowerQuery 或 SQL 中进行转换：

def network2(ipmask):
    ip, cidr = ipmask.split('/')
    print(">", ip, cidr)
    a, b, c, d = ip.split('.')
    ipn = (((int(a)*256+int(b))*256)+int(c))*256+int(d)

    cidr = int(cidr)
    mask = (0xffffffff >> (32 - cidr)) << (32 - cidr)
    net = ipn & mask

    print (ipn, cidr, mask, net )
    
    a = net%(256**4)//(256**3)
    b = net%(256**3)//(256**2)
    c = net%(256**2)//(256**1)
    d = net%(256**1)//(256**0)
    
    #net IP decimal
    print(">>>old  - ip: ", ipmask)
    print(">>>nets - ip: ", a, b, c, d, "/", cidr)


    #ip, cidr, mask
    # for i in ip.split('.')

network2('10.10.1.32/27') #mask 255.255.255.224
network2('10.10.1.38/27')


>>>old  - ip:  10.10.1.32/27
>>>nets - ip:  10 10 1 32 / 27
>>>old  - ip:  10.10.1.38/27
>>>nets - ip:  10 10 1 32 / 27

它在 Python 中工作，现在我尝试在 DAX 中做到这一点：

cidr_net = 
// ...
VAR mask = data[mask_lan]
VAR dot1 = FIND(".", mask, 1, 0)
VAR p1   = VALUE(IF(dot1>0, (MID(mask, 1, dot1-1)), "0"))
VAR dot2 = FIND(".", mask, dot1+1, 0)
VAR p2   = VALUE(IF(dot2>0, MID(mask, dot1+1, dot2-1-dot1), "0"))
VAR dot3 = FIND(".", mask, dot2+1, 0)
VAR p3   = VALUE(IF(dot3>0, MID(mask, dot2+1, dot3-1-dot2), "0"))
VAR p4   = VALUE(IF(dot3>0, MID(mask, dot3+1, len(mask)-dot3), "0"))

VAR ip    = data[ip_lan]
VAR d1    = FIND(".", ip, 1, 0)
VAR ip1   = VALUE(IF(d1>0, (MID(ip, 1, d1-1)), "0"))
VAR d2    = FIND(".", ip, d1+1, 0)
VAR ip2   = VALUE(IF(d2>0, MID(ip, d1+1, d2-1-d1), "0"))
VAR d3    = FIND(".", ip, d2+1, 0)
VAR ip3   = VALUE(IF(d3>0, MID(ip, d2+1, d3-1-d2), "0"))
VAR ip4   = VALUE(IF(d3>0, MID(ip, d3+1, len(ip)-d3), "0"))

VAR n1    = p1 && ip1
VAR n2    = p2 && ip2
VAR n3    = p3 && ip3
VAR n4    = p4 && ip4


VAR cidr = FORMAT( 32-log( 4294967296-((((p1*256+p2)*256)+p3)*256+p4), 2), "##")
RETURN CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(n1, "."), n2), "."), n3), "."), n4), "/"), cidr)

但它不起作用：

1/ 我在 DAX 中找不到按位运算符...所以“VAR n1 = p1 && ip1”不能用作按位与。是否可以按位和另一种方式进行？

2/在 DAX 中是否有另一种方法可以更轻松地做到这一点？

3/ 否则可能会使用 PowerQuery 吗？

标签： networkingpowerbiipdaxcidr

解决方案

我实现了一个 T-SQL 版本的转换。它使用自 SQL Server 2016 起可用的 STRING_SPLIT() 函数。它非常复杂，从性能的角度来看也可能很糟糕。我不会在生产中使用它，但只是为了得到这个想法

首先我创建一个示例表

create table t( ip varchar(40) not null, mask varchar(40) not null);
insert into t(ip, mask) values ('10.0.1.38', '255.255.255.224');

然后查询

WITH CTE AS (
SELECT ip, mask, B.ipn, M.masknc
from t AS A 
CROSS APPLY ( 
SELECT (([1] * 256 + [2]) * 256 + [3]) * 256 + [4] AS ipn FROM
(SELECT CAST([value] AS BIGINT) AS value, ID = ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) 
FROM STRING_SPLIT(A.ip, '.') ) AS src
PIVOT
(MAX(value) FOR ID in ([1],[2],[3],[4]) ) AS P ) AS B
CROSS APPLY ( 
SELECT CAST(256 AS BIGINT) * 256 * 256 * 256 - ((([1] * 256 + [2]) * 256 + [3]) * 256 + [4]) AS masknc FROM
(SELECT CAST([value] AS BIGINT) AS value, ID = ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) 
FROM STRING_SPLIT(A.mask, '.') ) AS src
PIVOT
(MAX(value) FOR ID in ([1],[2],[3],[4]) ) AS P ) AS M
), CTE1 AS (
SELECT C.ip, C.mask, CAST(C.ipn / C.masknc AS BIGINT) * C.masknc AS netipn, 
32 - LOG(C.masknc, 2) AS maskc, 
E32 = CAST(256 AS BIGINT) * 256 * 256 * 256, 
E24 = CAST(256 AS BIGINT) * 256 * 256, 
E16 = CAST(256 AS BIGINT) * 256,
E8 = CAST(256 AS BIGINT)
FROM CTE AS C
), CTE2 AS (
SELECT C.ip, C.mask, netip1 = FLOOR(netipn / E24), netip2 = FLOOR((netipn % E24)/ E16),
netip3 = FLOOR((netipn % E16) / E8), netip4 = FLOOR(netipn % E8), C.maskc
FROM CTE1 AS C
)
SELECT C.ip, C.mask, CAST(C.netip1 AS VARCHAR(3)) + '.' + CAST(C.netip2 AS VARCHAR(3)) + '.' +
CAST(C.netip3 AS VARCHAR(3)) + '.' + CAST(C.netip4 AS VARCHAR(3)) + '/' + CAST(C.maskc AS VARCHAR(4)) AS netip  
FROM CTE2 AS C

这是dbfiddle.uk上此代码的链接

我认为最好的选择可能是在 Power Query 中使用 M。

推荐阅读