KQL Search

Query Details

HomeAI ToolsDevice Query

Office Activity Summarize Download Activityby Guests

Query

//Summarize the total count and the list of files downloaded by guests in your Office 365 tenant

//Data connector required for this query - Office 365

let timeframe=7d;
OfficeActivity
| where TimeGenerated > ago(timeframe)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
| where UserId contains "#EXT#"
| summarize
    ['Count of Downloads']=count(),
    ['List of Files Downloaded']=make_set(OfficeObjectId)
    by UserId
| sort by ['Count of Downloads'] desc

Explanation

This query summarizes the total count and the list of files downloaded by guests in your Office 365 tenant. It uses the Office 365 data connector and looks at the past 7 days of activity. It filters for specific operations related to file downloads and only includes users with a UserId containing "#EXT#". The results are then grouped by UserId and sorted in descending order based on the count of downloads.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

OfficeActivity

Keywords

Devices,Intune,User,Office365,OfficeActivity,TimeGenerated,Operation,UserId,FileSyncDownloadedFull,FileDownloaded,#EXT#,CountofDownloads,ListofFilesDownloaded,OfficeObjectId

Operators

letwheresummarizecountmake_setbysort

Actions