Query Details
Office Activity Guest Domains Highest Downloads
Query
//Summarize the total count of downloads from Office 365 for each of your guest domains
//Data connector required for this query - Office 365
OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
| where UserId contains "#EXT#"
| extend ['Guest UserPrincipalName'] = tostring(split(UserId,"#")[0])
| extend ['Guest Domain'] = tostring(split(['Guest UserPrincipalName'],"_")[-1])
| project ['Guest Domain']
| summarize ['Download Count']=count()by ['Guest Domain']
| sort by ['Download Count'] desc Explanation
This query summarizes the total count of downloads from Office 365 for each guest domain. It filters the data for the past 30 days and includes only the operations "FileSyncDownloadedFull" and "FileDownloaded". It identifies guest users by their UserId containing "#EXT#" and extracts the guest domain from their UserPrincipalName. The query then projects the guest domain and summarizes the count of downloads for each domain. The results are sorted in descending order by the download count.
Details
Matt Zorich
Released: June 17, 2022
Tables
OfficeActivity
Keywords
OfficeActivity,TimeGenerated,Operation,UserId,GuestUserPrincipalName,GuestDomain,DownloadCount
Operators
| where| ago| in| contains| extend| tostring| split| project| summarize| count| by| sort