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])

requiredDataConnectors:
- connectorId: AzureActiveDirectory
  dataTypes:
  - AuditLogs
- connectorId: AzureActiveDirectory
  dataTypes:
  - SigninLogs
relevantTechniques:
- T1078.004
queryFrequency: 1h
id: defe4855-0d33-4362-9557-009237623976
name: Suspicious Login from deleted guest account
severity: Medium
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Detections/MultipleDataSources/SuspiciousLoginfromDeletedExternalIdentities.yaml
queryPeriod: 1d
entityMappings:
- fieldMappings:
  - columnName: UserPrincipalName
    identifier: FullName
  - columnName: AccountName
    identifier: Name
  - columnName: AccountUPNSuffix
    identifier: UPNSuffix
  entityType: Account
- fieldMappings:
  - columnName: IPAddress
    identifier: Address
  entityType: IP
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'  
triggerThreshold: 0
tactics:
- PrivilegeEscalation
tags:
- GuestorExternalIdentities
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])  
kind: Scheduled
triggerOperator: gt
metadata:
  support:
    tier: Community
  author:
    name: Microsoft Security Research
  source:
    kind: Community
  categories:
    domains:
    - Security - Others
    - Identity
version: 1.0.5

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