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

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:

Define a Source Path: It sets a specific path (Service Catalog/ServiceCatalog/) to focus on files within this directory.
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.
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.
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.

KQL Search