Multiple admin membership removals from newly created admin

let lookback = 7d; 
let timeframe = 1h; 
let GlobalAdminsRemoved = AuditLogs 
| where TimeGenerated > ago(timeframe) 
| where Category =~ "RoleManagement" 
| where AADOperationType in ("Unassign", "RemoveEligibleRole") 
| where ActivityDisplayName has_any ("Remove member from role", "Remove eligible member from role") 
| mv-apply TargetResource = TargetResources on 
  (
      where TargetResource.type =~ "User"
      | extend Target = tostring(TargetResource.userPrincipalName),
               props = TargetResource.modifiedProperties
  )
| mv-apply Property = props on 
      (
          where Property.displayName =~ "Role.DisplayName"
          | extend RoleName = trim('"',tostring(Property.oldValue))
      )
| where RoleName =~ "Global Administrator" // Add other Privileged role if applicable
| extend InitiatingAppName = tostring(InitiatedBy.app.displayName)
| extend InitiatingAppServicePrincipalId = tostring(InitiatedBy.app.servicePrincipalId)
| extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
| extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
| extend InitiatingIpAddress = tostring(iff(isnotempty(InitiatedBy.user.ipAddress), InitiatedBy.user.ipAddress, InitiatedBy.app.ipAddress))
| extend Initiator = iif(isnotempty(InitiatingAppName), InitiatingAppName, InitiatingUserPrincipalName) 
| where Initiator != "MS-PIM"  // Filtering PIM events  
| summarize RemovedGlobalAdminTime = max(TimeGenerated), TargetAdmins = make_set(Target,100) by OperationName, RoleName, Initiator, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIpAddress, Result; 
let GlobalAdminsAdded = AuditLogs 
| where TimeGenerated > ago(lookback) 
| where Category =~ "RoleManagement" 
| where AADOperationType in ("Assign", "AssignEligibleRole") 
| where ActivityDisplayName has_any ("Add eligible member to role", "Add member to role") and Result == "success" 
| mv-apply TargetResource = TargetResources on 
  (
      where TargetResource.type =~ "User"
      | extend Target = tostring(TargetResource.userPrincipalName),
               props = TargetResource.modifiedProperties
  )
| mv-apply Property = props on 
      (
          where Property.displayName =~ "Role.DisplayName"
          | extend RoleName = trim('"',tostring(Property.newValue))
      )
| where RoleName =~ "Global Administrator" // Add other Privileged role if applicable
| extend InitiatingAppName = tostring(InitiatedBy.app.displayName)
| extend Initiator = iif(isnotempty(InitiatingAppName), InitiatingAppName, tostring(InitiatedBy.user.userPrincipalName)) 
| where Initiator != "MS-PIM"  // Filtering PIM events 
| summarize AddedGlobalAdminTime = max(TimeGenerated) by OperationName, RoleName, Target, Initiator, Result;
GlobalAdminsAdded 
| join kind= inner GlobalAdminsRemoved on $left.Target == $right.Initiator 
| where AddedGlobalAdminTime < RemovedGlobalAdminTime 
| extend NoofAdminsRemoved = array_length(TargetAdmins) 
| where NoofAdminsRemoved > 1
| project AddedGlobalAdminTime, Initiator, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIpAddress, Target, RemovedGlobalAdminTime, TargetAdmins, NoofAdminsRemoved
| extend TargetName = tostring(split(Target,'@',0)[0]), TargetUPNSuffix = tostring(split(Target,'@',1)[0])
| extend InitiatedByName = tostring(split(InitiatingUserPrincipalName,'@',0)[0]), InitiatedByUPNSuffix = tostring(split(InitiatingUserPrincipalName,'@',1)[0])

triggerOperator: gt
queryFrequency: 1h
description: |
  'This query detects when newly created Global admin removes multiple existing global admins which can be an attempt by adversaries to lock down organization and retain sole access. 
   Investigate reasoning and intention of multiple membership removal by new Global admins and take necessary actions accordingly.'  
status: Available
kind: Scheduled
triggerThreshold: 0
requiredDataConnectors:
- connectorId: AzureActiveDirectory
  dataTypes:
  - AuditLogs
version: 1.0.2
queryPeriod: 7d
name: Multiple admin membership removals from newly created admin.
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Entra ID/Analytic Rules/MultipleAdmin_membership_removals_from_NewAdmin.yaml
id: cda5928c-2c1e-4575-9dfa-07568bc27a4f
tags:
- DEV-0537
tactics:
- Impact
relevantTechniques:
- T1531
severity: Medium
entityMappings:
- fieldMappings:
  - identifier: FullName
    columnName: Target
  - identifier: Name
    columnName: TargetName
  - identifier: UPNSuffix
    columnName: TargetUPNSuffix
  entityType: Account
- fieldMappings:
  - identifier: FullName
    columnName: InitiatingUserPrincipalName
  - identifier: Name
    columnName: InitiatedByName
  - identifier: UPNSuffix
    columnName: InitiatedByUPNSuffix
  entityType: Account
- fieldMappings:
  - identifier: AadUserId
    columnName: InitiatingAadUserId
  entityType: Account
- fieldMappings:
  - identifier: AadUserId
    columnName: InitiatingAppServicePrincipalId
  entityType: Account
- fieldMappings:
  - identifier: Address
    columnName: InitiatingIpAddress
  entityType: IP
query: |
  let lookback = 7d; 
  let timeframe = 1h; 
  let GlobalAdminsRemoved = AuditLogs 
  | where TimeGenerated > ago(timeframe) 
  | where Category =~ "RoleManagement" 
  | where AADOperationType in ("Unassign", "RemoveEligibleRole") 
  | where ActivityDisplayName has_any ("Remove member from role", "Remove eligible member from role") 
  | mv-apply TargetResource = TargetResources on 
    (
        where TargetResource.type =~ "User"
        | extend Target = tostring(TargetResource.userPrincipalName),
                 props = TargetResource.modifiedProperties
    )
  | mv-apply Property = props on 
        (
            where Property.displayName =~ "Role.DisplayName"
            | extend RoleName = trim('"',tostring(Property.oldValue))
        )
  | where RoleName =~ "Global Administrator" // Add other Privileged role if applicable
  | extend InitiatingAppName = tostring(InitiatedBy.app.displayName)
  | extend InitiatingAppServicePrincipalId = tostring(InitiatedBy.app.servicePrincipalId)
  | extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
  | extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
  | extend InitiatingIpAddress = tostring(iff(isnotempty(InitiatedBy.user.ipAddress), InitiatedBy.user.ipAddress, InitiatedBy.app.ipAddress))
  | extend Initiator = iif(isnotempty(InitiatingAppName), InitiatingAppName, InitiatingUserPrincipalName) 
  | where Initiator != "MS-PIM"  // Filtering PIM events  
  | summarize RemovedGlobalAdminTime = max(TimeGenerated), TargetAdmins = make_set(Target,100) by OperationName, RoleName, Initiator, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIpAddress, Result; 
  let GlobalAdminsAdded = AuditLogs 
  | where TimeGenerated > ago(lookback) 
  | where Category =~ "RoleManagement" 
  | where AADOperationType in ("Assign", "AssignEligibleRole") 
  | where ActivityDisplayName has_any ("Add eligible member to role", "Add member to role") and Result == "success" 
  | mv-apply TargetResource = TargetResources on 
    (
        where TargetResource.type =~ "User"
        | extend Target = tostring(TargetResource.userPrincipalName),
                 props = TargetResource.modifiedProperties
    )
  | mv-apply Property = props on 
        (
            where Property.displayName =~ "Role.DisplayName"
            | extend RoleName = trim('"',tostring(Property.newValue))
        )
  | where RoleName =~ "Global Administrator" // Add other Privileged role if applicable
  | extend InitiatingAppName = tostring(InitiatedBy.app.displayName)
  | extend Initiator = iif(isnotempty(InitiatingAppName), InitiatingAppName, tostring(InitiatedBy.user.userPrincipalName)) 
  | where Initiator != "MS-PIM"  // Filtering PIM events 
  | summarize AddedGlobalAdminTime = max(TimeGenerated) by OperationName, RoleName, Target, Initiator, Result;
  GlobalAdminsAdded 
  | join kind= inner GlobalAdminsRemoved on $left.Target == $right.Initiator 
  | where AddedGlobalAdminTime < RemovedGlobalAdminTime 
  | extend NoofAdminsRemoved = array_length(TargetAdmins) 
  | where NoofAdminsRemoved > 1
  | project AddedGlobalAdminTime, Initiator, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIpAddress, Target, RemovedGlobalAdminTime, TargetAdmins, NoofAdminsRemoved
  | extend TargetName = tostring(split(Target,'@',0)[0]), TargetUPNSuffix = tostring(split(Target,'@',1)[0])
  | extend InitiatedByName = tostring(split(InitiatingUserPrincipalName,'@',0)[0]), InitiatedByUPNSuffix = tostring(split(InitiatingUserPrincipalName,'@',1)[0])  

{
  "$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/cda5928c-2c1e-4575-9dfa-07568bc27a4f')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/cda5928c-2c1e-4575-9dfa-07568bc27a4f')]",
      "properties": {
        "alertRuleTemplateName": "cda5928c-2c1e-4575-9dfa-07568bc27a4f",
        "customDetails": null,
        "description": "'This query detects when newly created Global admin removes multiple existing global admins which can be an attempt by adversaries to lock down organization and retain sole access. \n Investigate reasoning and intention of multiple membership removal by new Global admins and take necessary actions accordingly.'\n",
        "displayName": "Multiple admin membership removals from newly created admin.",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "Target",
                "identifier": "FullName"
              },
              {
                "columnName": "TargetName",
                "identifier": "Name"
              },
              {
                "columnName": "TargetUPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "InitiatingUserPrincipalName",
                "identifier": "FullName"
              },
              {
                "columnName": "InitiatedByName",
                "identifier": "Name"
              },
              {
                "columnName": "InitiatedByUPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "InitiatingAadUserId",
                "identifier": "AadUserId"
              }
            ]
          },
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "InitiatingAppServicePrincipalId",
                "identifier": "AadUserId"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "InitiatingIpAddress",
                "identifier": "Address"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Entra ID/Analytic Rules/MultipleAdmin_membership_removals_from_NewAdmin.yaml",
        "query": "let lookback = 7d; \nlet timeframe = 1h; \nlet GlobalAdminsRemoved = AuditLogs \n| where TimeGenerated > ago(timeframe) \n| where Category =~ \"RoleManagement\" \n| where AADOperationType in (\"Unassign\", \"RemoveEligibleRole\") \n| where ActivityDisplayName has_any (\"Remove member from role\", \"Remove eligible member from role\") \n| mv-apply TargetResource = TargetResources on \n  (\n      where TargetResource.type =~ \"User\"\n      | extend Target = tostring(TargetResource.userPrincipalName),\n               props = TargetResource.modifiedProperties\n  )\n| mv-apply Property = props on \n      (\n          where Property.displayName =~ \"Role.DisplayName\"\n          | extend RoleName = trim('\"',tostring(Property.oldValue))\n      )\n| where RoleName =~ \"Global Administrator\" // Add other Privileged role if applicable\n| extend InitiatingAppName = tostring(InitiatedBy.app.displayName)\n| extend InitiatingAppServicePrincipalId = tostring(InitiatedBy.app.servicePrincipalId)\n| extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)\n| extend InitiatingAadUserId = tostring(InitiatedBy.user.id)\n| extend InitiatingIpAddress = tostring(iff(isnotempty(InitiatedBy.user.ipAddress), InitiatedBy.user.ipAddress, InitiatedBy.app.ipAddress))\n| extend Initiator = iif(isnotempty(InitiatingAppName), InitiatingAppName, InitiatingUserPrincipalName) \n| where Initiator != \"MS-PIM\"  // Filtering PIM events  \n| summarize RemovedGlobalAdminTime = max(TimeGenerated), TargetAdmins = make_set(Target,100) by OperationName, RoleName, Initiator, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIpAddress, Result; \nlet GlobalAdminsAdded = AuditLogs \n| where TimeGenerated > ago(lookback) \n| where Category =~ \"RoleManagement\" \n| where AADOperationType in (\"Assign\", \"AssignEligibleRole\") \n| where ActivityDisplayName has_any (\"Add eligible member to role\", \"Add member to role\") and Result == \"success\" \n| mv-apply TargetResource = TargetResources on \n  (\n      where TargetResource.type =~ \"User\"\n      | extend Target = tostring(TargetResource.userPrincipalName),\n               props = TargetResource.modifiedProperties\n  )\n| mv-apply Property = props on \n      (\n          where Property.displayName =~ \"Role.DisplayName\"\n          | extend RoleName = trim('\"',tostring(Property.newValue))\n      )\n| where RoleName =~ \"Global Administrator\" // Add other Privileged role if applicable\n| extend InitiatingAppName = tostring(InitiatedBy.app.displayName)\n| extend Initiator = iif(isnotempty(InitiatingAppName), InitiatingAppName, tostring(InitiatedBy.user.userPrincipalName)) \n| where Initiator != \"MS-PIM\"  // Filtering PIM events \n| summarize AddedGlobalAdminTime = max(TimeGenerated) by OperationName, RoleName, Target, Initiator, Result;\nGlobalAdminsAdded \n| join kind= inner GlobalAdminsRemoved on $left.Target == $right.Initiator \n| where AddedGlobalAdminTime < RemovedGlobalAdminTime \n| extend NoofAdminsRemoved = array_length(TargetAdmins) \n| where NoofAdminsRemoved > 1\n| project AddedGlobalAdminTime, Initiator, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingUserPrincipalName, InitiatingAadUserId, InitiatingIpAddress, Target, RemovedGlobalAdminTime, TargetAdmins, NoofAdminsRemoved\n| extend TargetName = tostring(split(Target,'@',0)[0]), TargetUPNSuffix = tostring(split(Target,'@',1)[0])\n| extend InitiatedByName = tostring(split(InitiatingUserPrincipalName,'@',0)[0]), InitiatedByUPNSuffix = tostring(split(InitiatingUserPrincipalName,'@',1)[0])\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P7D",
        "severity": "Medium",
        "status": "Available",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Impact"
        ],
        "tags": [
          "DEV-0537"
        ],
        "techniques": [
          "T1531"
        ],
        "templateVersion": "1.0.2",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}