Privileged Accounts - Sign in Failure Spikes

let starttime = 14d;
let timeframe = 1d;
let scorethreshold = 3;
let baselinethreshold = 5;
let aadFunc = (tableName:string){
    IdentityInfo
    | where TimeGenerated > ago(starttime)
    | summarize arg_max(TimeGenerated, *) by AccountUPN
    | mv-expand AssignedRoles
    | where AssignedRoles contains 'Admin' or GroupMembership has "Admin"
    | summarize Roles = make_list(AssignedRoles) by AccountUPN = tolower(AccountUPN)
    | join kind=inner (
        table(tableName)
        | where TimeGenerated between (startofday(ago(starttime))..startofday(now()))
        | where ResultType != 0
        | extend UserPrincipalName = tolower(UserPrincipalName)
    ) on $left.AccountUPN == $right.UserPrincipalName
    | extend timestamp = TimeGenerated, AccountCustomEntity = UserPrincipalName, Roles = tostring(Roles)
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
let allSignins = union isfuzzy=true aadSignin, aadNonInt;
let TimeSeriesAlerts = 
    allSignins
    | make-series HourlyCount=count() on TimeGenerated from startofday(ago(starttime)) to startofday(now()) step 1h by UserPrincipalName, Roles
    | extend (anomalies, score, baseline) = series_decompose_anomalies(HourlyCount, scorethreshold, -1, 'linefit')
    | mv-expand HourlyCount 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, Roles, AnomalyHour, TimeGenerated, HourlyCount, 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, Roles, UserDisplayName, AppDisplayName, ClientAppUsed, IPAddress, ResourceDisplayName
) on UserPrincipalName, $left.AnomalyHour == $right.DateHour
| project LatestAnomalyTime, OperationName, Category, UserPrincipalName, Roles = todynamic(Roles), UserDisplayName, ResultType, ResultDescription, AppDisplayName, ClientAppUsed, UserAgent, IPAddress, Location, AuthenticationRequirement, ConditionalAccessStatus, ResourceDisplayName, PartialFailedSignins, TotalFailedSignins = HourlyCount, baseline, anomalies, score
| extend Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])

kind: Scheduled
relevantTechniques:
- T1078.004
description: |
  ' Identifies spike in failed sign-ins from Privileged accounts. Privileged accounts list can be based on IdentityInfo UEBA table.
  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-privileged-accounts#things-to-monitor'  
queryPeriod: 14d
queryFrequency: 1d
tactics:
- InitialAccess
name: Privileged Accounts - Sign in Failure Spikes
requiredDataConnectors:
- connectorId: AzureActiveDirectory
  dataTypes:
  - SigninLogs
- connectorId: AzureActiveDirectory
  dataTypes:
  - AADNonInteractiveUserSignInLogs
- connectorId: BehaviorAnalytics
  dataTypes:
  - IdentityInfo
entityMappings:
- entityType: Account
  fieldMappings:
  - identifier: FullName
    columnName: UserPrincipalName
  - identifier: Name
    columnName: Name
  - identifier: UPNSuffix
    columnName: UPNSuffix
- entityType: IP
  fieldMappings:
  - identifier: Address
    columnName: IPAddress
triggerThreshold: 0
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Entra ID/Analytic Rules/PrivilegedAccountsSigninFailureSpikes.yaml
version: 1.1.1
id: 34c5aff9-a8c2-4601-9654-c7e46342d03b
query: |
  let starttime = 14d;
  let timeframe = 1d;
  let scorethreshold = 3;
  let baselinethreshold = 5;
  let aadFunc = (tableName:string){
      IdentityInfo
      | where TimeGenerated > ago(starttime)
      | summarize arg_max(TimeGenerated, *) by AccountUPN
      | mv-expand AssignedRoles
      | where AssignedRoles contains 'Admin' or GroupMembership has "Admin"
      | summarize Roles = make_list(AssignedRoles) by AccountUPN = tolower(AccountUPN)
      | join kind=inner (
          table(tableName)
          | where TimeGenerated between (startofday(ago(starttime))..startofday(now()))
          | where ResultType != 0
          | extend UserPrincipalName = tolower(UserPrincipalName)
      ) on $left.AccountUPN == $right.UserPrincipalName
      | extend timestamp = TimeGenerated, AccountCustomEntity = UserPrincipalName, Roles = tostring(Roles)
  };
  let aadSignin = aadFunc("SigninLogs");
  let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
  let allSignins = union isfuzzy=true aadSignin, aadNonInt;
  let TimeSeriesAlerts = 
      allSignins
      | make-series HourlyCount=count() on TimeGenerated from startofday(ago(starttime)) to startofday(now()) step 1h by UserPrincipalName, Roles
      | extend (anomalies, score, baseline) = series_decompose_anomalies(HourlyCount, scorethreshold, -1, 'linefit')
      | mv-expand HourlyCount 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, Roles, AnomalyHour, TimeGenerated, HourlyCount, 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, Roles, UserDisplayName, AppDisplayName, ClientAppUsed, IPAddress, ResourceDisplayName
  ) on UserPrincipalName, $left.AnomalyHour == $right.DateHour
  | project LatestAnomalyTime, OperationName, Category, UserPrincipalName, Roles = todynamic(Roles), UserDisplayName, ResultType, ResultDescription, AppDisplayName, ClientAppUsed, UserAgent, IPAddress, Location, AuthenticationRequirement, ConditionalAccessStatus, ResourceDisplayName, PartialFailedSignins, TotalFailedSignins = HourlyCount, baseline, anomalies, score
  | extend Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])  
status: Available
triggerOperator: gt
tags:
- AADSecOpsGuide
severity: High

{
  "$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/34c5aff9-a8c2-4601-9654-c7e46342d03b')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/34c5aff9-a8c2-4601-9654-c7e46342d03b')]",
      "properties": {
        "alertRuleTemplateName": "34c5aff9-a8c2-4601-9654-c7e46342d03b",
        "customDetails": null,
        "description": "' Identifies spike in failed sign-ins from Privileged accounts. Privileged accounts list can be based on IdentityInfo UEBA table.\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-privileged-accounts#things-to-monitor'\n",
        "displayName": "Privileged Accounts - Sign in Failure 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/PrivilegedAccountsSigninFailureSpikes.yaml",
        "query": "let starttime = 14d;\nlet timeframe = 1d;\nlet scorethreshold = 3;\nlet baselinethreshold = 5;\nlet aadFunc = (tableName:string){\n    IdentityInfo\n    | where TimeGenerated > ago(starttime)\n    | summarize arg_max(TimeGenerated, *) by AccountUPN\n    | mv-expand AssignedRoles\n    | where AssignedRoles contains 'Admin' or GroupMembership has \"Admin\"\n    | summarize Roles = make_list(AssignedRoles) by AccountUPN = tolower(AccountUPN)\n    | join kind=inner (\n        table(tableName)\n        | where TimeGenerated between (startofday(ago(starttime))..startofday(now()))\n        | where ResultType != 0\n        | extend UserPrincipalName = tolower(UserPrincipalName)\n    ) on $left.AccountUPN == $right.UserPrincipalName\n    | extend timestamp = TimeGenerated, AccountCustomEntity = UserPrincipalName, Roles = tostring(Roles)\n};\nlet aadSignin = aadFunc(\"SigninLogs\");\nlet aadNonInt = aadFunc(\"AADNonInteractiveUserSignInLogs\");\nlet allSignins = union isfuzzy=true aadSignin, aadNonInt;\nlet TimeSeriesAlerts = \n    allSignins\n    | make-series HourlyCount=count() on TimeGenerated from startofday(ago(starttime)) to startofday(now()) step 1h by UserPrincipalName, Roles\n    | extend (anomalies, score, baseline) = series_decompose_anomalies(HourlyCount, scorethreshold, -1, 'linefit')\n    | mv-expand HourlyCount 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, Roles, AnomalyHour, TimeGenerated, HourlyCount, 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, Roles, UserDisplayName, AppDisplayName, ClientAppUsed, IPAddress, ResourceDisplayName\n) on UserPrincipalName, $left.AnomalyHour == $right.DateHour\n| project LatestAnomalyTime, OperationName, Category, UserPrincipalName, Roles = todynamic(Roles), UserDisplayName, ResultType, ResultDescription, AppDisplayName, ClientAppUsed, UserAgent, IPAddress, Location, AuthenticationRequirement, ConditionalAccessStatus, ResourceDisplayName, PartialFailedSignins, TotalFailedSignins = HourlyCount, baseline, anomalies, score\n| extend Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])\n",
        "queryFrequency": "P1D",
        "queryPeriod": "P14D",
        "severity": "High",
        "status": "Available",
        "subTechniques": [
          "T1078.004"
        ],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "InitialAccess"
        ],
        "tags": [
          "AADSecOpsGuide"
        ],
        "techniques": [
          "T1078"
        ],
        "templateVersion": "1.1.1",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}