SharePoint file operation via previously unseen IPs
Description
Identify SharePoint uploads and downloads from previously unseen IPs whose volume differs by more than 25% from the user's historical daily per-IP activity for the same operation and site.
Query · kql
// Define a percentage 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; let baselineDays = (starttime - endtime) / 1d; let historicalIPActivity = OfficeActivity | where TimeGenerated >= ago(starttime) and TimeGenerated < ago(endtime) | where RecordType =~ szSharePointFileOperation | where Operation in~ (szOperations) | where isnotempty(UserAgent) | summarize Count = count() by UserId, Operation, Site_Url, ClientIP; let userBaseline = historicalIPActivity | summarize AvgCount = avg(toreal(Count) / baselineDays) by UserId, Operation, Site_Url; // 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; // Compare total activity per new IP, not individual files or user agents. let UserBehaviorAnalysis = recentUserActivity | summarize RecentIPCount = sum(RecentCount) by UserId, Operation, Site_Url, ClientIP | join kind=leftanti (historicalIPActivity) on UserId, Operation, Site_Url, ClientIP | join kind=inner (userBaseline) on UserId, Operation, Site_Url | extend Deviation = 100.0 * abs(RecentIPCount - AvgCount) / AvgCount | where Deviation > threshold; recentUserActivity | join kind=inner (UserBehaviorAnalysis) on UserId, Operation, Site_Url, ClientIP | project StartTimeUtc, EndTimeUtc, UserId, UserType, Operation, ClientIP, Site_Url, OfficeObjectId, OfficeWorkload, UserAgent, Deviation, Count = RecentCount, RecentIPCount, AvgCount | order by Count desc, ClientIP asc, Operation asc, UserId asc | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1]) | extend AccountCustomEntity = UserId, IPCustomEntity = ClientIP