Failed login attempts to Azure Portal

let timeRange = 1d;
let lookBack = 7d;
let threshold_Failed = 5;
let threshold_FailedwithSingleIP = 20;
let threshold_IPAddressCount = 2;
let isGUID = "[0-9a-z]{8}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{12}";
let aadFunc = (tableName:string){
let azPortalSignins = materialize(table(tableName)
| where TimeGenerated >= ago(lookBack)
// Azure Portal only
| where AppDisplayName =~ "Azure Portal")
;
let successPortalSignins = azPortalSignins
| where TimeGenerated >= ago(timeRange)
// Azure Portal only and exclude non-failure Result Types
| where ResultType in ("0", "50125", "50140")
// Tagging identities not resolved to friendly names
//| extend Unresolved = iff(Identity matches regex isGUID, true, false)
| distinct TimeGenerated, UserPrincipalName
;
let failPortalSignins = azPortalSignins
| where TimeGenerated >= ago(timeRange)
// Azure Portal only and exclude non-failure Result Types
| where ResultType !in ("0", "50125", "50140", "70044", "70043")
// Tagging identities not resolved to friendly names
| extend Unresolved = iff(Identity matches regex isGUID, true, false)
;
// Verify there is no success for the same connection attempt after the fail
let failnoSuccess = failPortalSignins | join kind= leftouter (
   successPortalSignins
) on UserPrincipalName
| where TimeGenerated > TimeGenerated1 or isempty(TimeGenerated1)
| project-away TimeGenerated1, UserPrincipalName1
;
// Lookup up resolved identities from last 7 days
let identityLookup = azPortalSignins
| where TimeGenerated >= ago(lookBack)
| where not(Identity matches regex isGUID)
| summarize by UserId, lu_UserDisplayName = UserDisplayName, lu_UserPrincipalName = UserPrincipalName;
// Join resolved names to unresolved list from portal signins
let unresolvedNames = failnoSuccess | where Unresolved == true | join kind= inner (
   identityLookup
) on UserId
| extend UserDisplayName = lu_UserDisplayName, UserPrincipalName = lu_UserPrincipalName
| project-away lu_UserDisplayName, lu_UserPrincipalName;
// Join Signins that had resolved names with list of unresolved that now have a resolved name
let u_azPortalSignins = failnoSuccess | where Unresolved == false | union unresolvedNames;
u_azPortalSignins
| extend DeviceDetail = todynamic(DeviceDetail), Status = todynamic(DeviceDetail), LocationDetails = todynamic(LocationDetails)
| extend Status = strcat(ResultType, ": ", ResultDescription), OS = tostring(DeviceDetail.operatingSystem), Browser = tostring(DeviceDetail.browser)
| extend State = tostring(LocationDetails.state), City = tostring(LocationDetails.city), Region = tostring(LocationDetails.countryOrRegion)
| extend FullLocation = strcat(Region,'|', State, '|', City)  
| summarize TimeGenerated = make_list(TimeGenerated,100), Status = make_list(Status,100), IPAddresses = make_list(IPAddress,100), IPAddressCount = dcount(IPAddress), FailedLogonCount = count()
by UserPrincipalName, UserId, UserDisplayName, AppDisplayName, Browser, OS, FullLocation, Type
| mvexpand TimeGenerated, IPAddresses, Status
| extend TimeGenerated = todatetime(tostring(TimeGenerated)), IPAddress = tostring(IPAddresses), Status = tostring(Status)
| project-away IPAddresses
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by UserPrincipalName, UserId, UserDisplayName, Status, FailedLogonCount, IPAddress, IPAddressCount, AppDisplayName, Browser, OS, FullLocation, Type
| where (IPAddressCount >= threshold_IPAddressCount and FailedLogonCount >= threshold_Failed) or FailedLogonCount >= threshold_FailedwithSingleIP
| extend Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
union isfuzzy=true aadSignin, aadNonInt

name: Failed login attempts to Azure Portal
query: |
  let timeRange = 1d;
  let lookBack = 7d;
  let threshold_Failed = 5;
  let threshold_FailedwithSingleIP = 20;
  let threshold_IPAddressCount = 2;
  let isGUID = "[0-9a-z]{8}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{12}";
  let aadFunc = (tableName:string){
  let azPortalSignins = materialize(table(tableName)
  | where TimeGenerated >= ago(lookBack)
  // Azure Portal only
  | where AppDisplayName =~ "Azure Portal")
  ;
  let successPortalSignins = azPortalSignins
  | where TimeGenerated >= ago(timeRange)
  // Azure Portal only and exclude non-failure Result Types
  | where ResultType in ("0", "50125", "50140")
  // Tagging identities not resolved to friendly names
  //| extend Unresolved = iff(Identity matches regex isGUID, true, false)
  | distinct TimeGenerated, UserPrincipalName
  ;
  let failPortalSignins = azPortalSignins
  | where TimeGenerated >= ago(timeRange)
  // Azure Portal only and exclude non-failure Result Types
  | where ResultType !in ("0", "50125", "50140", "70044", "70043")
  // Tagging identities not resolved to friendly names
  | extend Unresolved = iff(Identity matches regex isGUID, true, false)
  ;
  // Verify there is no success for the same connection attempt after the fail
  let failnoSuccess = failPortalSignins | join kind= leftouter (
     successPortalSignins
  ) on UserPrincipalName
  | where TimeGenerated > TimeGenerated1 or isempty(TimeGenerated1)
  | project-away TimeGenerated1, UserPrincipalName1
  ;
  // Lookup up resolved identities from last 7 days
  let identityLookup = azPortalSignins
  | where TimeGenerated >= ago(lookBack)
  | where not(Identity matches regex isGUID)
  | summarize by UserId, lu_UserDisplayName = UserDisplayName, lu_UserPrincipalName = UserPrincipalName;
  // Join resolved names to unresolved list from portal signins
  let unresolvedNames = failnoSuccess | where Unresolved == true | join kind= inner (
     identityLookup
  ) on UserId
  | extend UserDisplayName = lu_UserDisplayName, UserPrincipalName = lu_UserPrincipalName
  | project-away lu_UserDisplayName, lu_UserPrincipalName;
  // Join Signins that had resolved names with list of unresolved that now have a resolved name
  let u_azPortalSignins = failnoSuccess | where Unresolved == false | union unresolvedNames;
  u_azPortalSignins
  | extend DeviceDetail = todynamic(DeviceDetail), Status = todynamic(DeviceDetail), LocationDetails = todynamic(LocationDetails)
  | extend Status = strcat(ResultType, ": ", ResultDescription), OS = tostring(DeviceDetail.operatingSystem), Browser = tostring(DeviceDetail.browser)
  | extend State = tostring(LocationDetails.state), City = tostring(LocationDetails.city), Region = tostring(LocationDetails.countryOrRegion)
  | extend FullLocation = strcat(Region,'|', State, '|', City)  
  | summarize TimeGenerated = make_list(TimeGenerated,100), Status = make_list(Status,100), IPAddresses = make_list(IPAddress,100), IPAddressCount = dcount(IPAddress), FailedLogonCount = count()
  by UserPrincipalName, UserId, UserDisplayName, AppDisplayName, Browser, OS, FullLocation, Type
  | mvexpand TimeGenerated, IPAddresses, Status
  | extend TimeGenerated = todatetime(tostring(TimeGenerated)), IPAddress = tostring(IPAddresses), Status = tostring(Status)
  | project-away IPAddresses
  | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by UserPrincipalName, UserId, UserDisplayName, Status, FailedLogonCount, IPAddress, IPAddressCount, AppDisplayName, Browser, OS, FullLocation, Type
  | where (IPAddressCount >= threshold_IPAddressCount and FailedLogonCount >= threshold_Failed) or FailedLogonCount >= threshold_FailedwithSingleIP
  | extend Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])
  };
  let aadSignin = aadFunc("SigninLogs");
  let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
  union isfuzzy=true aadSignin, aadNonInt  
description: |
  'Identifies failed login attempts in the Microsoft Entra ID SigninLogs to the Azure Portal.  Many failed logon
  attempts or some failed logon attempts from multiple IPs could indicate a potential brute force attack.
  The following are excluded due to success and non-failure results:
  References: https://docs.microsoft.com/azure/active-directory/reports-monitoring/reference-sign-ins-error-codes
  0 - successful logon
  50125 - Sign-in was interrupted due to a password reset or password registration entry.
  50140 - This error occurred due to 'Keep me signed in' interrupt when the user was signing-in.'  
tactics:
- CredentialAccess
requiredDataConnectors:
- dataTypes:
  - SigninLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - AADNonInteractiveUserSignInLogs
  connectorId: AzureActiveDirectory
queryPeriod: 7d
queryFrequency: 1d
status: Available
triggerThreshold: 0
id: 223db5c1-1bf8-47d8-8806-bed401b356a4
triggerOperator: gt
version: 1.0.6
relevantTechniques:
- T1110
severity: Low
kind: Scheduled
entityMappings:
- entityType: Account
  fieldMappings:
  - identifier: FullName
    columnName: UserPrincipalName
  - identifier: Name
    columnName: Name
  - identifier: UPNSuffix
    columnName: UPNSuffix
- entityType: IP
  fieldMappings:
  - identifier: Address
    columnName: IPAddress
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Entra ID/Analytic Rules/FailedLogonToAzurePortal.yaml

{
  "$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/223db5c1-1bf8-47d8-8806-bed401b356a4')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/223db5c1-1bf8-47d8-8806-bed401b356a4')]",
      "properties": {
        "alertRuleTemplateName": "223db5c1-1bf8-47d8-8806-bed401b356a4",
        "customDetails": null,
        "description": "'Identifies failed login attempts in the Microsoft Entra ID SigninLogs to the Azure Portal.  Many failed logon\nattempts or some failed logon attempts from multiple IPs could indicate a potential brute force attack.\nThe following are excluded due to success and non-failure results:\nReferences: https://docs.microsoft.com/azure/active-directory/reports-monitoring/reference-sign-ins-error-codes\n0 - successful logon\n50125 - Sign-in was interrupted due to a password reset or password registration entry.\n50140 - This error occurred due to 'Keep me signed in' interrupt when the user was signing-in.'\n",
        "displayName": "Failed login attempts to Azure Portal",
        "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/FailedLogonToAzurePortal.yaml",
        "query": "let timeRange = 1d;\nlet lookBack = 7d;\nlet threshold_Failed = 5;\nlet threshold_FailedwithSingleIP = 20;\nlet threshold_IPAddressCount = 2;\nlet isGUID = \"[0-9a-z]{8}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{4}-[0-9a-z]{12}\";\nlet aadFunc = (tableName:string){\nlet azPortalSignins = materialize(table(tableName)\n| where TimeGenerated >= ago(lookBack)\n// Azure Portal only\n| where AppDisplayName =~ \"Azure Portal\")\n;\nlet successPortalSignins = azPortalSignins\n| where TimeGenerated >= ago(timeRange)\n// Azure Portal only and exclude non-failure Result Types\n| where ResultType in (\"0\", \"50125\", \"50140\")\n// Tagging identities not resolved to friendly names\n//| extend Unresolved = iff(Identity matches regex isGUID, true, false)\n| distinct TimeGenerated, UserPrincipalName\n;\nlet failPortalSignins = azPortalSignins\n| where TimeGenerated >= ago(timeRange)\n// Azure Portal only and exclude non-failure Result Types\n| where ResultType !in (\"0\", \"50125\", \"50140\", \"70044\", \"70043\")\n// Tagging identities not resolved to friendly names\n| extend Unresolved = iff(Identity matches regex isGUID, true, false)\n;\n// Verify there is no success for the same connection attempt after the fail\nlet failnoSuccess = failPortalSignins | join kind= leftouter (\n   successPortalSignins\n) on UserPrincipalName\n| where TimeGenerated > TimeGenerated1 or isempty(TimeGenerated1)\n| project-away TimeGenerated1, UserPrincipalName1\n;\n// Lookup up resolved identities from last 7 days\nlet identityLookup = azPortalSignins\n| where TimeGenerated >= ago(lookBack)\n| where not(Identity matches regex isGUID)\n| summarize by UserId, lu_UserDisplayName = UserDisplayName, lu_UserPrincipalName = UserPrincipalName;\n// Join resolved names to unresolved list from portal signins\nlet unresolvedNames = failnoSuccess | where Unresolved == true | join kind= inner (\n   identityLookup\n) on UserId\n| extend UserDisplayName = lu_UserDisplayName, UserPrincipalName = lu_UserPrincipalName\n| project-away lu_UserDisplayName, lu_UserPrincipalName;\n// Join Signins that had resolved names with list of unresolved that now have a resolved name\nlet u_azPortalSignins = failnoSuccess | where Unresolved == false | union unresolvedNames;\nu_azPortalSignins\n| extend DeviceDetail = todynamic(DeviceDetail), Status = todynamic(DeviceDetail), LocationDetails = todynamic(LocationDetails)\n| extend Status = strcat(ResultType, \": \", ResultDescription), OS = tostring(DeviceDetail.operatingSystem), Browser = tostring(DeviceDetail.browser)\n| extend State = tostring(LocationDetails.state), City = tostring(LocationDetails.city), Region = tostring(LocationDetails.countryOrRegion)\n| extend FullLocation = strcat(Region,'|', State, '|', City)  \n| summarize TimeGenerated = make_list(TimeGenerated,100), Status = make_list(Status,100), IPAddresses = make_list(IPAddress,100), IPAddressCount = dcount(IPAddress), FailedLogonCount = count()\nby UserPrincipalName, UserId, UserDisplayName, AppDisplayName, Browser, OS, FullLocation, Type\n| mvexpand TimeGenerated, IPAddresses, Status\n| extend TimeGenerated = todatetime(tostring(TimeGenerated)), IPAddress = tostring(IPAddresses), Status = tostring(Status)\n| project-away IPAddresses\n| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by UserPrincipalName, UserId, UserDisplayName, Status, FailedLogonCount, IPAddress, IPAddressCount, AppDisplayName, Browser, OS, FullLocation, Type\n| where (IPAddressCount >= threshold_IPAddressCount and FailedLogonCount >= threshold_Failed) or FailedLogonCount >= threshold_FailedwithSingleIP\n| extend Name = tostring(split(UserPrincipalName,'@',0)[0]), UPNSuffix = tostring(split(UserPrincipalName,'@',1)[0])\n};\nlet aadSignin = aadFunc(\"SigninLogs\");\nlet aadNonInt = aadFunc(\"AADNonInteractiveUserSignInLogs\");\nunion isfuzzy=true aadSignin, aadNonInt\n",
        "queryFrequency": "P1D",
        "queryPeriod": "P7D",
        "severity": "Low",
        "status": "Available",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "CredentialAccess"
        ],
        "techniques": [
          "T1110"
        ],
        "templateVersion": "1.0.6",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}