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

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

{
  "$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/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",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "CredentialAccess",
          "InitialAccess"
        ],
        "techniques": [
          "T1078",
          "T1110"
        ],
        "templateVersion": "1.0.5",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}