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 = 7d;
let timeNow = now();
//
let riskyUsers= // Look for users with risky sign-ins
  SigninLogs    
  | where CreatedDateTime between ((timeNow - timeWindow) .. (timeNow))
  | where isnotempty(UserId) and isnotempty(OriginalRequestId)
  | where ResultType == '0'
  | where RiskLevelDuringSignIn == 'high'
  | project RiskLevelDuringSignIn, UserId, CreatedDateTime, SessionId=OriginalRequestId
  ;
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 UserId=AccountObjectId, AccountDisplayName, ApplicationId, SessionId, ActionType, TimeGenerated, ReportId
  ;   
 //
deleteEvents  
| join kind=leftsemi riskyUsers on UserId, SessionId
| summarize Count=count() , (Timestamp, ReportId)=arg_min(TimeGenerated, ReportId) by UserId, 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 UserId, AccountDisplayName, ApplicationId 
| where TotalCount >= 3
| project UserId, AccountDisplayName, ApplicationId, TotalCount, ReportId, Timestamp
| extend NTDomain = tostring(split(AccountDisplayName,'\\',0)[0]), Name = tostring(split(AccountDisplayName,'\\',1)[0])

relevantTechniques:
- T1485
name: Unusual Volume of file deletion by users
triggerThreshold: 0
tactics:
- Impact
severity: High
id: e5f8e196-3544-4a8b-96a9-17c1b6a49710
status: Available
requiredDataConnectors:
- dataTypes:
  - CloudAppEvents
  - AADSignInEventsBeta
  connectorId: MicrosoftThreatProtection
kind: Scheduled
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 = 7d;
  let timeNow = now();
  //
  let riskyUsers= // Look for users with risky sign-ins
    SigninLogs    
    | where CreatedDateTime between ((timeNow - timeWindow) .. (timeNow))
    | where isnotempty(UserId) and isnotempty(OriginalRequestId)
    | where ResultType == '0'
    | where RiskLevelDuringSignIn == 'high'
    | project RiskLevelDuringSignIn, UserId, CreatedDateTime, SessionId=OriginalRequestId
    ;
  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 UserId=AccountObjectId, AccountDisplayName, ApplicationId, SessionId, ActionType, TimeGenerated, ReportId
    ;   
   //
  deleteEvents  
  | join kind=leftsemi riskyUsers on UserId, SessionId
  | summarize Count=count() , (Timestamp, ReportId)=arg_min(TimeGenerated, ReportId) by UserId, 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 UserId, AccountDisplayName, ApplicationId 
  | where TotalCount >= 3
  | project UserId, AccountDisplayName, ApplicationId, TotalCount, ReportId, Timestamp
  | extend NTDomain = tostring(split(AccountDisplayName,'\\',0)[0]), Name = tostring(split(AccountDisplayName,'\\',1)[0])  
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  
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Defender XDR/Analytic Rules/Impact/AnomalousVoulmeOfFileDeletion.yaml
triggerOperator: gt
queryPeriod: 1h
queryFrequency: 1h
customDetails:
  Count: TotalCount
version: 1.0.1
entityMappings:
- entityType: Account
  fieldMappings:
  - columnName: UserId
    identifier: AadUserId
- entityType: Account
  fieldMappings:
  - columnName: Name
    identifier: Name
  - columnName: NTDomain
    identifier: NTDomain
- entityType: CloudApplication
  fieldMappings:
  - columnName: ApplicationId
    identifier: AppId