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

| where event.category == "authentication" and event.dataset == "azure.signinlogs" and
        azure.signinlogs.properties.original_transfer_method == "deviceCodeFlow"

| Eval Esql.interactive_logon = CASE(azure.signinlogs.category == "SignInLogs", source.ip, null),
       Esql.non_interactive_logon = CASE(azure.signinlogs.category ==  "NonInteractiveUserSignInLogs", source.ip, null)

| stats Esql.count_logon = count(*),
        Esql.timestamp_values = values(@timestamp),
        Esql.source_ip_count_distinct = count_distinct(source.ip),
        Esql.is_interactive = count(Esql.interactive_logon),
        Esql.is_non_interactive = count(Esql.non_interactive_logon),
        Esql.user_agent_count_distinct = COUNT_DISTINCT(user_agent.original),
        Esql.user_agent_values = VALUES(user_agent.original),
        Esql.azure_signinlogs_properties_client_app_values = values(azure.signinlogs.properties.app_display_name),
        Esql.azure_signinlogs_properties_client_app_values = values(azure.signinlogs.properties.app_id),
        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),
        Esql.azure_signinlogs_properties_tenant_id = values(azure.tenant_id),
        Esql.azure_signinlogs_properties_status_error_code_values = values(azure.signinlogs.properties.status.error_code),
        Esql.message_values = values(message),
        Esql.azure_signinlogs_properties_resource_id_values = values(azure.signinlogs.properties.resource_id),
        Esql.source_ip_values = VALUES(source.ip) by azure.signinlogs.properties.session_id, azure.signinlogs.identity

| where Esql.is_interactive >= 2 and Esql.is_non_interactive >= 1 and (Esql.source_ip_count_distinct >= 2 or Esql.user_agent_count_distinct >= 2)
| keep
       Esql.*,
       azure.signinlogs.properties.session_id,
       azure.signinlogs.identity