User Accounts - Sign in Failure due to CA Spikes

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