Query Details
Teams List Federated Users
Query
//List of Teams sites that have federated external users OfficeActivity | where TimeGenerated > ago(7d) | where Operation =~ "MemberAdded" | extend UPN = tostring(parse_json(Members)[0].Upn) | where UPN !endswith "sixmilliondollarman.onmicrosoft.com" | where parse_json(Members)[0].Role == 3 | project TeamName, Operation, UserId, Members, UPN
Explanation
This query retrieves a list of Teams sites that have external users who are federated. It filters the results based on the activity in the last 7 days, specifically looking for member additions. It extends the query to include the user principal name (UPN) of the first member added to each site. It excludes any UPNs that end with "sixmilliondollarman.onmicrosoft.com" and only includes sites where the role of the first member is 3. The final result includes the team name, operation, user ID, members, and UPN.
Details
Rod Trent
Released: September 1, 2020
Tables
OfficeActivity
Keywords
Teams,Sites,Federated,External,Users
Operators
whereago=~extendtostringparse_json!endswith==project