Office Activity Calculate Percentageof Downloadsper Domain
Query
//Calculate the percentage that each guest domain is contributing to total downloads from your Office 365 tenant
//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])
| summarize Count=count() by ['Guest Domain']
| as T
| extend Percentage = round(100.0 * Count / toscalar (T
| summarize sum(Count)), 2)
| project-reorder ['Guest Domain'], Count, Percentage
| sort by Percentage descExplanation
This query calculates the percentage of downloads from each guest domain in your Office 365 tenant. It filters the data for the past 30 days and includes only full file sync downloads and file downloads. It identifies guest users by their UserPrincipalName containing "#EXT#" and extracts the guest domain from the UserPrincipalName. It then summarizes the count of downloads for each guest domain and calculates the percentage contribution to the total downloads. The results are sorted in descending order by percentage.
Details
Matt Zorich
Released: June 17, 2022
Tables
OfficeActivity
Keywords
OfficeActivityTimeGeneratedOperationUserIdGuest UserPrincipalNameGuest DomainCountPercentage
Operators
whereagoincontainsextendsplittostringsummarizecountasroundtoscalarproject-reordersort by