Query Details
Office Activity Visualize Downloadsby Trust Type
Query
//Visualize downloads from your Office 365 tenant by trust type (trusted/known by Azure Active Directory vs Unknown)
//Data connector required for this query - Office 365
//Data connector required for this query - Azure Active Directory - Signin Logs
//Query Azure AD logs to get a listing of each username, IPAddress and trust type
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| where UserType == "Member"
| extend DeviceTrustType = tostring(DeviceDetail.trustType)
| distinct UserPrincipalName, IPAddress, DeviceTrustType
//Join to Office Activity download on username and IP and find download events
| join kind=inner(
OfficeActivity
| where TimeGenerated > ago(30d)
| where Operation in ("FileSyncDownloadedFull", "FileDownloaded")
)
on $left.UserPrincipalName == $right.UserId, $left.IPAddress == $right.ClientIP
//Summarize download events by whether the device is known or not
| summarize
['Trusted Devices']=countif(isnotempty(DeviceTrustType)),
['Untrusted Devices']=countif(isempty(DeviceTrustType))
by bin(TimeGenerated, 1d)
| render timechart with (title="Downloads from Office 365 by device trust type")
Explanation
This query analyzes the downloads from your Office 365 tenant based on the trust type of the devices used. It retrieves data from Azure Active Directory (AAD) Signin Logs and Office Activity logs.
First, it filters the AAD Signin Logs to get a list of usernames, IP addresses, and trust types of devices. Then, it joins this data with the Office Activity logs to find download events based on matching usernames and IP addresses.
Next, it summarizes the download events by whether the device is known or unknown. It counts the number of events for trusted devices and untrusted devices.
Finally, it visualizes the summarized data in a timechart, showing the number of downloads from Office 365 by device trust type over time.
Details
Matt Zorich
Released: June 18, 2022
Tables
Keywords
Operators