✄𐘗
text code block:
✄𐘗FROM logs-azure.signinlogs-*, logs-azure.auditlogs-* METADATA _id, _version, _index
| WHERE
    (
      azure.signinlogs.result_signature == "SUCCESS" AND
      azure.signinlogs.properties.user_type == "Member" AND
      azure.signinlogs.properties.user_id IS NOT NULL AND
      azure.signinlogs.properties.cross_tenant_access_type == "none" AND
      (
        azure.signinlogs.properties.device_detail.device_id IS NULL OR
        azure.signinlogs.properties.device_detail.device_id == ""
      )
    ) OR
    (
      azure.auditlogs.operation_name IN ("Register device", "Add registered owner to device", "Add device") AND
      event.outcome == "success" AND
      azure.auditlogs.properties.initiated_by.user.id IS NOT NULL
    )
| EVAL
    Esql.sequence_stage = CASE(data_stream.dataset == "azure.signinlogs", "signin", "register"),
    Esql.actor_user_id = COALESCE(azure.signinlogs.properties.user_id, azure.auditlogs.properties.initiated_by.user.id),
    Esql.auth_method = FIELD_EXTRACT(azure.signinlogs.properties.authentication_details, "authentication_method"),
    Esql.auth_step_time = MV_MIN(TO_DATETIME(FIELD_EXTRACT(azure.signinlogs.properties.authentication_details, "authentication_step_date_time"))),
    Esql.register_candidate = CASE(
      data_stream.dataset == "azure.auditlogs",
      CONCAT(
        TO_STRING(@timestamp),
        "|",
        COALESCE(`azure.auditlogs.properties.target_resources.0.display_name`, ""),
        "|",
        azure.auditlogs.operation_name
      ),
      null
    )
| EVAL Esql.signin_time = COALESCE(Esql.auth_step_time, @timestamp)
| WHERE
    Esql.sequence_stage == "register" OR
    (
      MV_COUNT(Esql.auth_method) == 1 AND
      (
        Esql.auth_method == "Windows Hello for Business" OR
        TO_LOWER(Esql.auth_method) LIKE "fido2*" OR
        TO_LOWER(Esql.auth_method) LIKE "*passkey*"
      )
    )
| KEEP
    @timestamp,
    Esql.signin_time,
    Esql.actor_user_id,
    Esql.sequence_stage,
    Esql.auth_method,
    Esql.register_candidate,
    azure.signinlogs.properties.user_id,
    azure.signinlogs.properties.user_principal_name,
    azure.signinlogs.properties.device_detail.device_id,
    azure.signinlogs.properties.device_detail.is_managed,
    azure.signinlogs.properties.cross_tenant_access_type,
    azure.signinlogs.properties.app_id,
    azure.signinlogs.properties.app_display_name,
    azure.signinlogs.properties.resource_display_name,
    azure.signinlogs.properties.incoming_token_type,
    source.address,
    source.geo.country_name,
    user_agent.original,
    _id,
    _version,
    _index
| WHERE Esql.actor_user_id IS NOT NULL
| INLINE STATS
    Esql.register_count = COUNT(*) WHERE Esql.sequence_stage == "register",
    Esql.register_candidates = VALUES(Esql.register_candidate) WHERE Esql.sequence_stage == "register"
  BY Esql.actor_user_id
| DROP Esql.register_candidate
| WHERE Esql.sequence_stage == "signin" AND Esql.register_count > 0
| MV_EXPAND Esql.register_candidates
| EVAL
    Esql.register_time = TO_DATETIME(MV_FIRST(SPLIT(Esql.register_candidates, "|"))),
    Esql.registered_device_name = MV_SLICE(SPLIT(Esql.register_candidates, "|"), 1, 1),
    Esql.register_operation = MV_LAST(SPLIT(Esql.register_candidates, "|")),
    Esql.signin_to_register_s = DATE_DIFF("second", Esql.signin_time, Esql.register_time)
| DROP Esql.register_candidates, Esql.sequence_stage, Esql.register_count
| WHERE
    Esql.signin_to_register_s >= 0 AND
    Esql.signin_to_register_s <= 900
| SORT Esql.register_time ASC, Esql.signin_to_register_s ASC, _id ASC
| LIMIT 1 BY Esql.actor_user_id