from logs-aws.cloudtrail* metadata _id, _version, _index
| where @timestamp > now() - 30 minutes
  and event.dataset == "aws.cloudtrail"
  and aws.cloudtrail.user_identity.arn is not null
  and aws.cloudtrail.user_identity.type == "IAMUser"
  and source.ip is not null
  and not (
    user_agent.original like "%Terraform%" or
    user_agent.original like "%Ansible%" or
    user_agent.original like "%Pulumni%"
  )
  and `source.as.organization.name` != "AMAZON-AES"
  and event.provider not in (
    "health.amazonaws.com", "monitoring.amazonaws.com", "notifications.amazonaws.com",
    "ce.amazonaws.com", "cost-optimization-hub.amazonaws.com",
    "servicecatalog-appregistry.amazonaws.com", "securityhub.amazonaws.com"
  )

| eval
  Esql.time_window_date_trunc = date_trunc(30 minutes, @timestamp),
  Esql.aws_cloudtrail_user_identity_arn = aws.cloudtrail.user_identity.arn,
  Esql.aws_cloudtrail_user_identity_access_key_id = aws.cloudtrail.user_identity.access_key_id,
  Esql.source_ip = source.ip,
  Esql.user_agent_original = user_agent.original,
  Esql.source_ip_string = to_string(source.ip),
  Esql.source_ip_user_agent_pair = concat(Esql.source_ip_string, " - ", user_agent.original),
  Esql.source_ip_city_pair = concat(Esql.source_ip_string, " - ", source.geo.city_name),
  Esql.source_geo_city_name = source.geo.city_name,
  Esql.event_timestamp = @timestamp,
  Esql.source_network_org_name = `source.as.organization.name`

| stats
  Esql.event_action_values = values(event.action),
  Esql.event_provider_values = values(event.provider),
  Esql.aws_cloudtrail_user_identity_access_key_id_values = values(Esql.aws_cloudtrail_user_identity_access_key_id),
  Esql.aws_cloudtrail_user_identity_arn_values = values(Esql.aws_cloudtrail_user_identity_arn),
  Esql.source_ip_values = values(Esql.source_ip),
  Esql.user_agent_original_values = values(Esql.user_agent_original),
  Esql.source_ip_user_agent_pair_values = values(Esql.source_ip_user_agent_pair),
  Esql.source_geo_city_name_values = values(Esql.source_geo_city_name),
  Esql.source_ip_city_pair_values = values(Esql.source_ip_city_pair),
  Esql.source_network_org_name_values = values(Esql.source_network_org_name),
  Esql.source_ip_count_distinct = count_distinct(Esql.source_ip),
  Esql.user_agent_original_count_distinct = count_distinct(Esql.user_agent_original),
  Esql.source_geo_city_name_count_distinct = count_distinct(Esql.source_geo_city_name),
  Esql.source_network_org_name_count_distinct = count_distinct(Esql.source_network_org_name),
  Esql.timestamp_first_seen = min(Esql.event_timestamp),
  Esql.timestamp_last_seen = max(Esql.event_timestamp),
  Esql.event_count = count()
  by Esql.time_window_date_trunc, Esql.aws_cloudtrail_user_identity_access_key_id

| eval
  Esql.activity_type = case(
    Esql.source_ip_count_distinct >= 2 and Esql.source_network_org_name_count_distinct >= 2 and Esql.source_geo_city_name_count_distinct >= 2 and Esql.user_agent_original_count_distinct >= 2, "multiple_ip_network_city_user_agent",
    Esql.source_ip_count_distinct >= 2 and Esql.source_network_org_name_count_distinct >= 2 and Esql.source_geo_city_name_count_distinct >= 2, "multiple_ip_network_city",
    Esql.source_ip_count_distinct >= 2 and Esql.source_geo_city_name_count_distinct >= 2, "multiple_ip_and_city",
    Esql.source_ip_count_distinct >= 2 and Esql.source_network_org_name_count_distinct >= 2, "multiple_ip_and_network",
    Esql.source_ip_count_distinct >= 2 and Esql.user_agent_original_count_distinct >= 2, "multiple_ip_and_user_agent",
    "normal_activity"
  ),
  Esql.activity_fidelity_score = case(
    Esql.activity_type == "multiple_ip_network_city_user_agent", "high",
    Esql.activity_type == "multiple_ip_network_city", "high",
    Esql.activity_type == "multiple_ip_and_city", "medium",
    Esql.activity_type == "multiple_ip_and_network", "medium",
    Esql.activity_type == "multiple_ip_and_user_agent", "low"
  )

| keep
  Esql.time_window_date_trunc,
  Esql.activity_type,
  Esql.activity_fidelity_score,
  Esql.event_count,
  Esql.timestamp_first_seen,
  Esql.timestamp_last_seen,
  Esql.aws_cloudtrail_user_identity_arn_values,
  Esql.aws_cloudtrail_user_identity_access_key_id_values,
  Esql.event_action_values,
  Esql.event_provider_values,
  Esql.source_ip_values,
  Esql.user_agent_original_values,
  Esql.source_ip_user_agent_pair_values,
  Esql.source_geo_city_name_values,
  Esql.source_ip_city_pair_values,
  Esql.source_network_org_name_values,
  Esql.source_ip_count_distinct,
  Esql.user_agent_original_count_distinct,
  Esql.source_geo_city_name_count_distinct,
  Esql.source_network_org_name_count_distinct

| where Esql.activity_type != "normal_activity"