from logs-azure.signinlogs* metadata _id, _version, _index

// Scheduled to run every hour, reviewing events from past hour
| where
  @timestamp > now() - 1 hours
  and event.dataset == "azure.signinlogs"
  and source.ip is not null
  and azure.signinlogs.identity is not null
  and to_lower(event.outcome) == "success"

// keep relevant raw fields
| keep
  @timestamp,
  azure.signinlogs.identity,
  source.ip,
  azure.signinlogs.properties.authentication_requirement,
  azure.signinlogs.properties.app_id,
  azure.signinlogs.properties.resource_display_name,
  azure.signinlogs.properties.authentication_protocol,
  azure.signinlogs.properties.app_display_name

// case classifications for identity usage
| eval
      Esql.azure_signinlogs_properties_authentication_device_code_case = case(
      azure.signinlogs.properties.authentication_protocol == "deviceCode"
      and azure.signinlogs.properties.authentication_requirement != "multiFactorAuthentication",
      azure.signinlogs.identity,
      null),

    Esql.azure_signinlogs_auth_visual_studio_case = case(
      azure.signinlogs.properties.app_id == "aebc6443-996d-45c2-90f0-388ff96faa56"
      and azure.signinlogs.properties.resource_display_name == "Microsoft Graph",
      azure.signinlogs.identity,
      null),

    Esql.azure_signinlogs_auth_other_case = case(
      azure.signinlogs.properties.authentication_protocol != "deviceCode"
      and azure.signinlogs.properties.app_id != "aebc6443-996d-45c2-90f0-388ff96faa56",
      azure.signinlogs.identity,
      null)

// Aggregate metrics by user identity
| stats
    Esql.event_count = count(*),
    Esql.azure_signinlogs_properties_authentication_device_code_case_count_distinct = count_distinct(Esql.azure_signinlogs_properties_authentication_device_code_case),
    Esql.azure_signinlogs_properties_auth_visual_studio_count_distinct = count_distinct(Esql.azure_signinlogs_auth_visual_studio_case),
    Esql.azure_signinlogs_properties_auth_other_count_distinct = count_distinct(Esql.azure_signinlogs_auth_other_case),
    Esql.azure_signinlogs_properties_source_ip_count_distinct = count_distinct(source.ip),
    Esql.azure_signinlogs_properties_source_ip_values = values(source.ip),
    Esql.azure_signinlogs_properties_client_app_values = values(azure.signinlogs.properties.app_display_name),
    Esql.azure_signinlogs_properties_resource_display_name_values = values(azure.signinlogs.properties.resource_display_name),
    Esql.azure_signinlogs_properties_auth_requirement_values = values(azure.signinlogs.properties.authentication_requirement)
  by azure.signinlogs.identity

// Detect multiple unique IPs for one user with signs of deviceCode or VSC OAuth usage
| where
  Esql.azure_signinlogs_properties_source_ip_count_distinct >= 2
  and (
    Esql.azure_signinlogs_properties_authentication_device_code_case_count_distinct > 0
    or Esql.azure_signinlogs_properties_auth_visual_studio_count_distinct > 0
  )