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