Unusual Volume of file deletion by users

let relevantOperations = pack_array("FileDeleted", "FileRecycled", "FileDeletedFirstStageRecycleBin", "FileDeletedSecondStageRecycleBin", "FileVersionsAllMinorsRecycled", "FileVersionRecycled", "FileVersionsAllRecycled");
let relevantAppIds = pack_array(int(20892), int(15600)); // App Ids for SharePoint and OneDrive
let timeWindow = 24h;
let timeNow = now();
//
let riskyUsers= // Look for users with risky sign-ins
  AADSignInEventsBeta    
  | where Timestamp between ((timeNow - timeWindow) .. (timeNow))
  | where isnotempty(AccountObjectId) and isnotempty(RequestId) // In AADSignInEventsBeta, the SessionId column has inaccurate data and instead the RequestId has the actual Session identifier
  | where ErrorCode == 0
  | where RiskLevelDuringSignIn >=80
  | project RiskLevelDuringSignIn, AccountObjectId, Timestamp, SessionId=RequestId
  ;
let hasUsers = isnotempty(toscalar(riskyUsers));
//
let deleteEvents = // look for file deletion activity and scope it to risky users
  CloudAppEvents
  | where hasUsers
  | where TimeGenerated between ((timeNow - timeWindow) .. (timeNow))
  | where ApplicationId in (relevantAppIds)
  | where isnotempty(AccountObjectId)
  | where AccountObjectId in (riskyUsers)
  | where ActionType in (relevantOperations)
  | extend SessionId= tostring(RawEventData.AppAccessContext.AADSessionId)
  | where isnotempty(SessionId)
  | project AccountObjectId, AccountDisplayName, ApplicationId, SessionId, ActionType, TimeGenerated, ReportId
  ;   
 //
deleteEvents  
| join kind=leftsemi riskyUsers on AccountObjectId, SessionId
| summarize Count=count() , (Timestamp, ReportId)=arg_min(TimeGenerated, ReportId) by AccountObjectId, AccountDisplayName, ApplicationId, ActionType, Time=bin(TimeGenerated, 5m)
// look for only those scoped users who have generated an increase in file deletion activity.
| summarize TotalCount= countif(Count > 50), (Timestamp, ReportId)=arg_min(Timestamp, ReportId) by AccountObjectId, AccountDisplayName, ApplicationId 
| where TotalCount >= 3
| project AccountObjectId, AccountDisplayName, ApplicationId, TotalCount, ReportId, Timestamp
| extend NTDomain = tostring(split(AccountDisplayName,'\\',0)[0]), Name = tostring(split(AccountDisplayName,'\\',1)[0])

queryPeriod: 1h
entityMappings:
- fieldMappings:
  - columnName: AccountObjectId
    identifier: AadUserId
  entityType: Account
- fieldMappings:
  - columnName: Name
    identifier: Name
  - columnName: NTDomain
    identifier: NTDomain
  entityType: Account
- fieldMappings:
  - columnName: ApplicationId
    identifier: AppId
  entityType: CloudApplication
id: e5f8e196-3544-4a8b-96a9-17c1b6a49710
name: Unusual Volume of file deletion by users
status: Available
description: |
  This query looks for users performing file deletion activities. Spikes in file deletion observed from risky sign-in sessions are flagged here.
  This applies to SharePoint and OneDrive users.
  Audit event and Cloud application identifier references.
  Reference - https://learn.microsoft.com/microsoft-365/compliance/audit-log-activities?view=o365-worldwide
  Reference - https://learn.microsoft.com/azure/sentinel/entities-reference#cloud-application-identifiers  
tactics:
- Impact
customDetails:
  Count: TotalCount
triggerOperator: gt
query: |
  let relevantOperations = pack_array("FileDeleted", "FileRecycled", "FileDeletedFirstStageRecycleBin", "FileDeletedSecondStageRecycleBin", "FileVersionsAllMinorsRecycled", "FileVersionRecycled", "FileVersionsAllRecycled");
  let relevantAppIds = pack_array(int(20892), int(15600)); // App Ids for SharePoint and OneDrive
  let timeWindow = 24h;
  let timeNow = now();
  //
  let riskyUsers= // Look for users with risky sign-ins
    AADSignInEventsBeta    
    | where Timestamp between ((timeNow - timeWindow) .. (timeNow))
    | where isnotempty(AccountObjectId) and isnotempty(RequestId) // In AADSignInEventsBeta, the SessionId column has inaccurate data and instead the RequestId has the actual Session identifier
    | where ErrorCode == 0
    | where RiskLevelDuringSignIn >=80
    | project RiskLevelDuringSignIn, AccountObjectId, Timestamp, SessionId=RequestId
    ;
  let hasUsers = isnotempty(toscalar(riskyUsers));
  //
  let deleteEvents = // look for file deletion activity and scope it to risky users
    CloudAppEvents
    | where hasUsers
    | where TimeGenerated between ((timeNow - timeWindow) .. (timeNow))
    | where ApplicationId in (relevantAppIds)
    | where isnotempty(AccountObjectId)
    | where AccountObjectId in (riskyUsers)
    | where ActionType in (relevantOperations)
    | extend SessionId= tostring(RawEventData.AppAccessContext.AADSessionId)
    | where isnotempty(SessionId)
    | project AccountObjectId, AccountDisplayName, ApplicationId, SessionId, ActionType, TimeGenerated, ReportId
    ;   
   //
  deleteEvents  
  | join kind=leftsemi riskyUsers on AccountObjectId, SessionId
  | summarize Count=count() , (Timestamp, ReportId)=arg_min(TimeGenerated, ReportId) by AccountObjectId, AccountDisplayName, ApplicationId, ActionType, Time=bin(TimeGenerated, 5m)
  // look for only those scoped users who have generated an increase in file deletion activity.
  | summarize TotalCount= countif(Count > 50), (Timestamp, ReportId)=arg_min(Timestamp, ReportId) by AccountObjectId, AccountDisplayName, ApplicationId 
  | where TotalCount >= 3
  | project AccountObjectId, AccountDisplayName, ApplicationId, TotalCount, ReportId, Timestamp
  | extend NTDomain = tostring(split(AccountDisplayName,'\\',0)[0]), Name = tostring(split(AccountDisplayName,'\\',1)[0])  
queryFrequency: 1h
triggerThreshold: 0
kind: Scheduled
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Defender XDR/Analytic Rules/Impact/AnomalousVoulmeOfFileDeletion.yaml
requiredDataConnectors:
- dataTypes:
  - CloudAppEvents
  - AADSignInEventsBeta
  connectorId: MicrosoftThreatProtection
version: 1.0.0
relevantTechniques:
- T1485
severity: High

{
  "$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/e5f8e196-3544-4a8b-96a9-17c1b6a49710')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/e5f8e196-3544-4a8b-96a9-17c1b6a49710')]",
      "properties": {
        "alertRuleTemplateName": "e5f8e196-3544-4a8b-96a9-17c1b6a49710",
        "customDetails": {
          "Count": "TotalCount"
        },
        "description": "This query looks for users performing file deletion activities. Spikes in file deletion observed from risky sign-in sessions are flagged here.\nThis applies to SharePoint and OneDrive users.\nAudit event and Cloud application identifier references.\nReference - https://learn.microsoft.com/microsoft-365/compliance/audit-log-activities?view=o365-worldwide\nReference - https://learn.microsoft.com/azure/sentinel/entities-reference#cloud-application-identifiers\n",
        "displayName": "Unusual Volume of file deletion by users",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "AccountObjectId",
                "identifier": "AadUserId"
              }
            ]
          },
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "Name",
                "identifier": "Name"
              },
              {
                "columnName": "NTDomain",
                "identifier": "NTDomain"
              }
            ]
          },
          {
            "entityType": "CloudApplication",
            "fieldMappings": [
              {
                "columnName": "ApplicationId",
                "identifier": "AppId"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Defender XDR/Analytic Rules/Impact/AnomalousVoulmeOfFileDeletion.yaml",
        "query": "let relevantOperations = pack_array(\"FileDeleted\", \"FileRecycled\", \"FileDeletedFirstStageRecycleBin\", \"FileDeletedSecondStageRecycleBin\", \"FileVersionsAllMinorsRecycled\", \"FileVersionRecycled\", \"FileVersionsAllRecycled\");\nlet relevantAppIds = pack_array(int(20892), int(15600)); // App Ids for SharePoint and OneDrive\nlet timeWindow = 24h;\nlet timeNow = now();\n//\nlet riskyUsers= // Look for users with risky sign-ins\n  AADSignInEventsBeta    \n  | where Timestamp between ((timeNow - timeWindow) .. (timeNow))\n  | where isnotempty(AccountObjectId) and isnotempty(RequestId) // In AADSignInEventsBeta, the SessionId column has inaccurate data and instead the RequestId has the actual Session identifier\n  | where ErrorCode == 0\n  | where RiskLevelDuringSignIn >=80\n  | project RiskLevelDuringSignIn, AccountObjectId, Timestamp, SessionId=RequestId\n  ;\nlet hasUsers = isnotempty(toscalar(riskyUsers));\n//\nlet deleteEvents = // look for file deletion activity and scope it to risky users\n  CloudAppEvents\n  | where hasUsers\n  | where TimeGenerated between ((timeNow - timeWindow) .. (timeNow))\n  | where ApplicationId in (relevantAppIds)\n  | where isnotempty(AccountObjectId)\n  | where AccountObjectId in (riskyUsers)\n  | where ActionType in (relevantOperations)\n  | extend SessionId= tostring(RawEventData.AppAccessContext.AADSessionId)\n  | where isnotempty(SessionId)\n  | project AccountObjectId, AccountDisplayName, ApplicationId, SessionId, ActionType, TimeGenerated, ReportId\n  ;   \n //\ndeleteEvents  \n| join kind=leftsemi riskyUsers on AccountObjectId, SessionId\n| summarize Count=count() , (Timestamp, ReportId)=arg_min(TimeGenerated, ReportId) by AccountObjectId, AccountDisplayName, ApplicationId, ActionType, Time=bin(TimeGenerated, 5m)\n// look for only those scoped users who have generated an increase in file deletion activity.\n| summarize TotalCount= countif(Count > 50), (Timestamp, ReportId)=arg_min(Timestamp, ReportId) by AccountObjectId, AccountDisplayName, ApplicationId \n| where TotalCount >= 3\n| project AccountObjectId, AccountDisplayName, ApplicationId, TotalCount, ReportId, Timestamp\n| extend NTDomain = tostring(split(AccountDisplayName,'\\\\',0)[0]), Name = tostring(split(AccountDisplayName,'\\\\',1)[0])\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "PT1H",
        "severity": "High",
        "status": "Available",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Impact"
        ],
        "techniques": [
          "T1485"
        ],
        "templateVersion": "1.0.0",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}