攻击调查实战

-- 查看攻击源 IP 的所有外部连接
SELECT sourceip, destinationip, destinationport, 
       SUM(sourcebytes) as total_bytes,
       COUNT(*) as connection_count
FROM flows
WHERE sourceip = '203.0.113.50'
  AND destinationip NOT IN REFERENCESET('Internal_Networks')
GROUP BY sourceip, destinationip, destinationport
ORDER BY total_bytes DESC
LAST 24 HOURS

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Offense #12345 调查记录
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 
[10:15] 收到 Offense，Magnitude 8，疑似恶意软件通信
        - Source IP: 192.168.1.100
        - Category: Malware
        - Event Count: 45 (持续增长)
 
[10:20] 调查 Source IP
        - 资产: 财务部门工作站 WS-FIN-03
        - OS: Windows 10
        - 漏洞: CVE-2023-XXXX (未修补)
        - 该主机存在 3 个 High 漏洞
 
[10:30] 查看 Events 时间线
        - 10:00 首次检测到可疑 DNS 查询
        - 10:05 开始与外部 IP 通信
        - 10:10 大量数据上传 (5GB)
        - 10:15 规则触发，生成 Offense
 
[10:35] Network Activity 分析
        - 发现与已知 C2 IP 通信
        - 上传方向流量异常
        - 端口: 443 (伪装 HTTPS)
 
[10:45] 联系终端安全团队
        - 已远程隔离该主机
        - 开始磁盘镜像取证
 
[11:00] 初步结论
        - 确认感染: TrickBot 变种
        - 外泄数据: 客户数据库 (约 5GB)
        - 感染途径: 钓鱼邮件附件
 
[下一步]
        - 扩大搜索: 检查是否有其他主机感染
        - 通知法务/合规部门
        - 启动事件响应流程
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- 1. 查看特定 IP 在过去 7 天的所有事件
SELECT DATEFORMAT(starttime,'YYYY-MM-dd HH:mm') as time,
       QIDNAME(qid) as event_name,
       username,
       sourceip,
       destinationip,
       magnitude
FROM events
WHERE sourceip = '192.168.1.100' 
   OR destinationip = '192.168.1.100'
ORDER BY starttime
LAST 7 DAYS
 
-- 2. 统计某用户的登录活动
SELECT username, 
       QIDNAME(qid),
       COUNT(*) as count,
       MIN(starttime) as first_seen,
       MAX(starttime) as last_seen
FROM events
WHERE username = 'zhangsan'
  AND CATEGORYNAME(category) = 'Authentication'
GROUP BY username, QIDNAME(qid)
LAST 24 HOURS
 
-- 3. 发现外部通信异常
SELECT sourceip,
       destinationip,
       destinationport,
       SUM(sourcebytes) as upload_bytes,
       COUNT(*) as connections
FROM flows
WHERE destinationip NOT IN REFERENCESET('Internal_Networks')
  AND sourceip IN (SELECT sourceip FROM events WHERE offenseid = 12345)
GROUP BY sourceip, destinationip, destinationport
HAVING upload_bytes > 104857600  -- > 100MB
ORDER BY upload_bytes DESC
LAST 24 HOURS
 
-- 4. 检查账号共享
SELECT username,
       COUNT(DISTINCT sourceip) as unique_ips,
       ARRAY_AGG(DISTINCT sourceip) as ip_list
FROM events
WHERE CATEGORYNAME(category) = 'Authentication'
  AND QIDNAME(qid) = 'Successful Login'
GROUP BY username
HAVING unique_ips > 3
LAST 24 HOURS