Suspicious Login from deleted guest account

let query_frequency = 1h;
let query_period = 1d;
AuditLogs
| where TimeGenerated > ago(query_frequency)
| where Category =~ "UserManagement" and OperationName =~ "Delete user"
| mv-expand TargetResource = TargetResources
| where TargetResource["type"] == "User" and TargetResource["userPrincipalName"] has "#EXT#"
| extend ParsedDeletedUserPrincipalName = extract(@"^[0-9a-f]{32}([^\#]+)\#EXT\#", 1, tostring(TargetResource["userPrincipalName"]))
| extend
    Initiator = iif(isnotempty(InitiatedBy["app"]), tostring(InitiatedBy["app"]["displayName"]), tostring(InitiatedBy["user"]["userPrincipalName"])),
    InitiatorId = iif(isnotempty(InitiatedBy["app"]), tostring(InitiatedBy["app"]["servicePrincipalId"]), tostring(InitiatedBy["user"]["id"])),
    Delete_IPAddress = tostring(InitiatedBy[tostring(bag_keys(InitiatedBy)[0])]["ipAddress"])
| project Delete_TimeGenerated = TimeGenerated, Category, Identity, Initiator, Delete_IPAddress, OperationName, Result, ParsedDeletedUserPrincipalName, InitiatedBy, AdditionalDetails, TargetResources, InitiatorId, CorrelationId
| join kind=inner (
    SigninLogs
    | where TimeGenerated > ago(query_period)
    | where ResultType == 0
    | summarize take_any(*) by UserPrincipalName
    | extend ParsedUserPrincipalName = translate("@", "_", UserPrincipalName)
    | project SigninLogs_TimeGenerated = TimeGenerated, UserPrincipalName, UserDisplayName, ResultType, ResultDescription, IPAddress, LocationDetails, AppDisplayName, ResourceDisplayName, ClientAppUsed, UserAgent, DeviceDetail, UserId, UserType, OriginalRequestId, ParsedUserPrincipalName
    ) on $left.ParsedDeletedUserPrincipalName == $right.ParsedUserPrincipalName
| where SigninLogs_TimeGenerated > Delete_TimeGenerated
| project-away ParsedDeletedUserPrincipalName, ParsedUserPrincipalName
| extend
    AccountName = tostring(split(UserPrincipalName, "@")[0]),
    AccountUPNSuffix = tostring(split(UserPrincipalName, "@")[1])

query: |
  let query_frequency = 1h;
  let query_period = 1d;
  AuditLogs
  | where TimeGenerated > ago(query_frequency)
  | where Category =~ "UserManagement" and OperationName =~ "Delete user"
  | mv-expand TargetResource = TargetResources
  | where TargetResource["type"] == "User" and TargetResource["userPrincipalName"] has "#EXT#"
  | extend ParsedDeletedUserPrincipalName = extract(@"^[0-9a-f]{32}([^\#]+)\#EXT\#", 1, tostring(TargetResource["userPrincipalName"]))
  | extend
      Initiator = iif(isnotempty(InitiatedBy["app"]), tostring(InitiatedBy["app"]["displayName"]), tostring(InitiatedBy["user"]["userPrincipalName"])),
      InitiatorId = iif(isnotempty(InitiatedBy["app"]), tostring(InitiatedBy["app"]["servicePrincipalId"]), tostring(InitiatedBy["user"]["id"])),
      Delete_IPAddress = tostring(InitiatedBy[tostring(bag_keys(InitiatedBy)[0])]["ipAddress"])
  | project Delete_TimeGenerated = TimeGenerated, Category, Identity, Initiator, Delete_IPAddress, OperationName, Result, ParsedDeletedUserPrincipalName, InitiatedBy, AdditionalDetails, TargetResources, InitiatorId, CorrelationId
  | join kind=inner (
      SigninLogs
      | where TimeGenerated > ago(query_period)
      | where ResultType == 0
      | summarize take_any(*) by UserPrincipalName
      | extend ParsedUserPrincipalName = translate("@", "_", UserPrincipalName)
      | project SigninLogs_TimeGenerated = TimeGenerated, UserPrincipalName, UserDisplayName, ResultType, ResultDescription, IPAddress, LocationDetails, AppDisplayName, ResourceDisplayName, ClientAppUsed, UserAgent, DeviceDetail, UserId, UserType, OriginalRequestId, ParsedUserPrincipalName
      ) on $left.ParsedDeletedUserPrincipalName == $right.ParsedUserPrincipalName
  | where SigninLogs_TimeGenerated > Delete_TimeGenerated
  | project-away ParsedDeletedUserPrincipalName, ParsedUserPrincipalName
  | extend
      AccountName = tostring(split(UserPrincipalName, "@")[0]),
      AccountUPNSuffix = tostring(split(UserPrincipalName, "@")[1])  
queryPeriod: 1d
version: 1.0.5
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Detections/MultipleDataSources/SuspiciousLoginfromDeletedExternalIdentities.yaml
severity: Medium
triggerThreshold: 0
id: defe4855-0d33-4362-9557-009237623976
kind: Scheduled
requiredDataConnectors:
- dataTypes:
  - AuditLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - SigninLogs
  connectorId: AzureActiveDirectory
tags:
- GuestorExternalIdentities
name: Suspicious Login from deleted guest account
description: |
  ' This query will detect logins from guest account which was recently deleted. 
  For any successful logins from deleted identities should be investigated further if any existing user accounts have been altered or linked to such identity prior deletion'  
triggerOperator: gt
tactics:
- PrivilegeEscalation
queryFrequency: 1h
metadata:
  categories:
    domains:
    - Security - Others
    - Identity
  author:
    name: Microsoft Security Research
  source:
    kind: Community
  support:
    tier: Community
relevantTechniques:
- T1078.004
entityMappings:
- fieldMappings:
  - identifier: FullName
    columnName: UserPrincipalName
  - identifier: Name
    columnName: AccountName
  - identifier: UPNSuffix
    columnName: AccountUPNSuffix
  entityType: Account
- fieldMappings:
  - identifier: Address
    columnName: IPAddress
  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/defe4855-0d33-4362-9557-009237623976')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/defe4855-0d33-4362-9557-009237623976')]",
      "properties": {
        "alertRuleTemplateName": "defe4855-0d33-4362-9557-009237623976",
        "customDetails": null,
        "description": "' This query will detect logins from guest account which was recently deleted. \nFor any successful logins from deleted identities should be investigated further if any existing user accounts have been altered or linked to such identity prior deletion'\n",
        "displayName": "Suspicious Login from deleted guest account",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "UserPrincipalName",
                "identifier": "FullName"
              },
              {
                "columnName": "AccountName",
                "identifier": "Name"
              },
              {
                "columnName": "AccountUPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "IPAddress",
                "identifier": "Address"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Detections/MultipleDataSources/SuspiciousLoginfromDeletedExternalIdentities.yaml",
        "query": "let query_frequency = 1h;\nlet query_period = 1d;\nAuditLogs\n| where TimeGenerated > ago(query_frequency)\n| where Category =~ \"UserManagement\" and OperationName =~ \"Delete user\"\n| mv-expand TargetResource = TargetResources\n| where TargetResource[\"type\"] == \"User\" and TargetResource[\"userPrincipalName\"] has \"#EXT#\"\n| extend ParsedDeletedUserPrincipalName = extract(@\"^[0-9a-f]{32}([^\\#]+)\\#EXT\\#\", 1, tostring(TargetResource[\"userPrincipalName\"]))\n| extend\n    Initiator = iif(isnotempty(InitiatedBy[\"app\"]), tostring(InitiatedBy[\"app\"][\"displayName\"]), tostring(InitiatedBy[\"user\"][\"userPrincipalName\"])),\n    InitiatorId = iif(isnotempty(InitiatedBy[\"app\"]), tostring(InitiatedBy[\"app\"][\"servicePrincipalId\"]), tostring(InitiatedBy[\"user\"][\"id\"])),\n    Delete_IPAddress = tostring(InitiatedBy[tostring(bag_keys(InitiatedBy)[0])][\"ipAddress\"])\n| project Delete_TimeGenerated = TimeGenerated, Category, Identity, Initiator, Delete_IPAddress, OperationName, Result, ParsedDeletedUserPrincipalName, InitiatedBy, AdditionalDetails, TargetResources, InitiatorId, CorrelationId\n| join kind=inner (\n    SigninLogs\n    | where TimeGenerated > ago(query_period)\n    | where ResultType == 0\n    | summarize take_any(*) by UserPrincipalName\n    | extend ParsedUserPrincipalName = translate(\"@\", \"_\", UserPrincipalName)\n    | project SigninLogs_TimeGenerated = TimeGenerated, UserPrincipalName, UserDisplayName, ResultType, ResultDescription, IPAddress, LocationDetails, AppDisplayName, ResourceDisplayName, ClientAppUsed, UserAgent, DeviceDetail, UserId, UserType, OriginalRequestId, ParsedUserPrincipalName\n    ) on $left.ParsedDeletedUserPrincipalName == $right.ParsedUserPrincipalName\n| where SigninLogs_TimeGenerated > Delete_TimeGenerated\n| project-away ParsedDeletedUserPrincipalName, ParsedUserPrincipalName\n| extend\n    AccountName = tostring(split(UserPrincipalName, \"@\")[0]),\n    AccountUPNSuffix = tostring(split(UserPrincipalName, \"@\")[1])\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P1D",
        "severity": "Medium",
        "subTechniques": [
          "T1078.004"
        ],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "PrivilegeEscalation"
        ],
        "tags": [
          "GuestorExternalIdentities"
        ],
        "techniques": [
          "T1078"
        ],
        "templateVersion": "1.0.5",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}