External user added and removed in short timeframe

id: bff093b2-500e-4ae5-bb49-a5b1423cbd5b
name: External user added and removed in short timeframe
description: |
  'This detection flags the occurrences of external user accounts that are added to a Team and then removed within one hour.'
severity: Low
status: Available
requiredDataConnectors:
  - connectorId: Office365
    dataTypes:
      - OfficeActivity (Teams)
queryFrequency: 1h
queryPeriod: 1h
triggerOperator: gt
triggerThreshold: 0
tactics:
  - Persistence
relevantTechniques:
  - T1136
query: |
  let TeamsAddDel = (Op:string){
  OfficeActivity
  | where OfficeWorkload =~ "MicrosoftTeams"
  | where Operation == Op
  | where Members has ("#EXT#")
  | mv-expand Members
  | extend UPN = tostring(Members.UPN)
  | where UPN has ("#EXT#")
  | project TimeGenerated, Operation, UPN, UserId, TeamName, ClientIP
  };
  let TeamsAdd = TeamsAddDel("MemberAdded")
  | project TimeAdded=TimeGenerated, Operation, MemberAdded = UPN, UserWhoAdded = UserId, TeamName, ClientIP;
  let TeamsDel = TeamsAddDel("MemberRemoved")
  | project TimeDeleted=TimeGenerated, Operation, MemberRemoved = UPN, UserWhoDeleted = UserId, TeamName, ClientIP;
  TeamsAdd
  | join kind=inner (TeamsDel) on $left.MemberAdded == $right.MemberRemoved
  | where TimeDeleted > TimeAdded
  | project TimeAdded, TimeDeleted, MemberAdded_Removed = MemberAdded, UserWhoAdded, UserWhoDeleted, TeamName, ClientIP
  | extend MemberAdded_RemovedAccountName = tostring(split(MemberAdded_Removed, "@")[0]), MemberAdded_RemovedAccountUPNSuffix = tostring(split(MemberAdded_Removed, "@")[1])
  | extend UserWhoAddedAccountName = tostring(split(UserWhoAdded, "@")[0]), UserWhoAddedAccountUPNSuffix = tostring(split(UserWhoAdded, "@")[1])
  | extend UserWhoDeletedAccountName = tostring(split(UserWhoDeleted, "@")[0]), UserWhoDeletedAccountUPNSuffix = tostring(split(UserWhoDeleted, "@")[1])
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: MemberAdded_Removed
      - identifier: Name
        columnName: MemberAdded_RemovedAccountName
      - identifier: UPNSuffix
        columnName: MemberAdded_RemovedAccountUPNSuffix
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: UserWhoAdded
      - identifier: Name
        columnName: UserWhoAddedAccountName
      - identifier: UPNSuffix
        columnName: UserWhoAddedAccountUPNSuffix
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: UserWhoDeleted
      - identifier: Name
        columnName: UserWhoDeletedAccountName
      - identifier: UPNSuffix
        columnName: UserWhoDeletedAccountUPNSuffix
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: ClientIP
version: 2.1.3
kind: Scheduled