When reviewing Incidents such as SharePointFileOperation via devices with previously unseen user agents and then clicking Events, the query returns colums including URLCustomEntity / Site_URL which show the SharePoint Online site and OfficeObjectID which lists the file accessed on that site.
As the URLs may be cryptic to the security teams, is there any way we can include the "Firendly name" pr title of the site in the query that is returned? e.g. if the site is https://wingtiptoys.sharepoint.com/PRJ996
And the site is named "Northwind Model Platform" can we see "Northwind Model Platform" in the results table.
The query used is:
// The query_now parameter represents the time (in UTC) at which the scheduled analytics rule ran to produce this alert.
set query_now = datetime(2021-11-17T08:59:14.8623880Z);
let threshold = 50;
let szSharePointFileOperation = "SharePointFileOperation";
let szOperations = dynamic(["FileDownloaded", "FileUploaded"]);
let starttime = 14d;
let endtime = 1d;
let historicalActivity =
OfficeActivity
| where TimeGenerated between(ago(starttime) .. ago(endtime))
| where RecordType =~ szSharePointFileOperation
| where Operation in~ (szOperations)
| summarize historicalCount = count() by ClientIP, RecordType, Operation;
let recentActivity = OfficeActivity
| where TimeGenerated > ago(endtime)
| where RecordType =~ szSharePointFileOperation
| where Operation in~ (szOperations)
| summarize min(Start_Time), max(Start_Time), recentCount = count() by ClientIP, RecordType, Operation;
let RareIP = recentActivity
| join kind= leftanti (historicalActivity) on ClientIP, RecordType, Operation
// More than 50 downloads/uploads from a new IP
| where recentCount > threshold;
OfficeActivity
| where TimeGenerated >= ago(endtime)
| where RecordType =~ szSharePointFileOperation
| where Operation in~ (szOperations)
| join kind= inner (RareIP) on ClientIP, RecordType, Operation
| where Start_Time between(min_Start_Time .. max_Start_Time)
| summarize StartTimeUtc = min(min_Start_Time), EndTimeUtc = max(max_Start_Time)
by
RecordType,
Operation,
UserType,
UserId,
ClientIP,
OfficeWorkload,
Site_Url,
OfficeObjectId,
UserAgent,
IPSeenCount = recentCount
| extend
timestamp = StartTimeUtc,
AccountCustomEntity = UserId,
IPCustomEntity = ClientIP,
URLCustomEntity = Site_Url
| order by IPSeenCount desc, ClientIP asc, Operation asc, UserId asc
