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

triggerOperator: gt
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.'  
version: 1.0.5
kind: Scheduled
triggerThreshold: 0
requiredDataConnectors:
- connectorId: AzureActiveDirectory
  dataTypes:
  - SigninLogs
- connectorId: AzureActiveDirectory
  dataTypes:
  - AADNonInteractiveUserSignInLogs
- connectorId: AWS
  dataTypes:
  - AWSCloudTrail
queryPeriod: 1d
name: Failed AzureAD logons but success logon to AWS Console
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Detections/MultipleDataSources/AADAWSConsoleCorrelation.yaml
id: 643c2025-9604-47c5-833f-7b4b9378a1f5
tactics:
- InitialAccess
- CredentialAccess
metadata:
  source:
    kind: Community
  author:
    name: Microsoft Security Research
  categories:
    domains:
    - Security - Others
    - Identity
  support:
    tier: Community
relevantTechniques:
- T1078
- T1110
severity: Medium
entityMappings:
- fieldMappings:
  - identifier: Name
    columnName: AccountName
  - identifier: UPNSuffix
    columnName: AccountUPNSuffix
  - identifier: CloudAppAccountId
    columnName: RecipientAccountId
  entityType: Account
- fieldMappings:
  - identifier: Address
    columnName: SourceIpAddress
  entityType: IP
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  

{
  "$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"
    }
  ]
}