SharePointFileOperation via previously unseen IPs

id: 4b11568b-3f5f-4ba1-80c8-7f1dc8390eb7
name: SharePointFileOperation via previously unseen IPs
description: |
  'Identifies anomalies using user behavior by setting a threshold for significant changes in file upload/download activities from new IP addresses. It establishes a baseline of typical behavior, compares it to recent activity, and flags deviations exceeding a default threshold of 25.'
severity: Medium
status: Available
requiredDataConnectors:
  - connectorId: Office365
    dataTypes:
      - OfficeActivity
queryFrequency: 1d
queryPeriod: 14d
triggerOperator: gt
triggerThreshold: 0
tactics:
  - Exfiltration
relevantTechniques:
  - T1030
query: |
  // Define a threshold for significant deviations
  let threshold = 25;
  // Define the name for the SharePoint File Operation record type
  let szSharePointFileOperation = "SharePointFileOperation";
  // Define an array of SharePoint operations of interest
  let szOperations = dynamic(["FileDownloaded", "FileUploaded"]);
  // Define the start and end time for the analysis period
  let starttime = 14d;
  let endtime = 1d;
  // Define a baseline of normal user behavior
  let userBaseline = OfficeActivity
  | where TimeGenerated between(ago(starttime)..ago(endtime))
  | where RecordType =~ szSharePointFileOperation
  | where Operation in~ (szOperations)
  | where isnotempty(UserAgent)
  | summarize Count = count() by UserId, Operation, Site_Url, ClientIP
  | summarize AvgCount = avg(Count) by UserId, Operation, Site_Url, ClientIP;
  // Get recent user activity
  let recentUserActivity = OfficeActivity
  | where TimeGenerated > ago(endtime)
  | where RecordType =~ szSharePointFileOperation
  | where Operation in~ (szOperations)
  | where isnotempty(UserAgent)
  | summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), RecentCount = count() by UserId, UserType, Operation, Site_Url, ClientIP, OfficeObjectId, OfficeWorkload, UserAgent;
  // Join the baseline and recent activity, and calculate the deviation
  let UserBehaviorAnalysis = userBaseline | join kind=inner (recentUserActivity) on UserId, Operation, Site_Url, ClientIP
  | extend Deviation = abs(RecentCount - AvgCount) / AvgCount;
  // Filter for significant deviations
  UserBehaviorAnalysis
  | where Deviation > threshold
  | project StartTimeUtc, EndTimeUtc, UserId, UserType, Operation, ClientIP, Site_Url, OfficeObjectId, OfficeWorkload, UserAgent, Deviation, Count=RecentCount
  | order by Count desc, ClientIP asc, Operation asc, UserId asc
  | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: UserId
      - identifier: Name
        columnName: AccountName
      - identifier: UPNSuffix
        columnName: AccountUPNSuffix
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: ClientIP
  - entityType: URL
    fieldMappings:
      - identifier: Url
        columnName: Site_Url
version: 2.0.4
kind: Scheduled