from logs-* metadata _id
// Email or Elastic Defend alerts where user name is populated
| where
  (event.category == "email" and event.kind == "alert" and destination.user.name is not null) or
  (event.module == "endpoint" and event.dataset == "endpoint.alerts" and user.name is not null)

// extract target user name from email and endpoint alerts
| eval email_alert_target_user_name = CASE(event.category == "email", destination.user.name, null),
       elastic_defend_alert_user_name = CASE(event.module == "endpoint" and event.dataset == "endpoint.alerts", user.name, null)
| eval Esql.target_user_name = COALESCE(email_alert_target_user_name, elastic_defend_alert_user_name)
| where Esql.target_user_name is not null

// group by Esql.target_user_name
| stats Esql.alerts_count = COUNT(*),
        Esql.event_module_distinct_count = COUNT_DISTINCT(event.module),
        Esql.event_module_values = VALUES(event.module),
        Esql.message_values = VALUES(message),
        Esql.event_action_values = VALUES(event.action),
        Esql.process_executable_values = VALUES(process.executable),
        Esql.host_id_values = VALUES(host.id),
        Esql.source_user_name = VALUES(source.user.name),
        Esql.rule_name_values = VALUES(rule.name)
        by Esql.target_user_name
// alert when same user is observed in an endpoint and email alert
| where Esql.event_module_distinct_count >= 2
| keep Esql.alerts_count, Esql.event_module_values, Esql.host_id_values, Esql.source_user_name, Esql.target_user_name, Esql.message_values, Esql.rule_name_values, Esql.event_action_values