User Accounts - Sign in Failure due to CA Spikes

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 

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   
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.'  
triggerThreshold: 0
requiredDataConnectors:
- dataTypes:
  - SigninLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - AADNonInteractiveUserSignInLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - BehaviorAnalytics
  connectorId: BehaviorAnalytics
- dataTypes:
  - IdentityInfo
  connectorId: BehaviorAnalytics
name: User Accounts - Sign in Failure due to CA Spikes
relevantTechniques:
- T1078.004
queryPeriod: 14d
severity: Medium
triggerOperator: gt
version: 2.0.5
queryFrequency: 1d
entityMappings:
- entityType: Account
  fieldMappings:
  - identifier: FullName
    columnName: UserPrincipalName
  - identifier: Name
    columnName: Name
  - identifier: UPNSuffix
    columnName: UPNSuffix
- entityType: IP
  fieldMappings:
  - identifier: Address
    columnName: IPAddress
kind: Scheduled
status: Available
tags:
- AADSecOpsGuide
tactics:
- InitialAccess
id: 3a9d5ede-2b9d-43a2-acc4-d272321ff77c
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Entra ID/Analytic Rules/UserAccounts-CABlockedSigninSpikes.yaml

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "apiVersion": "2024-01-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",
        "subTechniques": [
          "T1078.004"
        ],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "InitialAccess"
        ],
        "tags": [
          "AADSecOpsGuide"
        ],
        "techniques": [
          "T1078"
        ],
        "templateVersion": "2.0.5",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}