✄𐘗
text code block:
✄𐘗from .alerts-security.*  metadata _id

// filter for medium-or-higher severity alerts, excluding threat_match, machine_learning, and deprecated rules.
| where kibana.alert.risk_score > 21 and
        kibana.alert.rule.name IS NOT NULL and kibana.alert.rule.rule_id IS NOT NULL and
        host.id is not null and event.dataset is not null and
        kibana.alert.rule.type not in ("threat_match", "machine_learning") and
        // Exclude a deprecated rule whose alert name does not carry the standard prefix
        kibana.alert.rule.name != "Potential PrintNightmare File Modification" and
        not kibana.alert.rule.name like "Deprecated - *" and
        not KQL("""kibana.alert.rule.tags : "Rule Type: Higher-Order Rule" """)

// extract unique counts and values by host.id
| stats Esql.alerts_count = COUNT(*),
        Esql.kibana_alert_rule_name_distinct_count = COUNT_DISTINCT(kibana.alert.rule.name),
        Esql.kibana_alert_rule_id_distinct_count = COUNT_DISTINCT(kibana.alert.rule.rule_id),
        Esql.event_module_values = VALUES(event.module),
        Esql.host_name_values = VALUES(host.name),
        Esql.kibana_alert_rule_name_values = VALUES(kibana.alert.rule.name),
        Esql.kibana_alert_rule_id_values = VALUES(kibana.alert.rule.rule_id),
        Esql.threat_tactic_id_distinct_count = COUNT_DISTINCT(kibana.alert.rule.threat.tactic.id),
        Esql.threat_tactic_name_values = VALUES(kibana.alert.rule.threat.tactic.name),
        Esql.process_executable_values = VALUES(process.executable),
        Esql.process_parent_executable_values = VALUES(process.parent.executable),
        Esql.process_command_line_values = VALUES(process.command_line),
        Esql.process_entity_id_distinct_count = COUNT_DISTINCT(process.entity_id) by host.id

// filter for risky hosts with multiple distinct rules across multiple tactics
// Distinct rule IDs prevent one rule mapped to multiple tactics from satisfying the correlation.
| where Esql.kibana_alert_rule_name_distinct_count >= 2 and
        Esql.kibana_alert_rule_id_distinct_count >= 2 and
        Esql.threat_tactic_id_distinct_count >= 3

// Populate the native host name for alert triage without changing the host.id correlation key.
| eval host.name = MV_FIRST(Esql.host_name_values)

// fields populated in the resulting alert
| keep host.id,
       host.name,
       Esql.alerts_count,
       Esql.kibana_alert_rule_name_distinct_count,
       Esql.kibana_alert_rule_id_distinct_count,
       Esql.process_entity_id_distinct_count,
       Esql.event_module_values,
       Esql.host_name_values,
       Esql.kibana_alert_rule_name_values,
       Esql.kibana_alert_rule_id_values,
       Esql.threat_tactic_name_values,
       Esql.process_executable_values,
       Esql.process_parent_executable_values,
       Esql.process_command_line_values