Query Details
Office Activity Visualize File Share Top Guest Domains
Query
//Visualize the guest domains that have had the most files shares to them from your Office 365 tenant
//Data connector required for this query - Office 365
OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation in~ ("AddedToSecureLink", "SecureLinkCreated", "SecureLinkUpdated")
| where TargetUserOrGroupType == "Guest" and TargetUserOrGroupName contains "#ext#"
| extend ['Guest UserPrincipalName'] = tostring(split(TargetUserOrGroupName, "#")[0])
| extend ['Guest Domain'] = tostring(split(['Guest UserPrincipalName'], "_")[-1])
| summarize Count=count() by ['Guest Domain']
| top 20 by Count
| render barchart with (title="Top guest domains with files shared to")Explanation
This query analyzes the Office 365 activity data to identify the guest domains that have received the most file shares from your Office 365 tenant in the past 30 days. It filters the data to include only operations related to file sharing and focuses on guest users. The query then splits the guest user's email address to extract the domain and summarizes the count of file shares for each guest domain. Finally, it displays the top 20 guest domains in a bar chart visualization.
Details
Matt Zorich
Released: June 17, 2022
Tables
OfficeActivity
Keywords
OfficeActivity,TimeGenerated,Operation,TargetUserOrGroupType,TargetUserOrGroupName,Guest,GuestUserPrincipalName,GuestDomain,Count,barchart
Operators
wherein~==containsextendtostringsplit[-1]summarizecount()bytoprender