PowerBI案例之查找无效的IP地址

IP地址作为网络数据分析中的关键字段，其有效性直接影响到基于地理位置、访问行为等分析的可信度。本篇文章将介绍如何使用PowerBI来识别和标记无效IP地址。

问题描述

为了解网络攻击情况，现在需要查找出无效的IP地址，请自行建模完成计算。

已知：IP地址由4个0-255的数值组成，即由4个8位字节的数值组成，然后中间用点号隔开，例如：127.0.0.1

无效的IP地址具有如下的特征：

• 任何 8 位字节中包含大于 255 的数字
• 任何 8 位字节中含有前导零（如 01.02.03.04）
• 少于或多于 4 个 8 位字节

具体问题如下图所示：

本案例的初始数据如下：

日志表：

IP	Log ID
192.168.1.1	1
256.1.2.3	2
192.168.0.1	3
192.168.1	4
255.1.02.0	5
127.00.0.1	6
127.0.0.1	7

若需其它辅助表等，可自行创建并建模。

解题要点

IP地址是字符串，因此在判断每个8位字节的数值是否满足要求前，需要先提取出所有的8位字节数值。另外就是要注意，在判断前导零时，不能将8位字节的内容转成数值，否则将会丢失前导零的内容。

提取数值的方法主要有两种：

1、使用FIND函数找出所有分隔符的位置，形成区间，然后再使用MID函数来提取。

2、将分隔符替换成"|"，将其转变成PATH系列函数能识别的层级结构路径，然后借助PATH系列函数来提取。

解决方案

首先，数据模型如下图所示：

然后，创建如下度量值，这里给出两种方法：

IP状态1 = 
VAR CurText = MAX('日志表'[IP])
VAR SplitPositions = 
    UNION(
        SELECTCOLUMNS({0},"Position",[Value]),
        DISTINCT(FILTER(SELECTCOLUMNS(GENERATESERIES(1,LEN(CurText)),"Position",FIND(".",CurText,[Value],BLANK())),[Position]<>BLANK())),
        {LEN(CurText)+1}
    )
VAR SplitPosition_AddIndex = ADDCOLUMNS(SplitPositions,"Index",RANKX(SplitPositions,[Position],,1))
VAR NumList = 
    SELECTCOLUMNS(
        GENERATESERIES(1,COUNTROWS(SplitPositions)-1),
        "Num",
            VAR Start_ = SUMMARIZE(FILTER(SplitPosition_AddIndex,[Index]=[Value]),[Position])+1
            VAR End_ = SUMMARIZE(FILTER(SplitPosition_AddIndex,[Index]=[Value]+1),[Position])
            RETURN
            MID(CurText,Start_,End_-Start_)
    )
VAR Condition_1 = NOT ISEMPTY(FILTER(NumList,IFERROR([Num]*1>255 || [Num]*1<0,1)))
VAR Condition_2 = NOT ISEMPTY(FILTER(NumList,LEFT([Num],1)="0" && LEN([Num])<>1))
VAR Condition_3 = COUNTROWS(NumList)<>4
RETURN
IF(Condition_1 || Condition_2 || Condition_3,"无效","有效")

IP状态2 = 
VAR CurText = MAX('日志表'[IP])
VAR PathText = SUBSTITUTE(CurText,".","|")
VAR NumList = 
    SELECTCOLUMNS(
        GENERATESERIES(1,PATHLENGTH(PathText)),
        "Num",PATHITEM(PathText,[Value])
    )
VAR Condition_1 = NOT ISEMPTY(FILTER(NumList,IFERROR([Num]*1>255 || [Num]*1<0,1)))
VAR Condition_2 = NOT ISEMPTY(FILTER(NumList,LEFT([Num],1)="0" && LEN([Num])<>1))
VAR Condition_3 = COUNTROWS(NumList)<>4
RETURN
IF(Condition_1 || Condition_2 || Condition_3,"无效","有效")

然后创建一个矩阵，并将IP字段作为行标签，再将上面的度量值放入矩阵的值字段即可，如下图所示：

总结

以上方法仅供参考，若有更优雅的解决方案，欢迎留言讨论，或者加入我们的技术交流群，一起享受这种思维碰撞的快乐吧！

PBI/DAX技术交流群(QQ)：344353627