Query Details
Office Activity Visualize Guests Added Removedfrom Teams
Query
//Visualize guests added vs removed from Teams per day over the last 30 days
//Data connector required for this query - Office 365
OfficeActivity
| where TimeGenerated > ago(30d)
| where UserType == "Regular"
| where CommunicationType == "Team"
| where OfficeWorkload == "MicrosoftTeams"
| where Operation in ("MemberAdded", "MemberRemoved")
| mv-expand Members
| extend User = tostring(Members.UPN)
| where User contains "#EXT#"
| project TimeGenerated, Operation, User
| summarize
['Guests Added']=countif(Operation == "MemberAdded"),
['Guests Removed']=countif(Operation == "MemberRemoved")
by startofday(TimeGenerated)
| render columnchart
with (
kind=unstacked,
xtitle="Count",
ytitle="Day",
title="Guests Added vs Removed from Teams")Explanation
This query visualizes the number of guests added and removed from Teams per day over the last 30 days. It uses the Office 365 data connector and filters the OfficeActivity table for regular users, Teams communication type, and Microsoft Teams workload. It then expands the Members column, converts it to a string, and filters for users with "#EXT#" in their UPN. The query projects the TimeGenerated, Operation, and User columns, and summarizes the count of guests added and removed by the start of each day. Finally, it renders a column chart with the count on the x-axis, the day on the y-axis, and a title indicating "Guests Added vs Removed from Teams".
Details
Matt Zorich
Released: June 17, 2022
Tables
OfficeActivity
Keywords
Devices,Intune,User
Operators
whereago==inmv-expandextendcontainsprojectsummarizecountifbyrender