如何用SQL过滤CEF日志并提取origin、dst字段至新列

针对从单列CEF日志中过滤并提取指定IP字段的需求，以下是主流SQL数据库的实现方案：

1. MySQL/MariaDB #

过滤+提取SQL语句 #

SELECT
  raw,
  REGEXP_SUBSTR(raw, 'origin=([0-9.]+)', 1, 1, 'c', 1) AS origin_ip,
  REGEXP_SUBSTR(raw, 'dst=([0-9.]+)', 1, 1, 'c', 1) AS dest_ip
FROM your_table
WHERE
  raw REGEXP 'origin=30\\.20\\.0\\.10'
  AND raw REGEXP 'dst=1\\.1\\.1\\.1';

说明 #

• REGEXP_SUBSTR的第6个参数1表示提取正则表达式中第一个捕获组的内容（即括号内的IP部分）
• 正则里用[0-9.]+匹配IP格式字符串，转义\.避免匹配任意字符
• 过滤条件用REGEXP精准匹配指定的origin和dst值

2. PostgreSQL #

过滤+提取SQL语句 #

SELECT
  raw,
  (regexp_match(raw, 'origin=([0-9.]+)'))[1] AS origin_ip,
  (regexp_match(raw, 'dst=([0-9.]+)'))[1] AS dest_ip
FROM your_table
WHERE
  raw ~ 'origin=30\.20\.0\.10'
  AND raw ~ 'dst=1\.1\.1\.1';

说明 #

• regexp_match返回捕获组的数组，取第一个元素[1]得到IP值
• ~是PostgreSQL的正则匹配操作符，功能等价于REGEXP

3. SQL Server（2017及以上版本） #

过滤+提取SQL语句 #

SELECT
  raw,
  REGEXP_SUBSTRING(raw, 'origin=([0-9.]+)', 1, 1, 0, 1) AS origin_ip,
  REGEXP_SUBSTRING(raw, 'dst=([0-9.]+)', 1, 1, 0, 1) AS dest_ip
FROM your_table
WHERE
  raw LIKE '%origin=30.20.0.10%'
  AND raw LIKE '%dst=1.1.1.1%';

兼容低版本SQL Server的方案（无REGEXP_SUBSTRING） #

SELECT
  raw,
  SUBSTRING(
    raw,
    CHARINDEX('origin=', raw) + 7,
    ISNULL(CHARINDEX(' ', raw, CHARINDEX('origin=', raw)), LEN(raw)+1) - CHARINDEX('origin=', raw) - 7
  ) AS origin_ip,
  SUBSTRING(
    raw,
    CHARINDEX('dst=', raw) + 4,
    ISNULL(CHARINDEX(' ', raw, CHARINDEX('dst=', raw)), LEN(raw)+1) - CHARINDEX('dst=', raw) - 4
  ) AS dest_ip
FROM your_table
WHERE
  raw LIKE '%origin=30.20.0.10%'
  AND raw LIKE '%dst=1.1.1.1%';

说明 #

• 低版本方案通过CHARINDEX定位键值对的起始和结束位置，用SUBSTRING截取IP；ISNULL处理字段位于日志末尾无后续空格的情况
• 若日志中存在值包含空格的特殊键值对，需调整正则或定位逻辑

通用注意事项 #

• CEF日志的键值对通常以空格分隔，若值中包含空格，可将正则匹配规则改为origin=([^=]+?)(?=\s\w+=|$)，确保精准截取到下一个键的起始位置或日志末尾

内容的提问来源于stack exchange，提问作者Eps Geofs Diivs