from .alerts-security.*  metadata _id

// filter for alerts with populated risk score, excluding threat_match rules, deprecated and some other noisy ones.
| where kibana.alert.risk_score > 0 and
        kibana.alert.rule.name IS NOT NULL and
        host.id is not null and event.dataset is not null and
        kibana.alert.rule.type != "threat_match" and
        not kibana.alert.rule.name in ("Agent Spoofing - Mismatched Agent ID") and
        not kibana.alert.rule.name like "Deprecated - *"

// 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.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_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.kibana_alert_risk_score_sum = sum(kibana.alert.risk_score),
        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

// divide the sum of risk scores by the total number of alerts
| eval Esql.risk_alerts_count_ratio = Esql.kibana_alert_risk_score_sum/Esql.alerts_count

// filter for risky hosts by risk score and unique count of rules and tactics
| where Esql.kibana_alert_rule_name_distinct_count >= 5 and Esql.threat_tactic_id_distinct_count >= 3 and Esql.threat_tactic_id_distinct_count >= 3 and Esql.alerts_count <= 500 and Esql.risk_alerts_count_ratio >= 50

// fiels populated in the resulting alert
| keep host.id,
       Esql.alerts_count,
       Esql.kibana_alert_risk_score_sum,
       Esql.risk_alerts_count_ratio,
       Esql.kibana_alert_rule_name_distinct_count,
       Esql.event_module_values,
       Esql.kibana_alert_rule_name_values,
       Esql.threat_tactic_name_values,
       Esql.process_executable_values,
       Esql.process_parent_executable_values,
       Esql.process_command_line_values