KQL Search

Query Details

HomeAI ToolsDevice Query

Office365 Recycled Restored

Query

# Office 365 - SharePoint and OneDrive - Compare recycled vs. restored files

## Query Information

### Description

Use the below query to list all recycled (deleted) and compare with restored files

#### References

### Microsoft Sentinel / Defender XDR

```kql
let SourcePath = "Service Catalog/ServiceCatalog/";
let restored = (OfficeActivity
| where Operation in ("FileRestored","FolderRestored")
| where SourceRelativeUrl has (SourcePath)
| where ItemType <> "Folder"
| project TimeGenerated, Operation, UserId, Site_Url, SourceRelativeUrl, SourceFileName, SourceFileExtension, OfficeObjectId,ItemType, OfficeWorkload);
let recycled = (OfficeActivity
| where Operation in ("FileRecycled","FolderRecycled")
| where SourceRelativeUrl has  (SourcePath)
| where ItemType <> "Folder"
| project TimeGenerated, Operation, UserId, Site_Url, SourceRelativeUrl, SourceFileName, SourceFileExtension, OfficeObjectId,ItemType, OfficeWorkload);
recycled
| join kind=leftouter (restored)
on $left. OfficeObjectId == $right. OfficeObjectId
```

Explanation

This query is designed to compare files that have been deleted (recycled) with those that have been restored in Office 365's SharePoint and OneDrive services. Here's a simple breakdown of what the query does:

  1. Define a Source Path: It sets a specific path (Service Catalog/ServiceCatalog/) to focus on files within this directory.

  2. Identify Restored Files:

    • It looks for activities where files or folders have been restored.
    • Filters out any folders (only considers files).
    • Projects (selects) relevant columns like time, operation type, user ID, site URL, file name, etc.

  3. Identify Recycled Files:

    • It looks for activities where files or folders have been deleted (recycled).
    • Filters out any folders (only considers files).
    • Projects (selects) the same relevant columns as for restored files.

  4. Compare Recycled and Restored Files:

    • It performs a left outer join on the recycled files with the restored files based on a unique identifier (OfficeObjectId).
    • This means it will list all deleted files and show corresponding restored file information if available.

In essence, the query helps you see which deleted files have been restored and provides details about both actions.

Details

Alex Verboon profile picture

Alex Verboon

Released: September 12, 2024

Tables

OfficeActivity

Keywords

OfficeActivityOperationSourceRelativeUrlItemTypeTimeGeneratedUserIdSiteUrlSourceFileNameSourceFileExtensionOfficeObjectIdOfficeWorkload

Operators

letinhasprojectjoinkindon==

Actions