SharePointFileOperation via devices with previously unseen user agents

// Set threshold for the number of downloads/uploads from a new user agent
let threshold = 5;
// Define constants for SharePoint file operations
let szSharePointFileOperation = "SharePointFileOperation";
let szOperations = dynamic(["FileDownloaded", "FileUploaded"]);
// Define the historical activity for analysis
let starttime = 14d; // Define the start time for historical data (14 days ago)
let endtime = 1d;   // Define the end time for historical data (1 day ago)
// Extract the base events for analysis
let Baseevents =
  OfficeActivity
  | where TimeGenerated between (ago(starttime) .. ago(endtime))
  | where RecordType =~ szSharePointFileOperation
  | where Operation in~ (szOperations)
  | where isnotempty(UserAgent);
// Identify frequently occurring user agents
let FrequentUA = Baseevents
  | summarize FUACount = count() by UserAgent, RecordType, Operation
  | where FUACount >= threshold
  | distinct UserAgent;
// Calculate a user baseline for further analysis
let UserBaseLine = Baseevents
  | summarize Count = count() by UserId, Operation, Site_Url
  | summarize AvgCount = avg(Count) by UserId, Operation, Site_Url;
// Extract recent activity for analysis
let RecentActivity = OfficeActivity
  | where TimeGenerated > ago(endtime)
  | where RecordType =~ szSharePointFileOperation
  | where Operation in~ (szOperations)
  | where isnotempty(UserAgent)
  | where UserAgent in~ (FrequentUA)
  | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), OfficeObjectIdCount = dcount(OfficeObjectId), OfficeObjectIdList = make_set(OfficeObjectId), UserAgentSeenCount = count() 
  by RecordType, Operation, UserAgent, UserType, UserId, ClientIP, OfficeWorkload, Site_Url;
// Analyze user behavior based on baseline and recent activity
let UserBehaviorAnalysis = UserBaseLine
  | join kind=inner (RecentActivity) on UserId, Operation, Site_Url
  | extend Deviation = abs(UserAgentSeenCount - AvgCount) / AvgCount;
// Filter and format results for specific user behavior analysis
UserBehaviorAnalysis
  | where Deviation > 25
  | extend UserIdName = tostring(split(UserId, '@')[0]), UserIdUPNSuffix = tostring(split(UserId, '@')[1])
  | project-reorder StartTime, EndTime, UserAgent, UserAgentSeenCount, UserId, ClientIP, Site_Url
  | project-away Site_Url1, UserId1, Operation1
  | order by UserAgentSeenCount desc, UserAgent asc, UserId asc, Site_Url asc

requiredDataConnectors:
- connectorId: Office365
  dataTypes:
  - OfficeActivity
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft 365/Analytic Rules/SharePoint_Downloads_byNewUserAgent.yaml
triggerThreshold: 0
status: Available
relevantTechniques:
- T1030
queryPeriod: 14d
name: SharePointFileOperation via devices with previously unseen user agents
entityMappings:
- entityType: Account
  fieldMappings:
  - columnName: UserId
    identifier: FullName
  - columnName: UserIdName
    identifier: Name
  - columnName: UserIdUPNSuffix
    identifier: UPNSuffix
- entityType: IP
  fieldMappings:
  - columnName: ClientIP
    identifier: Address
- entityType: URL
  fieldMappings:
  - columnName: Site_Url
    identifier: Url
queryFrequency: 1d
triggerOperator: gt
kind: Scheduled
description: |
    'Identifies anomalies if the number of documents uploaded or downloaded from device(s) associated with a previously unseen user agent exceeds a threshold (default is 5) and deviation (default is 25).'
tactics:
- Exfiltration
severity: Medium
version: 2.2.4
query: |
  // Set threshold for the number of downloads/uploads from a new user agent
  let threshold = 5;
  // Define constants for SharePoint file operations
  let szSharePointFileOperation = "SharePointFileOperation";
  let szOperations = dynamic(["FileDownloaded", "FileUploaded"]);
  // Define the historical activity for analysis
  let starttime = 14d; // Define the start time for historical data (14 days ago)
  let endtime = 1d;   // Define the end time for historical data (1 day ago)
  // Extract the base events for analysis
  let Baseevents =
    OfficeActivity
    | where TimeGenerated between (ago(starttime) .. ago(endtime))
    | where RecordType =~ szSharePointFileOperation
    | where Operation in~ (szOperations)
    | where isnotempty(UserAgent);
  // Identify frequently occurring user agents
  let FrequentUA = Baseevents
    | summarize FUACount = count() by UserAgent, RecordType, Operation
    | where FUACount >= threshold
    | distinct UserAgent;
  // Calculate a user baseline for further analysis
  let UserBaseLine = Baseevents
    | summarize Count = count() by UserId, Operation, Site_Url
    | summarize AvgCount = avg(Count) by UserId, Operation, Site_Url;
  // Extract recent activity for analysis
  let RecentActivity = OfficeActivity
    | where TimeGenerated > ago(endtime)
    | where RecordType =~ szSharePointFileOperation
    | where Operation in~ (szOperations)
    | where isnotempty(UserAgent)
    | where UserAgent in~ (FrequentUA)
    | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), OfficeObjectIdCount = dcount(OfficeObjectId), OfficeObjectIdList = make_set(OfficeObjectId), UserAgentSeenCount = count() 
    by RecordType, Operation, UserAgent, UserType, UserId, ClientIP, OfficeWorkload, Site_Url;
  // Analyze user behavior based on baseline and recent activity
  let UserBehaviorAnalysis = UserBaseLine
    | join kind=inner (RecentActivity) on UserId, Operation, Site_Url
    | extend Deviation = abs(UserAgentSeenCount - AvgCount) / AvgCount;
  // Filter and format results for specific user behavior analysis
  UserBehaviorAnalysis
    | where Deviation > 25
    | extend UserIdName = tostring(split(UserId, '@')[0]), UserIdUPNSuffix = tostring(split(UserId, '@')[1])
    | project-reorder StartTime, EndTime, UserAgent, UserAgentSeenCount, UserId, ClientIP, Site_Url
    | project-away Site_Url1, UserId1, Operation1
    | order by UserAgentSeenCount desc, UserAgent asc, UserId asc, Site_Url asc  
id: 5dd76a87-9f87-4576-bab3-268b0e2b338b