KQL Search

Query Details

HomeAI ToolsDevice Query

HUNT 12 M365 One Drive Deleted Files Timeline 30d

Query

// Hunt    : M365 - OneDrive Deleted Files Timeline (for Ransomware / Sabotage) (30d)
// Purpose : Enumerate all file deletion and version-deletion events in OneDrive
//           over 30 days, ranked by user. Supports ransomware impact assessment,
//           insider sabotage review, and recovery scoping.
// Tables  : OfficeActivity
// Period  : P30D
//==========================================================================================

let LookbackDays = 30d;

OfficeActivity
| where TimeGenerated > ago(LookbackDays)
| where RecordType in ("OneDrive", "SharePointFileOperation", "OneDriveFileOperation")
| where Operation in (
    "FileDeleted", "FolderDeleted",
    "FileVersionsAllDeleted", "FileRecycled",
    "FilePermanentlyDeleted")
| extend
    FileExt        = tostring(extract(@"(\.[a-zA-Z0-9]+)$", 1, SourceFileName)),
    IsPermanent    = Operation in ("FilePermanentlyDeleted", "FileVersionsAllDeleted")
| summarize
    TotalDeleted     = count(),
    PermanentDeletes = countif(IsPermanent),
    VersionDeletes   = countif(Operation == "FileVersionsAllDeleted"),
    UniqueFiles      = dcount(SourceFileName),
    FolderDeletes    = countif(Operation == "FolderDeleted"),
    FileExtensions   = make_set(FileExt, 15),
    SampleFiles      = make_set(SourceFileName, 10),
    ClientIPs        = make_set(ClientIP, 5),
    FirstDelete      = min(TimeGenerated),
    LastDelete       = max(TimeGenerated)
    by UserId
| sort by PermanentDeletes desc, TotalDeleted desc
| project
    UserId,
    TotalDeleted,
    PermanentDeletes,
    VersionDeletes,
    FolderDeletes,
    UniqueFiles,
    FileExtensions,
    SampleFiles,
    ClientIPs,
    FirstDelete,
    LastDelete

Explanation

This query is designed to analyze file deletion activities in OneDrive over the past 30 days. It focuses on identifying potential ransomware impacts, insider sabotage, and helps in planning recovery efforts. Here's a simple breakdown of what the query does:

  1. Time Frame: It looks at data from the last 30 days.

  2. Data Source: It examines records from the OfficeActivity table, specifically those related to OneDrive and SharePoint file operations.

  3. Operations of Interest: It filters for events where files or folders were deleted, including permanent deletions and deletions of all file versions.

  4. Data Processing:

    • It extracts file extensions from the file names.
    • It checks if the deletion was permanent.
    • It counts various types of deletions and unique files affected.
    • It collects a sample of file names and client IPs involved in the deletions.

  5. Summarization:

    • It summarizes the data by user, counting total deletions, permanent deletions, version deletions, folder deletions, and unique files.
    • It also lists up to 15 different file extensions and samples of up to 10 file names and 5 client IPs.
    • It records the first and last deletion event times for each user.

  6. Sorting and Output:

    • The results are sorted by the number of permanent deletions and total deletions in descending order.
    • The final output includes user ID, counts of various deletion types, file extensions, sample files, client IPs, and the time range of deletions.

This query helps identify users with significant deletion activities, which could indicate malicious actions or accidental data loss.

Details

David Alonso profile picture

David Alonso

Released: March 18, 2026

Tables

OfficeActivity

Keywords

OfficeActivityOneDriveSharePointFileOperationOneDriveFileOperationFileDeletedFolderDeletedFileVersionsAllDeletedFileRecycledFilePermanentlyDeletedUserIdSourceFileNameClientIPTimeGenerated

Operators

let|whereinextendtostringextractsummarizecountcountifdcountmake_setminmaxbysortdescproject

Actions