如何用Splunk查询特定IP对应同路径的异IP历史请求

原始日志数据 #

timestamp     ip      path
1668603956000 1.1.1.1 /some/path
1668603955000 2.2.2.2 /some/path
1668603954000 2.2.2.2 /some/other/path
1668603953000 3.3.3.3 /some/path
1668603952000 3.3.3.3 /some/other/path
1668603951000 4.4.4.4 /some/path
1668603950000 5.5.5.5 /some/path

需求 #

针对ip=2.2.2.2的请求，生成表格展示同路径下、时间更早的来自其他IP的请求，预期结果示例如下：

L.time           R.time           R.ip       R.path
1668603953000    1668603955000    3.3.3.3    /some/path
1668603952000    1668603954000    3.3.3.3    /some/other/path
1668603951000    1668603955000    4.4.4.4    /some/path
1668603950000    1668603955000    5.5.5.5    /some/path

失败的尝试查询 #

以下查询未生效，仅返回2.2.2.2的原始事件，无关联结果：

source=my_log
| where ip = "2.2.2.2"
| table path, ip, _time
| join type=inner left=L right=R usetime=true earlier=true where L.path = R.path [
    | where L.ip != R.ip
]
| table L._time, R._time, R.ip, R.path

解决方案 #

问题出在join子查询的写法：子查询内无法直接引用主查询的L/R别名，且未正确限定子查询的数据源。正确的查询逻辑是先获取2.2.2.2的请求作为左表，再关联所有其他IP的历史请求（同路径、更早时间）：

source=my_log
| where ip = "2.2.2.2"
| rename _time as L_time, ip as L_ip, path as L_path
| join type=inner L_path [
    source=my_log
    | where ip != "2.2.2.2"
    | rename _time as R_time, ip as R_ip, path as R_path
    | where R_time < L_time
]
| table L_time, R_time, R_ip, R_path
| rename L_time as "L.time", R_time as "R.time", R_ip as "R.ip", R_path as "R.path"

关键修正点 #

1. 主查询先重命名字段，明确区分左表（L）的属性
2. join子查询重新搜索全量日志，过滤掉2.2.2.2的IP，并重命名右表（R）字段
3. 子查询内添加时间条件R_time < L_time，确保只匹配之前的请求
4. 最后通过rename调整表头，匹配预期格式

内容的提问来源于stack exchange，提问作者Josh M.