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

| where kibana.alert.risk_score > 21 and
        kibana.alert.rule.name IS NOT NULL and
        host.id is not null and event.dataset is not null and

        // excluding ML and Threat Match rules as they tend to be noisy
        not kibana.alert.rule.type in ("threat_match", "machine_learning") and

        // excluding noisy tactics like Discovery, Persistence and Lateral Movement
        kibana.alert.rule.threat.tactic.name in ("Credential Access", "Defense Evasion", "Execution", "Command and Control") and

        // excluding some noisy rules
        not kibana.alert.rule.name in ("Agent Spoofing - Mismatched Agent ID", "Process Termination followed by Deletion")

// extract unique counts and values by host.id and tactic name
| stats Esql.alerts_count = COUNT(*),
        Esql.kibana_alert_rule_name_distinct_count = COUNT_DISTINCT(kibana.alert.rule.name),
        Esql.event_module_distinct_count = COUNT_DISTINCT(event.module),
        Esql.event_module_values = VALUES(event.module),
        Esql.kibana_alert_rule_name_values = VALUES(kibana.alert.rule.name),
        Esql.threat_technique_id_distinct_count = COUNT_DISTINCT(kibana.alert.rule.threat.technique.id),
        Esql.threat_technique_name_values = VALUES(kibana.alert.rule.threat.technique.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, kibana.alert.rule.threat.tactic.name

// filter for at least 3 unique rules and exclude noisy patterns like high count of alerts or processes often associated with noisy FPs
| where Esql.kibana_alert_rule_name_distinct_count >= 3 and Esql.process_entity_id_distinct_count <= 10 and Esql.alerts_count <= 20

// fields populated in the resulting alerts
| Keep host.id, kibana.alert.rule.threat.tactic.name, Esql.*