Failed AzureAD logons but success logon to AWS Console

//Adjust this threshold to fit your environment
let signin_threshold = 5;
//Make a list of IPs with AAD signin failures above our threshold
let aadFunc = (tableName:string){
let Suspicious_signins =
table(tableName)
| where ResultType !in ("0", "50125", "50140")
| where IPAddress !in ("127.0.0.1", "::1")
| summarize count() by IPAddress
| where count_ >  signin_threshold
| summarize make_set(IPAddress);
Suspicious_signins
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
let Suspicious_signins =
union isfuzzy=true aadSignin, aadNonInt
| summarize make_set(set_IPAddress);
//See if any of those IPs have sucessfully logged into the AWS console
AWSCloudTrail
| where EventName =~ "ConsoleLogin"
| extend LoginResult = tostring(parse_json(ResponseElements).ConsoleLogin)
| where LoginResult =~ "Success"
| where SourceIpAddress in (Suspicious_signins)
| extend Reason = "Multiple failed AAD logins from IP address"
| extend MFAUsed = tostring(parse_json(AdditionalEventData).MFAUsed)
| extend UserIdentityArn = iif(isempty(UserIdentityArn), tostring(parse_json(Resources)[0].ARN), UserIdentityArn)
| extend UserName = tostring(split(UserIdentityArn, '/')[-1])
| extend AccountName = case( UserIdentityPrincipalid == "Anonymous", "Anonymous", isempty(UserIdentityUserName), UserName, UserIdentityUserName)
| extend AccountName = iif(AccountName contains "@", tostring(split(AccountName, '@', 0)[0]), AccountName),
  AccountUPNSuffix = iif(AccountName contains "@", tostring(split(AccountName, '@', 1)[0]), "")
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by Reason, LoginResult, EventTypeName, UserIdentityType, RecipientAccountId, AccountName, AccountUPNSuffix, AWSRegion, SourceIpAddress, UserAgent, MFAUsed
| extend timestamp = StartTime

requiredDataConnectors:
- connectorId: AzureActiveDirectory
  dataTypes:
  - SigninLogs
- connectorId: AzureActiveDirectory
  dataTypes:
  - AADNonInteractiveUserSignInLogs
- connectorId: AWS
  dataTypes:
  - AWSCloudTrail
triggerOperator: gt
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Detections/MultipleDataSources/AADAWSConsoleCorrelation.yaml
version: 1.0.5
entityMappings:
- fieldMappings:
  - identifier: Name
    columnName: AccountName
  - identifier: UPNSuffix
    columnName: AccountUPNSuffix
  - identifier: CloudAppAccountId
    columnName: RecipientAccountId
  entityType: Account
- fieldMappings:
  - identifier: Address
    columnName: SourceIpAddress
  entityType: IP
queryPeriod: 1d
severity: Medium
relevantTechniques:
- T1078
- T1110
tactics:
- InitialAccess
- CredentialAccess
kind: Scheduled
queryFrequency: 1d
description: |
  'Identifies a list of IP addresses with a minimum number (defualt of 5) of failed logon attempts to Microsoft Entra ID.
  Uses that list to identify any successful AWS Console logons from these IPs within the same timeframe.'  
query: |
  //Adjust this threshold to fit your environment
  let signin_threshold = 5;
  //Make a list of IPs with AAD signin failures above our threshold
  let aadFunc = (tableName:string){
  let Suspicious_signins =
  table(tableName)
  | where ResultType !in ("0", "50125", "50140")
  | where IPAddress !in ("127.0.0.1", "::1")
  | summarize count() by IPAddress
  | where count_ >  signin_threshold
  | summarize make_set(IPAddress);
  Suspicious_signins
  };
  let aadSignin = aadFunc("SigninLogs");
  let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
  let Suspicious_signins =
  union isfuzzy=true aadSignin, aadNonInt
  | summarize make_set(set_IPAddress);
  //See if any of those IPs have sucessfully logged into the AWS console
  AWSCloudTrail
  | where EventName =~ "ConsoleLogin"
  | extend LoginResult = tostring(parse_json(ResponseElements).ConsoleLogin)
  | where LoginResult =~ "Success"
  | where SourceIpAddress in (Suspicious_signins)
  | extend Reason = "Multiple failed AAD logins from IP address"
  | extend MFAUsed = tostring(parse_json(AdditionalEventData).MFAUsed)
  | extend UserIdentityArn = iif(isempty(UserIdentityArn), tostring(parse_json(Resources)[0].ARN), UserIdentityArn)
  | extend UserName = tostring(split(UserIdentityArn, '/')[-1])
  | extend AccountName = case( UserIdentityPrincipalid == "Anonymous", "Anonymous", isempty(UserIdentityUserName), UserName, UserIdentityUserName)
  | extend AccountName = iif(AccountName contains "@", tostring(split(AccountName, '@', 0)[0]), AccountName),
    AccountUPNSuffix = iif(AccountName contains "@", tostring(split(AccountName, '@', 1)[0]), "")
  | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by Reason, LoginResult, EventTypeName, UserIdentityType, RecipientAccountId, AccountName, AccountUPNSuffix, AWSRegion, SourceIpAddress, UserAgent, MFAUsed
  | extend timestamp = StartTime  
id: 643c2025-9604-47c5-833f-7b4b9378a1f5
metadata:
  author:
    name: Microsoft Security Research
  source:
    kind: Community
  support:
    tier: Community
  categories:
    domains:
    - Security - Others
    - Identity
triggerThreshold: 0
name: Failed AzureAD logons but success logon to AWS Console

{
  "$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/643c2025-9604-47c5-833f-7b4b9378a1f5')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/643c2025-9604-47c5-833f-7b4b9378a1f5')]",
      "properties": {
        "alertRuleTemplateName": "643c2025-9604-47c5-833f-7b4b9378a1f5",
        "customDetails": null,
        "description": "'Identifies a list of IP addresses with a minimum number (defualt of 5) of failed logon attempts to Microsoft Entra ID.\nUses that list to identify any successful AWS Console logons from these IPs within the same timeframe.'\n",
        "displayName": "Failed AzureAD logons but success logon to AWS Console",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "AccountName",
                "identifier": "Name"
              },
              {
                "columnName": "AccountUPNSuffix",
                "identifier": "UPNSuffix"
              },
              {
                "columnName": "RecipientAccountId",
                "identifier": "CloudAppAccountId"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "SourceIpAddress",
                "identifier": "Address"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Detections/MultipleDataSources/AADAWSConsoleCorrelation.yaml",
        "query": "//Adjust this threshold to fit your environment\nlet signin_threshold = 5;\n//Make a list of IPs with AAD signin failures above our threshold\nlet aadFunc = (tableName:string){\nlet Suspicious_signins =\ntable(tableName)\n| where ResultType !in (\"0\", \"50125\", \"50140\")\n| where IPAddress !in (\"127.0.0.1\", \"::1\")\n| summarize count() by IPAddress\n| where count_ >  signin_threshold\n| summarize make_set(IPAddress);\nSuspicious_signins\n};\nlet aadSignin = aadFunc(\"SigninLogs\");\nlet aadNonInt = aadFunc(\"AADNonInteractiveUserSignInLogs\");\nlet Suspicious_signins =\nunion isfuzzy=true aadSignin, aadNonInt\n| summarize make_set(set_IPAddress);\n//See if any of those IPs have sucessfully logged into the AWS console\nAWSCloudTrail\n| where EventName =~ \"ConsoleLogin\"\n| extend LoginResult = tostring(parse_json(ResponseElements).ConsoleLogin)\n| where LoginResult =~ \"Success\"\n| where SourceIpAddress in (Suspicious_signins)\n| extend Reason = \"Multiple failed AAD logins from IP address\"\n| extend MFAUsed = tostring(parse_json(AdditionalEventData).MFAUsed)\n| extend UserIdentityArn = iif(isempty(UserIdentityArn), tostring(parse_json(Resources)[0].ARN), UserIdentityArn)\n| extend UserName = tostring(split(UserIdentityArn, '/')[-1])\n| extend AccountName = case( UserIdentityPrincipalid == \"Anonymous\", \"Anonymous\", isempty(UserIdentityUserName), UserName, UserIdentityUserName)\n| extend AccountName = iif(AccountName contains \"@\", tostring(split(AccountName, '@', 0)[0]), AccountName),\n  AccountUPNSuffix = iif(AccountName contains \"@\", tostring(split(AccountName, '@', 1)[0]), \"\")\n| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by Reason, LoginResult, EventTypeName, UserIdentityType, RecipientAccountId, AccountName, AccountUPNSuffix, AWSRegion, SourceIpAddress, UserAgent, MFAUsed\n| extend timestamp = StartTime\n",
        "queryFrequency": "P1D",
        "queryPeriod": "P1D",
        "severity": "Medium",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "CredentialAccess",
          "InitialAccess"
        ],
        "techniques": [
          "T1078",
          "T1110"
        ],
        "templateVersion": "1.0.5",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}