KQL Search

Query Details

HomeAI ToolsDevice Query

Office Activity Summarize Guests Addedto Teams

Query

//Find any of your Teams that have had guests added to them in the last week and arrange by the Teams with the most guests added.

//Data connector required for this query - Office 365

OfficeActivity
| where TimeGenerated > ago(7d)
| where Operation == "MemberAdded"
| mv-expand Members
| extend UserAdded = tostring(Members.UPN)
| where UserAdded contains "#EXT#"
| where CommunicationType == "Team"
| summarize
    ['Number of Guests Added']=dcount(UserAdded),
    ['List of Guests Added']=make_set(UserAdded)
    by TeamName
| sort by ['Number of Guests Added'] desc

Explanation

This query finds Teams that have had guests added to them in the last week and arranges them based on the number of guests added. It uses the Office 365 data connector and the OfficeActivity table. It filters for member additions, expands the members column, and filters for external users added to Teams. It then summarizes the number of guests added and creates a list of the guests added for each Team. Finally, it sorts the results in descending order based on the number of guests added.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

OfficeActivity

Keywords

OfficeActivity,TimeGenerated,Operation,MemberAdded,Members,UserAdded,UPN,CommunicationType,TeamName

Operators

whereago==mv-expandextendcontainssummarizedcountmake_setbysortdesc

Actions