自定义检测规则SQL语法-云安全中心(Security Center)-阿里云帮助中心

本文详细介绍了如何在威胁分析与响应（CTDR）平台中，利用日志服务（SLS）的SQL查询能力，构建自定义检测规则，实现对休眠账户异常登录等安全事件的实时监测与告警。

背景与目标

目标场景：检测休眠账户的异常登录。

实现思路：通过定时SQL查询，对比用户“近期登录”与“历史登录基线”，找出在近期出现但历史上长期未出现的用户。

核心逻辑

检测SQL主要分为以下三个部分：

定义“近期活动”：查询最近20分钟内发生的所有登录事件。
定义“历史基线”：查询在“近期活动”之前的一个较长时间段（过去24小时内，但不包括最近20分钟）内，发生过主机登录的用户。
进行比对：将“近期活动”与“历史基线”进行关联。如果一个用户在“近期活动”中出现，但在“历史基线”中找不到任何记录，则认为该用户的本次登录是异常的。

定义“近期活动”

(
  select
    user_id,
    src_ip,
    username,
    uuid,
    start_time
  from
    log
  where
    cast(start_time as bigint) >= cast(to_unixtime (current_timestamp) as bigint) - 20 * 60
    and cast(start_time as bigint) < cast(to_unixtime (current_timestamp) as bigint)
) a

语法解析：
- from log：从 log 表中查询数据。
- to_unixtime(current_timestamp)：获取当前时间的Unix时间戳（秒）。
- cast(... as bigint)：将时间戳转换为长整型数字，便于进行数学运算和比较。
- where ...：定义了一个 20分钟的滑动时间窗口，从当前时间点向前追溯。
  - >= ... - 20 * 60：日志的开始时间（start_time）必须大于等于“当前时间 - 20分钟”。
  - < ...：日志的开始时间必须小于“当前时间”。
语义解析：
- 目的： 定义一个名为 a 的“近期活动”集合，用于圈定分析的主要对象。
- 结果： 一个临时结果集，其中包含了在最近20分钟内有过活动的用户及其ID (user_id)、源IP (src_ip)等关键信息。
- 关键点： “近期”被精确定义为从当前时间点向前追溯的 20分钟 时间窗口。

定义“历史基线”

(
  select
    user_id,
    username,
    uuid
  from
    log
  where
    schema='HOST_LOGIN_ACTIVITY' and
    cast(start_time as bigint) >= cast(to_unixtime (current_timestamp) as bigint) - 24 * 3600
    and cast(start_time as bigint) < cast(to_unixtime (current_timestamp) as bigint) - 20 * 60
) b

语法解析：
- schema='HOST_LOGIN_ACTIVITY'：明确指定只查询主机登录活动相关的日志，使历史基线更精确。
- where ...：子句定义了一个从24小时前到20分钟前的时间区间。
  - >= ... - 24 * 3600：日志开始时间需大于等于“当前时间 - 24小时”。
  - < ... - 20 * 60：日志开始时间需小于“当前时间 - 20分钟”。
语义解析：
- 目的： 构建一个名为 b 的“历史基线”集合，作为判断近期活动是否异常的参照物。
- 结果： 一个临时结果集，包含在过去一天内（但不包括最近20分钟）有过登录活动的用户。
- 关键点： 该时间窗口被设定为 从24小时前到20分钟前，这确保了历史基线与近期活动数据 没有重叠，避免了自我比较的逻辑错误。

利用 `LEFT JOIN` 进行差异对比

... a
left join
... b on a.username = b.username and a.uuid = b.uuid and a.user_id = b.user_id

语法解析：
- LEFT JOIN：以左表（a，近期活跃用户）为基准，将其每一条记录与右表（b，历史活跃用户）进行匹配。
- on a.username = b.username and a.uuid = b.uuid and a.user_id=b.user_id：指定了关联条件，通过username, uuid, user_id三个字段来唯一识别一个用户实体，确保匹配的精确性。
语义解析：
- 目的： 将“近期活动”(a)与“历史基线”(b)进行关联，尝试为每个近期活动的用户找到其对应的历史登录记录。
- 结果： 生成一个组合结果集，其中包含了a表的全部记录，以及b表中能通过用户身份字段成功匹配上的记录。
- 关键点： 利用 LEFT JOIN 的不对等特性：如果a表中的用户在b表中 不存在，关联后，所有属于b表的字段值将为 NULL。这是识别“新出现”行为的核心机制。

筛选最终结果

where
  (
    b.username is null
    or b.username = ''
  )

语法解析：
- b.username is null：利用 LEFT JOIN 特性的关键。当近期登录的用户（在 a 表中）在历史基线（b 表）中找不到任何登录记录时，b.username 就会是 NULL。
- or b.username = ''：这是一个防御性条件，用于处理某些情况下日志字段可能为空字符串''而不是NULL的场景。
语义解析：
- 目的： 从关联后的结果中，精确筛选出那些“仅在近期出现，但在历史基线中无记录”的异常活动。
- 结果： 经过此步过滤，结果集中仅保留那些 LEFT JOIN 中未能匹配上的记录，即真正关心的异常事件。
- 关键点： b.username is null 是整个检测逻辑的 核心决策点。它利用上一步产生的 NULL 值，将“近期有、历史无”的记录从海量数据中分离出来。

`SELECT DISTINCT`：输出告警

select distinct
     a.user_id,
     a.src_ip,
     a.username,
     a.uuid

语法解析：
- SELECT DISTINCT：选择并输出指定的字段，并对结果进行去重，确保对于同一次异常登录事件只产生一条告警。
语义解析：
- 目的： 整理并输出最终的、可直接用于告警的异常事件列表。
- 结果： 一个清晰、去重的告警清单，每条记录都包含定位异常事件所需的核心溯源信息（如用户ID、源IP等）。
- 关键点： DISTINCT 的使用至关重要。它能对结果进行去重，确保在检测窗口内（20分钟），同一个用户的同一类异常行为只触发 一次告警。

完整解决方案

最终SQL查询语句

*|set session mode=scan;
select distinct
     a.user_id,
     a.src_ip,
     a.username,
     a.uuid
   from
     (
       select
         user_id,
         src_ip,
         username,
         uuid,
         start_time
       from
         log
       where
         cast(start_time as bigint) >= cast(to_unixtime (current_timestamp) as bigint) -20 * 60
         and cast(start_time as bigint) < cast(to_unixtime (current_timestamp) as bigint)
     ) a
     left join (
       select
         user_id,
         username,
         uuid
       from
         log
       where
         schema='HOST_LOGIN_ACTIVITY' and
         cast(start_time as bigint) >= cast(to_unixtime (current_timestamp) as bigint) - 24 * 3600
         and cast(start_time as bigint) < cast(to_unixtime (current_timestamp) as bigint) -20 * 60
     ) b on a.username = b.username
     and a.uuid = b.uuid
     and a.user_id=b.user_id
   where
     (
       b.username is null
       or b.username = ''
     )

在威胁分析与响应中配置规则

购买并开通威胁分析与响应
请参考购买并开通威胁分析与响应文档进行选购。建议同时选购日志接入流量和日志存储容量，以享受更完成的自定义威胁检测服务。
登录控制台并进入创建自定义规则页面
1. 登录云安全中心控制台。
2. 在左侧导航栏，选择威胁分析与响应 > 规则管理。在控制台左上角，选择需防护资产所在的区域：中国或全球（不含中国）。
3. 在自定义页签，单击新增自定义规则。

配置告警生成规则

在创建自定义规则面板，基础信息页签输入规则名称和描述后单击下一步，进入告警生成设置页面。

配置SQL检测规则，配置参数可参考如下：

配置项	配置值
规则体	SQL
日志范围	登录日志-主机登录成功日志。
SQL查询语句	复制SQL语法中的代码即可。
调度间隔	固定间隔 - 20分钟。
SQL时间窗口	24小时。
起始时间	规则启用时。
生成结构	其他告警日志。
告警类型	异常登录。
告警等级	中危。
ATT&CK阶段	持久化 - T1136 Create Account。
实体映射	网络地址 `is_malware`： 1 `ip`： `$src_ip` `net_connect_dir`： in 主机 `is_asset`： 1 `uuid`： `$uuid`

配置事件生成规则
1. 在告警生成设置页面，配置完成后单击下一步，进入事件生成设置页面。
2. 配置时间规则，配置参数可参考如下：
  - 生成事件：是。
  - 事件生成方式：同类聚合。
  - 聚合窗口：20分钟。
验证规则
新创建的规则为未启用状态，可对其进行测试，评估告警效果。测试时系统会自动校准告警字段，请参考生成的校准建议，优化规则SQL或剧本，确保正式启用后告警的准确性与规范性。
1. 将目标规则启用状态修改为测试中。
2. 在目标规则操作列，单击查看告警测试结果。
3. 在测试结果详情页，查看测试产生的告警趋势图、告警列表。
4. 单击目标告警操作列的详情，查看告警的校准结果。
启用自定义规则
测试通过后需在自定义规则启用状态列操作列修改状态为启用。
重要
建议参考下文，对规则进行测试后再启用。

测试结果示例

风险提示

误报：长时间休假或出差后首次登录的正常用户，可能被误报。可通过延长dormant_hours阈值（如72小时）或配置用户白名单来减少误报。
漏报：日志采集中断或日志字段格式不规范，可能导致SQL无法正确计算时间间隔，造成漏报。需保障日志数据的完整性和一致性。

SQL语法相关文档

关于SQL语法的更多信息，请参见SQL分析语法与功能。
也可以使用日志服务Copilot工具，辅助您生成并优化SQL查询语句。更多信息，请参见通过AI智能生成查询与分析语句（Copilot）。
如何配置威胁检测规则，请参见配置威胁检测规则。