Successful logon from IP and failure from a different IP

let riskScoreCutoff = 3; //Adjust this score threshold based on volume of results. Activities identified as the most abnormal receive the highest scores (on a scale of 0-10)
let logonDiff = 10m; 
let aadFunc = (tableName:string)
{ 
table(tableName)
| where ResultType == "0"
| where AppDisplayName !in ("Office 365 Exchange Online", "Skype for Business Online") // To remove false-positives, add more Apps to this array
// ---------- Fix for SuccessBlock to also consider IPv6
| extend SuccessIPv6Block = strcat(split(IPAddress, ":")[0], ":", split(IPAddress, ":")[1], ":", split(IPAddress, ":")[2], ":", split(IPAddress, ":")[3])
| extend SuccessIPv4Block = strcat(split(IPAddress, ".")[0], ".", split(IPAddress, ".")[1])
// ------------------
| project SuccessLogonTime = TimeGenerated, UserPrincipalName, SuccessIPAddress = IPAddress, SuccessLocation = Location, AppDisplayName, SuccessIPBlock = iff(IPAddress contains ":", strcat(split(IPAddress, ":")[0], ":", split(IPAddress, ":")[1]), strcat(split(IPAddress, ".")[0], ".", split(IPAddress, ".")[1])), Type
| join kind= inner (
    table(tableName)
    | where ResultType !in ("0", "50140")
    | where ResultDescription !~ "Other"
    | where AppDisplayName !in ("Office 365 Exchange Online", "Skype for Business Online")
    | project FailedLogonTime = TimeGenerated, UserPrincipalName, FailedIPAddress = IPAddress, FailedLocation = Location, AppDisplayName, ResultType, ResultDescription, Type 
) on UserPrincipalName, AppDisplayName
| where SuccessLogonTime < FailedLogonTime and FailedLogonTime - SuccessLogonTime <= logonDiff and FailedIPAddress !startswith SuccessIPBlock
| summarize FailedLogonTime = max(FailedLogonTime), SuccessLogonTime = max(SuccessLogonTime) by UserPrincipalName, SuccessIPAddress, SuccessLocation, AppDisplayName, FailedIPAddress, FailedLocation, ResultType, ResultDescription, Type
| extend timestamp = SuccessLogonTime
| extend UserPrincipalName = tolower(UserPrincipalName)};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
union isfuzzy=true aadSignin, aadNonInt
| extend Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])
// UEBA context below - make sure you have these 2 datatypes, otherwise the query will not work. If so, comment all that is below.
| 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
//Below it will be joined with BehaviorAnalytics table to the Failed IP Addresses
| join kind=leftouter (
    BehaviorAnalytics
    | where ActivityType in ("FailedLogOn", "LogOn")
    | where isnotempty(SourceIPAddress)
    | project UsersInsights, DevicesInsights, ActivityInsights, InvestigationPriority, SourceIPAddress, UserName
    | project-rename FailedIPAddress = SourceIPAddress, Name = UserName
    | summarize
        MaxInvestigationScore = max(InvestigationPriority)  // Only retrieve maximum Investigation Property score for both FailedIP and User
    by FailedIPAddress, Name)
on FailedIPAddress, Name  // Joining on both IP and User so as to only return context associated with same user
| extend UEBARiskScore = MaxInvestigationScore
| project-away *1 // removing duplicate columns post outer join from output
| where  UEBARiskScore > riskScoreCutoff
| sort by UEBARiskScore desc

query: |
  let riskScoreCutoff = 3; //Adjust this score threshold based on volume of results. Activities identified as the most abnormal receive the highest scores (on a scale of 0-10)
  let logonDiff = 10m; 
  let aadFunc = (tableName:string)
  { 
  table(tableName)
  | where ResultType == "0"
  | where AppDisplayName !in ("Office 365 Exchange Online", "Skype for Business Online") // To remove false-positives, add more Apps to this array
  // ---------- Fix for SuccessBlock to also consider IPv6
  | extend SuccessIPv6Block = strcat(split(IPAddress, ":")[0], ":", split(IPAddress, ":")[1], ":", split(IPAddress, ":")[2], ":", split(IPAddress, ":")[3])
  | extend SuccessIPv4Block = strcat(split(IPAddress, ".")[0], ".", split(IPAddress, ".")[1])
  // ------------------
  | project SuccessLogonTime = TimeGenerated, UserPrincipalName, SuccessIPAddress = IPAddress, SuccessLocation = Location, AppDisplayName, SuccessIPBlock = iff(IPAddress contains ":", strcat(split(IPAddress, ":")[0], ":", split(IPAddress, ":")[1]), strcat(split(IPAddress, ".")[0], ".", split(IPAddress, ".")[1])), Type
  | join kind= inner (
      table(tableName)
      | where ResultType !in ("0", "50140")
      | where ResultDescription !~ "Other"
      | where AppDisplayName !in ("Office 365 Exchange Online", "Skype for Business Online")
      | project FailedLogonTime = TimeGenerated, UserPrincipalName, FailedIPAddress = IPAddress, FailedLocation = Location, AppDisplayName, ResultType, ResultDescription, Type 
  ) on UserPrincipalName, AppDisplayName
  | where SuccessLogonTime < FailedLogonTime and FailedLogonTime - SuccessLogonTime <= logonDiff and FailedIPAddress !startswith SuccessIPBlock
  | summarize FailedLogonTime = max(FailedLogonTime), SuccessLogonTime = max(SuccessLogonTime) by UserPrincipalName, SuccessIPAddress, SuccessLocation, AppDisplayName, FailedIPAddress, FailedLocation, ResultType, ResultDescription, Type
  | extend timestamp = SuccessLogonTime
  | extend UserPrincipalName = tolower(UserPrincipalName)};
  let aadSignin = aadFunc("SigninLogs");
  let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
  union isfuzzy=true aadSignin, aadNonInt
  | extend Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])
  // UEBA context below - make sure you have these 2 datatypes, otherwise the query will not work. If so, comment all that is below.
  | 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
  //Below it will be joined with BehaviorAnalytics table to the Failed IP Addresses
  | join kind=leftouter (
      BehaviorAnalytics
      | where ActivityType in ("FailedLogOn", "LogOn")
      | where isnotempty(SourceIPAddress)
      | project UsersInsights, DevicesInsights, ActivityInsights, InvestigationPriority, SourceIPAddress, UserName
      | project-rename FailedIPAddress = SourceIPAddress, Name = UserName
      | summarize
          MaxInvestigationScore = max(InvestigationPriority)  // Only retrieve maximum Investigation Property score for both FailedIP and User
      by FailedIPAddress, Name)
  on FailedIPAddress, Name  // Joining on both IP and User so as to only return context associated with same user
  | extend UEBARiskScore = MaxInvestigationScore
  | project-away *1 // removing duplicate columns post outer join from output
  | where  UEBARiskScore > riskScoreCutoff
  | sort by UEBARiskScore desc  
triggerThreshold: 0
relevantTechniques:
- T1110
- T1078
name: Successful logon from IP and failure from a different IP
severity: Medium
queryPeriod: 1d
description: |
  'Identifies when a user account successfully logs onto an Azure App from one IP and within 10 mins failed to logon to the same App via a different IP (may indicate a malicious attempt at password guessing with known account). 
  UEBA added for context to gather all asoociated information assocaited with IP addressed initiating Faile Logon and affected user. 
  Please note, Failed logons from known IP ranges can be benign depending on the conditional access policies. In case of noisy behavior, consider tuning the source IP ranges after careful consideration'  
status: Available
triggerOperator: gt
id: 02ef8d7e-fc3a-4d86-a457-650fa571d8d2
entityMappings:
- fieldMappings:
  - columnName: UserPrincipalName
    identifier: FullName
  - columnName: Name
    identifier: Name
  - columnName: UPNSuffix
    identifier: UPNSuffix
  entityType: Account
- fieldMappings:
  - columnName: SuccessIPAddress
    identifier: Address
  entityType: IP
- fieldMappings:
  - columnName: FailedIPAddress
    identifier: Address
  entityType: IP
requiredDataConnectors:
- connectorId: AzureActiveDirectory
  dataTypes:
  - SigninLogs
- connectorId: AzureActiveDirectory
  dataTypes:
  - AADNonInteractiveUserSignInLogs
- connectorId: BehaviorAnalytics
  dataTypes:
  - BehaviorAnalytics
- connectorId: BehaviorAnalytics
  dataTypes:
  - IdentityInfo
kind: Scheduled
tactics:
- CredentialAccess
- InitialAccess
queryFrequency: 1d
version: 2.1.11
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Entra ID/Analytic Rules/SuccessThenFail_DiffIP_SameUserandApp.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/02ef8d7e-fc3a-4d86-a457-650fa571d8d2')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/02ef8d7e-fc3a-4d86-a457-650fa571d8d2')]",
      "properties": {
        "alertRuleTemplateName": "02ef8d7e-fc3a-4d86-a457-650fa571d8d2",
        "customDetails": null,
        "description": "'Identifies when a user account successfully logs onto an Azure App from one IP and within 10 mins failed to logon to the same App via a different IP (may indicate a malicious attempt at password guessing with known account). \nUEBA added for context to gather all asoociated information assocaited with IP addressed initiating Faile Logon and affected user. \nPlease note, Failed logons from known IP ranges can be benign depending on the conditional access policies. In case of noisy behavior, consider tuning the source IP ranges after careful consideration'\n",
        "displayName": "Successful logon from IP and failure from a different IP",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "UserPrincipalName",
                "identifier": "FullName"
              },
              {
                "columnName": "Name",
                "identifier": "Name"
              },
              {
                "columnName": "UPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "SuccessIPAddress",
                "identifier": "Address"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "FailedIPAddress",
                "identifier": "Address"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Entra ID/Analytic Rules/SuccessThenFail_DiffIP_SameUserandApp.yaml",
        "query": "let riskScoreCutoff = 3; //Adjust this score threshold based on volume of results. Activities identified as the most abnormal receive the highest scores (on a scale of 0-10)\nlet logonDiff = 10m; \nlet aadFunc = (tableName:string)\n{ \ntable(tableName)\n| where ResultType == \"0\"\n| where AppDisplayName !in (\"Office 365 Exchange Online\", \"Skype for Business Online\") // To remove false-positives, add more Apps to this array\n// ---------- Fix for SuccessBlock to also consider IPv6\n| extend SuccessIPv6Block = strcat(split(IPAddress, \":\")[0], \":\", split(IPAddress, \":\")[1], \":\", split(IPAddress, \":\")[2], \":\", split(IPAddress, \":\")[3])\n| extend SuccessIPv4Block = strcat(split(IPAddress, \".\")[0], \".\", split(IPAddress, \".\")[1])\n// ------------------\n| project SuccessLogonTime = TimeGenerated, UserPrincipalName, SuccessIPAddress = IPAddress, SuccessLocation = Location, AppDisplayName, SuccessIPBlock = iff(IPAddress contains \":\", strcat(split(IPAddress, \":\")[0], \":\", split(IPAddress, \":\")[1]), strcat(split(IPAddress, \".\")[0], \".\", split(IPAddress, \".\")[1])), Type\n| join kind= inner (\n    table(tableName)\n    | where ResultType !in (\"0\", \"50140\")\n    | where ResultDescription !~ \"Other\"\n    | where AppDisplayName !in (\"Office 365 Exchange Online\", \"Skype for Business Online\")\n    | project FailedLogonTime = TimeGenerated, UserPrincipalName, FailedIPAddress = IPAddress, FailedLocation = Location, AppDisplayName, ResultType, ResultDescription, Type \n) on UserPrincipalName, AppDisplayName\n| where SuccessLogonTime < FailedLogonTime and FailedLogonTime - SuccessLogonTime <= logonDiff and FailedIPAddress !startswith SuccessIPBlock\n| summarize FailedLogonTime = max(FailedLogonTime), SuccessLogonTime = max(SuccessLogonTime) by UserPrincipalName, SuccessIPAddress, SuccessLocation, AppDisplayName, FailedIPAddress, FailedLocation, ResultType, ResultDescription, Type\n| extend timestamp = SuccessLogonTime\n| extend UserPrincipalName = tolower(UserPrincipalName)};\nlet aadSignin = aadFunc(\"SigninLogs\");\nlet aadNonInt = aadFunc(\"AADNonInteractiveUserSignInLogs\");\nunion isfuzzy=true aadSignin, aadNonInt\n| extend Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])\n// UEBA context below - make sure you have these 2 datatypes, otherwise the query will not work. If so, comment all that is below.\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//Below it will be joined with BehaviorAnalytics table to the Failed IP Addresses\n| join kind=leftouter (\n    BehaviorAnalytics\n    | where ActivityType in (\"FailedLogOn\", \"LogOn\")\n    | where isnotempty(SourceIPAddress)\n    | project UsersInsights, DevicesInsights, ActivityInsights, InvestigationPriority, SourceIPAddress, UserName\n    | project-rename FailedIPAddress = SourceIPAddress, Name = UserName\n    | summarize\n        MaxInvestigationScore = max(InvestigationPriority)  // Only retrieve maximum Investigation Property score for both FailedIP and User\n    by FailedIPAddress, Name)\non FailedIPAddress, Name  // Joining on both IP and User so as to only return context associated with same user\n| extend UEBARiskScore = MaxInvestigationScore\n| project-away *1 // removing duplicate columns post outer join from output\n| where  UEBARiskScore > riskScoreCutoff\n| sort by UEBARiskScore desc\n",
        "queryFrequency": "P1D",
        "queryPeriod": "P1D",
        "severity": "Medium",
        "status": "Available",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "CredentialAccess",
          "InitialAccess"
        ],
        "techniques": [
          "T1078",
          "T1110"
        ],
        "templateVersion": "2.1.11",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}