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

id: cda5928c-2c1e-4575-9dfa-07568bc27a4f
triggerOperator: gt
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Entra ID/Analytic Rules/MultipleAdmin_membership_removals_from_NewAdmin.yaml
requiredDataConnectors:
- dataTypes:
  - AuditLogs
  connectorId: AzureActiveDirectory
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.'  
severity: Medium
queryPeriod: 7d
kind: Scheduled
tags:
- DEV-0537
tactics:
- Impact
queryFrequency: 1h
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])  
version: 1.0.2
triggerThreshold: 0
name: Multiple admin membership removals from newly created admin.
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
status: Available
relevantTechniques:
- T1531

{
  "$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/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",
        "subTechniques": [],
        "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"
    }
  ]
}