MicrosoftGreat post, Pete Bryan!
I noticed a minor bug in the "External Users Added then Removed"-query.
When a user is removed from a team it appears that the userid is prepended to the actual UPN which means that the join wont match the add event with the remove event, for example "test1@org.com" would in the remove event become "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxtest1@org.com".
My simple fix below:
// If you want to look at user added further than 7 days ago adjust this value
let time_ago = 7d;
// If you want to change the timeframe of how quickly accounts need to be added and removed change this value
let time_delta = 1h;
TeamsData
| where TimeGenerated > ago(time_ago)
| where Operation =~ "MemberAdded"
| extend UPN = tostring(parse_json(Members)[0].UPN)
| project TimeAdded=TimeGenerated, Operation, UPN, UserWhoAdded = UserId, TeamName, TeamGuid = tostring(Details.TeamGuid)
| join (
TeamsData
| where TimeGenerated > ago(time_ago)
| where Operation =~ "MemberRemoved"
| extend UPN = trim_start(@"[0-9a-fA-F]{32}", tostring(parse_json(Members)[0].UPN))
| project TimeDeleted=TimeGenerated, Operation, UPN, UserWhoDeleted = UserId, TeamName, TeamGuid = tostring(Details.TeamGuid)) on UPN, TeamGuid
| where TimeDeleted < (TimeAdded + time_delta)
| project TimeAdded, TimeDeleted, UPN, UserWhoAdded, UserWhoDeleted, TeamName, TeamGuidAlso, this does not look for external users per se, it will catch intra org users as well. Would need to add the organization filter from the "External users from anomolous organizations" query and probably a parameter for "trusted organizations" for this to look explicitly for external users.
Keep up the great work!