✄𐘗
text code block:
✄𐘗FROM metrics-*, .alerts-security.*  METADATA _index
| eval
       // hosts with more than 90% total CPU use
       cpu_metrics_host_ids = CASE(_index like ".ds-metrics-system.cpu-*" and system.cpu.total.norm.pct >= 0.9, host.id, null),
       // hosts with high severity security alerts
       alerts_host_ids = CASE(_index like ".internal.alerts-security.*" and kibana.alert.rule.name is not null and host.id is not null and kibana.alert.risk_score >= 73, host.id, null)
| stats host_with_cpu_spike = COUNT_DISTINCT(cpu_metrics_host_ids),
        host_with_alerts = COUNT_DISTINCT(alerts_host_ids),
        Esql.max_cpu_pct = MAX(system.cpu.total.norm.pct),
        Esql.unique_alerts_count = COUNT_DISTINCT(kibana.alert.rule.name),
        Esql.unique_process_count = COUNT_DISTINCT(process.entity_id),
        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 host.id
// at least 3 unique high severity alerts and from a host with 90% CPU use
| where host_with_cpu_spike > 0 and host_with_alerts > 0 and Esql.unique_alerts_count >= 3
| 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.*