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

| where
    // filter for Entra Sign-In Logs
    event.dataset == "azure.signinlogs"
    and azure.signinlogs.operation_name == "Sign-in activity"
    and azure.signinlogs.properties.user_type == "Member"

    // filter for MFA attempts with OATH conditional access attempts or TOTP
    and azure.signinlogs.properties.mfa_detail.auth_method == "OATH verification code"

    // filter on failures only from brute-force attempts
    and (
            (
                azure.signinlogs.result_signature == "FAILURE" and
                azure.signinlogs.result_description == "Authentication failed during strong authentication request."
            ) or azure.signinlogs.properties.status.error_code == 500121
        )

| stats
    Esql.event_count = COUNT(*),
    Esql.azure_signinlogs_properties.session_id_count_distinct = COUNT_DISTINCT(azure.signinlogs.properties.session_id),
    Esql.source_address_values = VALUES(source.address),
    Esql.azure_tenant_id_valuues = VALUES(azure.tenant_id),
    Esql_priv.azure_identity_values = VALUES(azure.signinlogs.identity),
    Esql_priv.azure_signinlogs_properties_user_principal_name_values = VALUES(azure.signinlogs.properties.user_principal_name),
    Esql.azure_signinlogs_properties_app_id_values = VALUES(azure.signinlogs.properties.app_id),
    Esql.azure_signinlogs_properties_app_display_name_values = VALUES(azure.signinlogs.properties.app_display_name),
    Esql.azure_signinlogs_properties_authentication_requirement_values = VALUES(azure.signinlogs.properties.authentication_requirement),
    Esql.azure_signinlogs_properties_authentication_protocol_values = VALUES(azure.signinlogs.properties.authentication_protocol),
    Esql.azure_signinlogs_properties_client_app_used_values = VALUES(azure.signinlogs.properties.client_app_used),
    Esql.azure_signinlogs_properties_client_credential_type_values = VALUES(azure.signinlogs.properties.client_credential_type),
    Esql.azure_signinlogs_properties_conditional_access_status_values = VALUES(azure.signinlogs.properties.conditional_access_status),
    Esql.azure_signinlogs_properties_correlation_id_values = VALUES(azure.signinlogs.properties.correlation_id),
    Esql.azure_signinlogs_properties_is_interactive_values = VALUES(azure.signinlogs.properties.is_interactive),
    Esql.azure_signinlogs_properties_mfa_detail_auth_method_values = VALUES(azure.signinlogs.properties.mfa_detail.auth_method),
    Esql.azure_signinlogs_properties_resource_display_name_values = VALUES(azure.signinlogs.properties.resource_display_name),
    Esql.azure_signinlogs_properties_resource_id_values = VALUES(azure.signinlogs.properties.resource_id),
    Esql.azure_signinlogs_properties_risk_state_values = VALUES(azure.signinlogs.properties.risk_state),
    Esql.azure_signinlogs_properties_risk_detail_values = VALUES(azure.signinlogs.properties.risk_detail),
    Esql.azure_signinlogs_properties_status.error_code_values = VALUES(azure.signinlogs.properties.status.error_code),
    Esql.azure_signinlogs_properties_original_request_id_values = VALUES(azure.signinlogs.properties.original_request_id),
    Esql.user_id_values = VALUES(user.id)
    by user.id

| where Esql.event_count >= 20 and Esql.azure_signinlogs_properties.session_id_count_distinct >= 10

| keep
    Esql.event_count,
    Esql.azure_signinlogs_properties.session_id_count_distinct,
    Esql.source_address_values,
    Esql.azure_tenant_id_valuues,
    Esql_priv.azure_identity_values,
    Esql_priv.azure_signinlogs_properties_user_principal_name_values,
    Esql.azure_signinlogs_properties_app_id_values,
    Esql.azure_signinlogs_properties_app_display_name_values,
    Esql.azure_signinlogs_properties_authentication_requirement_values,
    Esql.azure_signinlogs_properties_authentication_protocol_values,
    Esql.azure_signinlogs_properties_client_app_used_values,
    Esql.azure_signinlogs_properties_client_credential_type_values,
    Esql.azure_signinlogs_properties_conditional_access_status_values,
    Esql.azure_signinlogs_properties_correlation_id_values,
    Esql.azure_signinlogs_properties_is_interactive_values,
    Esql.azure_signinlogs_properties_mfa_detail_auth_method_values,
    Esql.azure_signinlogs_properties_resource_display_name_values,
    Esql.azure_signinlogs_properties_resource_id_values,
    Esql.azure_signinlogs_properties_risk_state_values,
    Esql.azure_signinlogs_properties_risk_detail_values,
    Esql.azure_signinlogs_properties_status.error_code_values,
    Esql.azure_signinlogs_properties_original_request_id_values,
    Esql.user_id_values