✄𐘗
text code block:
✄𐘗FROM metrics-*, .alerts-security.* METADATA _index
| eval
       // processes with more than 70% total CPU use
       cpu_metrics_pids = CASE(_index like ".ds-metrics-system.process-*" and system.process.cpu.total.norm.pct >= 0.7, process.pid, null),
       // any security alert with process.name and ID populated excluding low severity ones
       alerts_pids = CASE(_index like ".internal.alerts-security.*" and kibana.alert.rule.name is not null and process.name is not null and process.pid is not null and host.id is not null and kibana.alert.risk_score > 21, process.pid, null)
| stats pid_with_cpu_spike = COUNT_DISTINCT(cpu_metrics_pids), pid_with_alerts = COUNT_DISTINCT(alerts_pids),
        Esql.max_cpu_pct = MAX(system.process.cpu.total.norm.pct),
        Esql.alerts = VALUES(kibana.alert.rule.name),
        Esql.process_hash_sha256 = VALUES(process.hash.sha256),
        process_path = VALUES(process.executable),
        parent_process_path = VALUES(process.parent.executable),
        user_name = VALUES(user.name),
        cmdline = VALUES(process.command_line) by process.pid, process.name, host.id
| where pid_with_cpu_spike > 0 and pid_with_alerts > 0
// populate fields to use in rule exceptions
| eval process.hash.sha256 = MV_FIRST(Esql.process_hash_sha256),
       process.executable = MV_FIRST(process_path),
       process.parent.executable = MV_FIRST(parent_process_path),
       process.command_line = MV_FIRST(cmdline),
       user.name = MV_FIRST(user_name)
| KEEP user.name, host.id, process.*, Esql.*