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

queryFrequency: 1h
requiredDataConnectors:
- connectorId: AzureActiveDirectory
  dataTypes:
  - AuditLogs
tags:
- DEV-0537
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; 
  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])  
entityMappings:
- entityType: Account
  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: IP
  fieldMappings:
  - identifier: Address
    columnName: InitiatingIpAddress
relevantTechniques:
- T1531
name: Multiple admin membership removals from newly created admin.
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
triggerOperator: gt
kind: Scheduled
status: Available
tactics:
- Impact
id: cda5928c-2c1e-4575-9dfa-07568bc27a4f
version: 1.0.2
triggerThreshold: 0

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