from logs-azure.signinlogs*
| eval
Esql.time_window_date_trunc = date_trunc(30 minutes, @timestamp),
Esql_priv.azure_signinlogs_properties_user_principal_name_lower = to_lower(azure.signinlogs.properties.user_principal_name),
Esql.azure_signinlogs_properties_incoming_token_type_lower = to_lower(azure.signinlogs.properties.incoming_token_type),
Esql.azure_signinlogs_properties_app_display_name_lower = to_lower(azure.signinlogs.properties.app_display_name)
| where event.dataset == "azure.signinlogs"
and event.category == "authentication"
and azure.signinlogs.category in ("NonInteractiveUserSignInLogs", "SignInLogs")
and event.outcome == "failure"
and azure.signinlogs.properties.authentication_requirement == "singleFactorAuthentication"
and azure.signinlogs.properties.status.error_code == 50053
and azure.signinlogs.properties.user_principal_name is not null
and azure.signinlogs.properties.user_principal_name != ""
and source.`as`.organization.name != "MICROSOFT-CORP-MSN-as-BLOCK"
| stats
Esql.azure_signinlogs_properties_authentication_requirement_values = values(azure.signinlogs.properties.authentication_requirement),
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_resource_id_values = values(azure.signinlogs.properties.resource_id),
Esql.azure_signinlogs_properties_resource_display_name_values = values(azure.signinlogs.properties.resource_display_name),
Esql.azure_signinlogs_properties_conditional_access_status_values = values(azure.signinlogs.properties.conditional_access_status),
Esql.azure_signinlogs_properties_device_detail_browser_values = values(azure.signinlogs.properties.device_detail.browser),
Esql.azure_signinlogs_properties_device_detail_device_id_values = values(azure.signinlogs.properties.device_detail.device_id),
Esql.azure_signinlogs_properties_device_detail_operating_system_values = values(azure.signinlogs.properties.device_detail.operating_system),
Esql.azure_signinlogs_properties_incoming_token_type_values = values(azure.signinlogs.properties.incoming_token_type),
Esql.azure_signinlogs_properties_risk_state_values = values(azure.signinlogs.properties.risk_state),
Esql.azure_signinlogs_properties_session_id_values = values(azure.signinlogs.properties.session_id),
Esql.azure_signinlogs_properties_user_id_values = values(azure.signinlogs.properties.user_id),
Esql_priv.azure_signinlogs_properties_user_principal_name_values = values(azure.signinlogs.properties.user_principal_name),
Esql.azure_signinlogs_result_description_values = values(azure.signinlogs.result_description),
Esql.azure_signinlogs_result_signature_values = values(azure.signinlogs.result_signature),
Esql.azure_signinlogs_result_type_values = values(azure.signinlogs.result_type),
Esql.azure_signinlogs_properties_user_principal_name_lower_count_distinct = count_distinct(Esql_priv.azure_signinlogs_properties_user_principal_name_lower),
Esql_priv.azure_signinlogs_properties_user_principal_name_lower_values = values(Esql_priv.azure_signinlogs_properties_user_principal_name_lower),
Esql.azure_signinlogs_result_description_count_distinct = count_distinct(azure.signinlogs.result_description),
Esql.azure_signinlogs_properties_status_error_code_count_distinct = count_distinct(azure.signinlogs.properties.status.error_code),
Esql.azure_signinlogs_properties_status_error_code_values = values(azure.signinlogs.properties.status.error_code),
Esql.azure_signinlogs_properties_incoming_token_type_lower_values = values(Esql.azure_signinlogs_properties_incoming_token_type_lower),
Esql.azure_signinlogs_properties_app_display_name_lower_values = values(Esql.azure_signinlogs_properties_app_display_name_lower),
Esql.source_ip_values = values(source.ip),
Esql.source_ip_count_distinct = count_distinct(source.ip),
Esql.source_as_organization_name_values = values(source.`as`.organization.name),
Esql.source_as_organization_name_count_distinct = count_distinct(source.`as`.organization.name),
Esql.source_geo_country_name_values = values(source.geo.country_name),
Esql.source_geo_country_name_count_distinct = count_distinct(source.geo.country_name),
Esql.@timestamp.min = min(@timestamp),
Esql.@timestamp.max = max(@timestamp),
Esql.event_count = count()
by Esql.time_window_date_trunc
| where Esql.azure_signinlogs_properties_user_principal_name_lower_count_distinct >= 15 and Esql.event_count >= 20
| keep
Esql.time_window_date_trunc,
Esql.event_count,
Esql.@timestamp.min,
Esql.@timestamp.max,
Esql.azure_signinlogs_properties_user_principal_name_lower_count_distinct,
Esql_priv.azure_signinlogs_properties_user_principal_name_lower_values,
Esql.azure_signinlogs_result_description_count_distinct,
Esql.azure_signinlogs_result_description_values,
Esql.azure_signinlogs_properties_status_error_code_count_distinct,
Esql.azure_signinlogs_properties_status_error_code_values,
Esql.azure_signinlogs_properties_incoming_token_type_lower_values,
Esql.azure_signinlogs_properties_app_display_name_lower_values,
Esql.source_ip_values,
Esql.source_ip_count_distinct,
Esql.source_as_organization_name_values,
Esql.source_as_organization_name_count_distinct,
Esql.source_geo_country_name_values,
Esql.source_geo_country_name_count_distinct,
Esql.azure_signinlogs_properties_authentication_requirement_values,
Esql.azure_signinlogs_properties_app_id_values,
Esql.azure_signinlogs_properties_app_display_name_values,
Esql.azure_signinlogs_properties_resource_id_values,
Esql.azure_signinlogs_properties_resource_display_name_values,
Esql.azure_signinlogs_properties_conditional_access_status_values,
Esql.azure_signinlogs_properties_device_detail_browser_values,
Esql.azure_signinlogs_properties_device_detail_device_id_values,
Esql.azure_signinlogs_properties_device_detail_operating_system_values,
Esql.azure_signinlogs_properties_incoming_token_type_values,
Esql.azure_signinlogs_properties_risk_state_values,
Esql.azure_signinlogs_properties_session_id_values,
Esql.azure_signinlogs_properties_user_id_values,
Esql_priv.azure_signinlogs_properties_user_principal_name_values,
Esql.azure_signinlogs_result_description_values,
Esql.azure_signinlogs_result_signature_values,
Esql.azure_signinlogs_result_type_values
Install detection rules in Elastic Security
Detect Microsoft Entra ID Exccessive Account Lockouts Detected in the Elastic Security detection engine by installing this rule into your Elastic Stack.
To setup this rule, check out the installation guide for Prebuilt Security Detection Rules(opens in a new tab or window).