Accessed files shared by temporary external user

id: bff058b2-500e-4ae5-bb49-a5b1423cbd5b
name: Accessed files shared by temporary external user
description: |
  'This detection identifies when an external user is added to a Team or Teams chat and shares a file which is accessed by many users (>10) and the users is removed within short period of time. This might be an indicator of suspicious activity.'
severity: Low
status: Available 
requiredDataConnectors:
  - connectorId: Office365
    dataTypes:
      - OfficeActivity (Teams)
  - connectorId: Office365
    dataTypes:
      - OfficeActivity (SharePoint)
queryFrequency: 1h
queryPeriod: 1h
triggerOperator: gt
triggerThreshold: 0
tactics:
  - InitialAccess
relevantTechniques:
  - T1566
query: |
  let fileAccessThrehold = 10;
  OfficeActivity
  | where OfficeWorkload =~ "MicrosoftTeams"
  | where Operation =~ "MemberAdded"
  | extend MemberAdded = tostring(parse_json(Members)[0].UPN)
  | where MemberAdded contains ("#EXT#")
  | project TimeAdded=TimeGenerated, Operation, MemberAdded, UserWhoAdded = UserId, TeamName
  | join kind = inner (
    OfficeActivity
    | where OfficeWorkload =~ "MicrosoftTeams"
    | where Operation =~ "MemberRemoved"
    | extend MemberAdded = tostring(parse_json(Members)[0].UPN)
    | where MemberAdded contains ("#EXT#")
    | project TimeDeleted=TimeGenerated, Operation, MemberAdded, UserWhoDeleted = UserId, TeamName
    ) on MemberAdded
  | where TimeDeleted > TimeAdded
  | join kind=inner (
    OfficeActivity
    | where RecordType == "SharePointFileOperation"
    | where SourceRelativeUrl has "Microsoft Teams Chat Files"
    | where Operation == "FileUploaded"
    | extend MemberAdded = UserId
    | join kind = inner (
        OfficeActivity
        | where RecordType == "SharePointFileOperation"
        | where Operation  == "FileAccessed"
        | where SourceRelativeUrl has "Microsoft Teams Chat Files"
        | summarize FileAccessCount = count() by OfficeObjectId
        | where FileAccessCount > fileAccessThrehold
        ) on $left.OfficeObjectId == $right.OfficeObjectId
    )on MemberAdded
  | project-away MemberAdded1, MemberAdded2, OfficeObjectId1, Operation1, Operation2, TeamName1, TeamName2
  | extend MemberAddedAccountName = tostring(split(MemberAdded, "@")[0]), MemberAddedAccountUPNSuffix = tostring(split(MemberAdded, "@")[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
      - identifier: Name
        columnName: MemberAddedAccountName
      - identifier: UPNSuffix
        columnName: MemberAddedAccountUPNSuffix
  - 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.1
kind: Scheduled