Microsoft Sentinel Analytic Rules
cloudbrothers.infoAzure Sentinel RepoToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeBack to homepage

User Accounts - Sign in Failure due to CA Spikes

Back
Id3a9d5ede-2b9d-43a2-acc4-d272321ff77c
RulenameUser Accounts - Sign in Failure due to CA Spikes
DescriptionIdentifies spike in failed sign-ins from user accounts due to conditional access policied.

Spike is determined based on Time series anomaly which will look at historical baseline values.

Ref : https://docs.microsoft.com/azure/active-directory/fundamentals/security-operations-user-accounts#monitoring-for-failed-unusual-sign-ins

This query has also been updated to include UEBA logs IdentityInfo and BehaviorAnalytics for contextual information around the results.
SeverityMedium
TacticsInitialAccess
TechniquesT1078.004
Required data connectorsAzureActiveDirectory
BehaviorAnalytics
KindScheduled
Query frequency1d
Query period14d
Trigger threshold0
Trigger operatorgt
Source Urihttps://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Entra ID/Analytic Rules/UserAccounts-CABlockedSigninSpikes.yaml
Version2.0.5
Arm template3a9d5ede-2b9d-43a2-acc4-d272321ff77c.json
Deploy To Azure
let riskScoreCutoff = 20; //Adjust this based on volume of results
let starttime = 14d;
let timeframe = 1d;
let scorethreshold = 3;
let baselinethreshold = 50;
let aadFunc = (tableName:string){
  // Failed Signins attempts with reasoning related to conditional access policies.
  table(tableName)
  | where TimeGenerated between (startofday(ago(starttime))..startofday(now()))
  | where ResultDescription has_any ("conditional access", "CA") or ResultType in (50005, 50131, 53000, 53001, 53002, 52003, 70044)
  | extend UserPrincipalName = tolower(UserPrincipalName)
  | extend timestamp = TimeGenerated, AccountCustomEntity = UserPrincipalName
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
let allSignins = union isfuzzy=true aadSignin, aadNonInt;
let TimeSeriesAlerts = 
allSignins
| make-series DailyCount=count() on TimeGenerated from startofday(ago(starttime)) to startofday(now()) step 1d by UserPrincipalName
| extend (anomalies, score, baseline) = series_decompose_anomalies(DailyCount, scorethreshold, -1, 'linefit')
| mv-expand DailyCount to typeof(double), TimeGenerated to typeof(datetime), anomalies to typeof(double), score to typeof(double), baseline to typeof(long)
// Filtering low count events per baselinethreshold
| where anomalies > 0 and baseline > baselinethreshold
| extend AnomalyHour = TimeGenerated
| project UserPrincipalName, AnomalyHour, TimeGenerated, DailyCount, baseline, anomalies, score;
// Filter the alerts for specified timeframe
TimeSeriesAlerts
| where TimeGenerated > startofday(ago(timeframe))
| join kind=inner ( 
  allSignins
  | where TimeGenerated > startofday(ago(timeframe))
  // create a new column and round to hour
  | extend DateHour = bin(TimeGenerated, 1h)
  | summarize PartialFailedSignins = count(), LatestAnomalyTime = arg_max(TimeGenerated, *) by bin(TimeGenerated, 1h), OperationName, Category, ResultType, ResultDescription, UserPrincipalName, UserDisplayName, AppDisplayName, ClientAppUsed, IPAddress, ResourceDisplayName
) on UserPrincipalName, $left.AnomalyHour == $right.DateHour
| project LatestAnomalyTime, OperationName, Category, UserPrincipalName, UserDisplayName, ResultType, ResultDescription, AppDisplayName, ClientAppUsed, UserAgent, IPAddress, Location, AuthenticationRequirement, ConditionalAccessStatus, ResourceDisplayName, PartialFailedSignins, TotalFailedSignins = DailyCount, baseline, anomalies, score
| extend timestamp = LatestAnomalyTime, Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])
| extend UserPrincipalName = tolower(UserPrincipalName)
| join kind=leftouter (
    IdentityInfo
    | summarize LatestReportTime = arg_max(TimeGenerated, *) by AccountUPN
    | project AccountUPN, Tags, JobTitle, GroupMembership, AssignedRoles, UserType, IsAccountEnabled
    | summarize
        Tags = make_set(Tags, 1000),
        GroupMembership = make_set(GroupMembership, 1000),
        AssignedRoles = make_set(AssignedRoles, 1000),
        UserType = make_set(UserType, 1000),
        UserAccountControl = make_set(UserType, 1000)
    by AccountUPN
    | extend UserPrincipalName=tolower(AccountUPN)
) on UserPrincipalName
| join kind=leftouter (
    BehaviorAnalytics
    | where ActivityType in ("FailedLogOn", "LogOn")
    | where isnotempty(SourceIPAddress)
    | project UsersInsights, DevicesInsights, ActivityInsights, InvestigationPriority, SourceIPAddress
    | project-rename IPAddress = SourceIPAddress
    | summarize
        UsersInsights = make_set(UsersInsights, 1000),
        DevicesInsights = make_set(DevicesInsights, 1000),
        IPInvestigationPriority = sum(InvestigationPriority)
    by IPAddress)
on IPAddress
| extend UEBARiskScore = IPInvestigationPriority
| where UEBARiskScore > riskScoreCutoff
| sort by UEBARiskScore desc 
status: Available
description: |
  ' Identifies spike in failed sign-ins from user accounts due to conditional access policied.
  Spike is determined based on Time series anomaly which will look at historical baseline values.
  Ref : https://docs.microsoft.com/azure/active-directory/fundamentals/security-operations-user-accounts#monitoring-for-failed-unusual-sign-ins
  This query has also been updated to include UEBA logs IdentityInfo and BehaviorAnalytics for contextual information around the results.'  
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Entra ID/Analytic Rules/UserAccounts-CABlockedSigninSpikes.yaml
version: 2.0.5
triggerThreshold: 0
kind: Scheduled
queryPeriod: 14d
tags:
- AADSecOpsGuide
entityMappings:
- entityType: Account
  fieldMappings:
  - columnName: UserPrincipalName
    identifier: FullName
  - columnName: Name
    identifier: Name
  - columnName: UPNSuffix
    identifier: UPNSuffix
- entityType: IP
  fieldMappings:
  - columnName: IPAddress
    identifier: Address
requiredDataConnectors:
- connectorId: AzureActiveDirectory
  dataTypes:
  - SigninLogs
- connectorId: AzureActiveDirectory
  dataTypes:
  - AADNonInteractiveUserSignInLogs
- connectorId: BehaviorAnalytics
  dataTypes:
  - BehaviorAnalytics
- connectorId: BehaviorAnalytics
  dataTypes:
  - IdentityInfo
triggerOperator: gt
id: 3a9d5ede-2b9d-43a2-acc4-d272321ff77c
name: User Accounts - Sign in Failure due to CA Spikes
query: |
  let riskScoreCutoff = 20; //Adjust this based on volume of results
  let starttime = 14d;
  let timeframe = 1d;
  let scorethreshold = 3;
  let baselinethreshold = 50;
  let aadFunc = (tableName:string){
    // Failed Signins attempts with reasoning related to conditional access policies.
    table(tableName)
    | where TimeGenerated between (startofday(ago(starttime))..startofday(now()))
    | where ResultDescription has_any ("conditional access", "CA") or ResultType in (50005, 50131, 53000, 53001, 53002, 52003, 70044)
    | extend UserPrincipalName = tolower(UserPrincipalName)
    | extend timestamp = TimeGenerated, AccountCustomEntity = UserPrincipalName
  };
  let aadSignin = aadFunc("SigninLogs");
  let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
  let allSignins = union isfuzzy=true aadSignin, aadNonInt;
  let TimeSeriesAlerts = 
  allSignins
  | make-series DailyCount=count() on TimeGenerated from startofday(ago(starttime)) to startofday(now()) step 1d by UserPrincipalName
  | extend (anomalies, score, baseline) = series_decompose_anomalies(DailyCount, scorethreshold, -1, 'linefit')
  | mv-expand DailyCount to typeof(double), TimeGenerated to typeof(datetime), anomalies to typeof(double), score to typeof(double), baseline to typeof(long)
  // Filtering low count events per baselinethreshold
  | where anomalies > 0 and baseline > baselinethreshold
  | extend AnomalyHour = TimeGenerated
  | project UserPrincipalName, AnomalyHour, TimeGenerated, DailyCount, baseline, anomalies, score;
  // Filter the alerts for specified timeframe
  TimeSeriesAlerts
  | where TimeGenerated > startofday(ago(timeframe))
  | join kind=inner ( 
    allSignins
    | where TimeGenerated > startofday(ago(timeframe))
    // create a new column and round to hour
    | extend DateHour = bin(TimeGenerated, 1h)
    | summarize PartialFailedSignins = count(), LatestAnomalyTime = arg_max(TimeGenerated, *) by bin(TimeGenerated, 1h), OperationName, Category, ResultType, ResultDescription, UserPrincipalName, UserDisplayName, AppDisplayName, ClientAppUsed, IPAddress, ResourceDisplayName
  ) on UserPrincipalName, $left.AnomalyHour == $right.DateHour
  | project LatestAnomalyTime, OperationName, Category, UserPrincipalName, UserDisplayName, ResultType, ResultDescription, AppDisplayName, ClientAppUsed, UserAgent, IPAddress, Location, AuthenticationRequirement, ConditionalAccessStatus, ResourceDisplayName, PartialFailedSignins, TotalFailedSignins = DailyCount, baseline, anomalies, score
  | extend timestamp = LatestAnomalyTime, Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])
  | extend UserPrincipalName = tolower(UserPrincipalName)
  | join kind=leftouter (
      IdentityInfo
      | summarize LatestReportTime = arg_max(TimeGenerated, *) by AccountUPN
      | project AccountUPN, Tags, JobTitle, GroupMembership, AssignedRoles, UserType, IsAccountEnabled
      | summarize
          Tags = make_set(Tags, 1000),
          GroupMembership = make_set(GroupMembership, 1000),
          AssignedRoles = make_set(AssignedRoles, 1000),
          UserType = make_set(UserType, 1000),
          UserAccountControl = make_set(UserType, 1000)
      by AccountUPN
      | extend UserPrincipalName=tolower(AccountUPN)
  ) on UserPrincipalName
  | join kind=leftouter (
      BehaviorAnalytics
      | where ActivityType in ("FailedLogOn", "LogOn")
      | where isnotempty(SourceIPAddress)
      | project UsersInsights, DevicesInsights, ActivityInsights, InvestigationPriority, SourceIPAddress
      | project-rename IPAddress = SourceIPAddress
      | summarize
          UsersInsights = make_set(UsersInsights, 1000),
          DevicesInsights = make_set(DevicesInsights, 1000),
          IPInvestigationPriority = sum(InvestigationPriority)
      by IPAddress)
  on IPAddress
  | extend UEBARiskScore = IPInvestigationPriority
  | where UEBARiskScore > riskScoreCutoff
  | sort by UEBARiskScore desc   
tactics:
- InitialAccess
queryFrequency: 1d
relevantTechniques:
- T1078.004
severity: Medium
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "apiVersion": "2023-02-01-preview",
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/3a9d5ede-2b9d-43a2-acc4-d272321ff77c')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/3a9d5ede-2b9d-43a2-acc4-d272321ff77c')]",
      "properties": {
        "alertRuleTemplateName": "3a9d5ede-2b9d-43a2-acc4-d272321ff77c",
        "customDetails": null,
        "description": "' Identifies spike in failed sign-ins from user accounts due to conditional access policied.\nSpike is determined based on Time series anomaly which will look at historical baseline values.\nRef : https://docs.microsoft.com/azure/active-directory/fundamentals/security-operations-user-accounts#monitoring-for-failed-unusual-sign-ins\nThis query has also been updated to include UEBA logs IdentityInfo and BehaviorAnalytics for contextual information around the results.'\n",
        "displayName": "User Accounts - Sign in Failure due to CA Spikes",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "UserPrincipalName",
                "identifier": "FullName"
              },
              {
                "columnName": "Name",
                "identifier": "Name"
              },
              {
                "columnName": "UPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "IPAddress",
                "identifier": "Address"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Entra ID/Analytic Rules/UserAccounts-CABlockedSigninSpikes.yaml",
        "query": "let riskScoreCutoff = 20; //Adjust this based on volume of results\nlet starttime = 14d;\nlet timeframe = 1d;\nlet scorethreshold = 3;\nlet baselinethreshold = 50;\nlet aadFunc = (tableName:string){\n  // Failed Signins attempts with reasoning related to conditional access policies.\n  table(tableName)\n  | where TimeGenerated between (startofday(ago(starttime))..startofday(now()))\n  | where ResultDescription has_any (\"conditional access\", \"CA\") or ResultType in (50005, 50131, 53000, 53001, 53002, 52003, 70044)\n  | extend UserPrincipalName = tolower(UserPrincipalName)\n  | extend timestamp = TimeGenerated, AccountCustomEntity = UserPrincipalName\n};\nlet aadSignin = aadFunc(\"SigninLogs\");\nlet aadNonInt = aadFunc(\"AADNonInteractiveUserSignInLogs\");\nlet allSignins = union isfuzzy=true aadSignin, aadNonInt;\nlet TimeSeriesAlerts = \nallSignins\n| make-series DailyCount=count() on TimeGenerated from startofday(ago(starttime)) to startofday(now()) step 1d by UserPrincipalName\n| extend (anomalies, score, baseline) = series_decompose_anomalies(DailyCount, scorethreshold, -1, 'linefit')\n| mv-expand DailyCount to typeof(double), TimeGenerated to typeof(datetime), anomalies to typeof(double), score to typeof(double), baseline to typeof(long)\n// Filtering low count events per baselinethreshold\n| where anomalies > 0 and baseline > baselinethreshold\n| extend AnomalyHour = TimeGenerated\n| project UserPrincipalName, AnomalyHour, TimeGenerated, DailyCount, baseline, anomalies, score;\n// Filter the alerts for specified timeframe\nTimeSeriesAlerts\n| where TimeGenerated > startofday(ago(timeframe))\n| join kind=inner ( \n  allSignins\n  | where TimeGenerated > startofday(ago(timeframe))\n  // create a new column and round to hour\n  | extend DateHour = bin(TimeGenerated, 1h)\n  | summarize PartialFailedSignins = count(), LatestAnomalyTime = arg_max(TimeGenerated, *) by bin(TimeGenerated, 1h), OperationName, Category, ResultType, ResultDescription, UserPrincipalName, UserDisplayName, AppDisplayName, ClientAppUsed, IPAddress, ResourceDisplayName\n) on UserPrincipalName, $left.AnomalyHour == $right.DateHour\n| project LatestAnomalyTime, OperationName, Category, UserPrincipalName, UserDisplayName, ResultType, ResultDescription, AppDisplayName, ClientAppUsed, UserAgent, IPAddress, Location, AuthenticationRequirement, ConditionalAccessStatus, ResourceDisplayName, PartialFailedSignins, TotalFailedSignins = DailyCount, baseline, anomalies, score\n| extend timestamp = LatestAnomalyTime, Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])\n| extend UserPrincipalName = tolower(UserPrincipalName)\n| join kind=leftouter (\n    IdentityInfo\n    | summarize LatestReportTime = arg_max(TimeGenerated, *) by AccountUPN\n    | project AccountUPN, Tags, JobTitle, GroupMembership, AssignedRoles, UserType, IsAccountEnabled\n    | summarize\n        Tags = make_set(Tags, 1000),\n        GroupMembership = make_set(GroupMembership, 1000),\n        AssignedRoles = make_set(AssignedRoles, 1000),\n        UserType = make_set(UserType, 1000),\n        UserAccountControl = make_set(UserType, 1000)\n    by AccountUPN\n    | extend UserPrincipalName=tolower(AccountUPN)\n) on UserPrincipalName\n| join kind=leftouter (\n    BehaviorAnalytics\n    | where ActivityType in (\"FailedLogOn\", \"LogOn\")\n    | where isnotempty(SourceIPAddress)\n    | project UsersInsights, DevicesInsights, ActivityInsights, InvestigationPriority, SourceIPAddress\n    | project-rename IPAddress = SourceIPAddress\n    | summarize\n        UsersInsights = make_set(UsersInsights, 1000),\n        DevicesInsights = make_set(DevicesInsights, 1000),\n        IPInvestigationPriority = sum(InvestigationPriority)\n    by IPAddress)\non IPAddress\n| extend UEBARiskScore = IPInvestigationPriority\n| where UEBARiskScore > riskScoreCutoff\n| sort by UEBARiskScore desc \n",
        "queryFrequency": "P1D",
        "queryPeriod": "P14D",
        "severity": "Medium",
        "status": "Available",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "InitialAccess"
        ],
        "tags": [
          "AADSecOpsGuide"
        ],
        "techniques": [
          "T1078"
        ],
        "templateVersion": "2.0.5",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}