Query Details
Office Activity Summaryof External Activity
Query
//Create a set of users external to your organization who have accessed Office files after being shared. Events are grouped by the user who shared the document, and what activities were performed against it and by which external account.
//Data connector required for this query - Office 365
OfficeActivity
| project LinkCreatedTime=TimeGenerated, Operation, UserWhoShared=UserId, OfficeObjectId
| where Operation in ('AddedToSecureLink', 'SecureLinkCreated', 'SecureLinkUpdated', 'SharingInvitationCreated')
| join kind=inner
(OfficeActivity
| project LinkClickedTime=TimeGenerated, Operation, UserWhoAccessed=UserId, OfficeObjectId)
on OfficeObjectId
| where UserWhoAccessed !endswith "yourdomain.com" and UserWhoAccessed != "app@sharepoint"
| extend ExternalOperation=Operation1
| summarize ExternalUsers=make_set(UserWhoAccessed) by UserWhoShared, OfficeObjectId, ExternalOperationExplanation
This query looks for users outside of your organization who have accessed Office files after they were shared. The query groups the events by the user who shared the document, the activities performed on it, and the external account used. It requires the Office 365 data connector. The query filters for specific operations related to sharing and joins the data to find the users who accessed the shared files. It further filters out any users with email addresses ending in "yourdomain.com" or "app@sharepoint". Finally, it summarizes the results by the user who shared the document, the Office file ID, and the external operation performed, and creates a set of external users who accessed the files.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators