✄𐘗
text code block:
✄𐘗FROM logs-aws.cloudtrail-*
| WHERE event.dataset == "aws.cloudtrail"
  AND event.outcome == "success"
  AND source.as.organization.name IS NOT NULL
  AND user.name IS NOT NULL

| EVAL is_trusted_cloud = CASE(
    source.as.organization.name LIKE "Amazon*" OR
    source.as.organization.name == "Google LLC" OR
    source.as.organization.name == "Microsoft Corporation" OR
	source.as.organization.name == "MongoDB, Inc.",
    true, false
  )

| EVAL is_suspicious_action = CASE(
    event.action IN (
      "GetCallerIdentity", "GetAccountSummary", "ListAccountAliases",
      "GetSecretValue", "ListSecrets", "DescribeSecret",
      "GetParameter", "GetParameters", "GetParametersByPath",
      "AssumeRole", "AssumeRoleWithWebIdentity", "AssumeRoleWithSAML",
      "AttachUserPolicy", "AttachRolePolicy",
      "PutUserPolicy", "PutRolePolicy",
      "CreateAccessKey", "UpdateAccessKey",
      "CreateUser", "CreateLoginProfile",
      "UpdateLoginProfile", "AddUserToGroup",
      "GetObject", "ListBuckets", "ListObjects", "ListObjectsV2",
      "InvokeModel", "InvokeModelWithResponseStream", "Converse"
    ), true, false
  )

// Single aggregation — full event count preserved for ratio logic
// suspicious action tracking is additive on top
| STATS
    Esql.total_events_all_asns = COUNT(*),
    Esql.count_distinct_asns = COUNT_DISTINCT(source.as.organization.name),
    Esql.src_asn_values = VALUES(source.as.organization.name),
	Esql.user_agent_values = VALUES(user_agent.original),
	Esql.related_users = VALUES(user.changes.name),
	Esql.source_ip_values = VALUES(source.address),
    Esql.has_trusted_cloud_asn = MAX(is_trusted_cloud),
    Esql.trusted_cloud_event_count = SUM(CASE(is_trusted_cloud == true, 1, 0)),
    Esql.untrusted_event_count = SUM(CASE(is_trusted_cloud == false, 1, 0)),
    // Suspicious action visibility from untrusted ASNs — informational only, not a filter
    Esql.untrusted_suspicious_count  = SUM(CASE(
        is_trusted_cloud == false AND is_suspicious_action == true, 1, 0
      )),
    Esql.untrusted_suspicious_actions = VALUES(CASE(
        is_trusted_cloud == false AND is_suspicious_action == true,
        event.action, null
      )),
    Esql.most_recent_low_asn_day   = MAX(CASE(
        is_trusted_cloud == false, @timestamp, null
      ))
    BY user.name, aws.cloudtrail.user_identity.type

| EVAL Esql.rare_asn_ratio = TO_DOUBLE(Esql.untrusted_event_count) / TO_DOUBLE(Esql.total_events_all_asns), 
       Esql.unique_action_from_untrusted_asn = MV_COUNT(Esql.untrusted_suspicious_actions)

// Detection thresholds — unchanged, full event counts drive the logic
| WHERE Esql.has_trusted_cloud_asn == true
  AND Esql.untrusted_event_count >= 1
  AND Esql.trusted_cloud_event_count >= 100
  AND Esql.rare_asn_ratio <= 0.01
  AND Esql.unique_action_from_untrusted_asn >= 2 
  AND Esql.count_distinct_asns <= 5
  AND Esql.most_recent_low_asn_day >= NOW() - 1 hour

| KEEP user.name,
       aws.cloudtrail.user_identity.type,
       Esql.*