Officeactivity Excessive Sharepoint Activity
KQL detection rule by ep3p · MIT
No description provided
let query_frequency = 1h;
let query_period = 14d;
let query_lookback_end = 1d;
let query_window_checks = 6;
let score_threshold = 200000;
let home_tenant_domain = toscalar(
_GetWatchlist("UUID-EntraIdTenantIds")
| where Notes has "[HomeTenant]"
| project trim_end(@"\.onmicrosoft\.com", OnMicrosoftDomain)
);
let onedrive_url = strcat("https://", home_tenant_domain, "-my.sharepoint.com/"); // https://contoso365-my.sharepoint.com/
let sharepoint_url = strcat("https://", home_tenant_domain, ".sharepoint.com/"); // https://contoso365.sharepoint.com/
let _DefaultSharePointAdministrators = toscalar(
_GetWatchlist("Activity-ExpectedSignificantActivity")
| where Activity == "SharePointAdministrator" and Notes has "[Default]"
| summarize make_list(ActorPrincipalName)
);
OfficeActivity
| where TimeGenerated > ago(2 * query_frequency)
| where isnotempty(Site_Url) and not(UserId in (_DefaultSharePointAdministrators))
// Don't consider sites that were visited in the previous days
| join kind=leftanti(
OfficeActivity
| where TimeGenerated between (ago(query_period) .. ago(query_lookback_end))
| where not(isempty(Site_Url)) and not(UserId in (_DefaultSharePointAdministrators))
) on Site_Url, UserId//, OfficeObjectId
// Remove operations where site is the default SharePoint site, OfficeObjectIds usually will be default images/files
| where not(Site_Url == sharepoint_url and Operation in ("FileAccessed", "FilePreviewed"))
// Remove operations where the user accesses default images of a site
| where not(OfficeObjectId has "/siteassets/")
// Remove operations where the user accesses themes files of a site
| where not(SourceFileExtension in ("spcolor", "sptheme"))
// Remove operations where the user visualizes thumbnails
| where not(SourceFileName endswith "Thumb.jpg")
// Remove operations where the user accesses JavaScript default files from sites/apps
| where not(OfficeWorkload == "SharePoint" and Operation in ("FileAccessed", "FileAccessedExtended")
and SourceFileExtension == "js" and SourceRelativeUrl startswith "ClientSideAssets/")
// Remove operations where the user accesses the default view of a site/folder
| where not(OfficeWorkload == "SharePoint" and Operation in ("FileAccessed", "FileAccessedExtended") and SourceFileName in ("AllPages.aspx", "AllItems.aspx", "allitems.aspx", "ByAuthor.aspx", "Thumbnails.aspx"))
// Remove operations where the user accesses the default view of a site/folder
| where not(OfficeWorkload == "OneDrive" and Operation in ("FileAccessed", "FileAccessedExtended") and SourceFileName == "All.aspx")
// Remove presumably benign operations with high noise potential
| where not(OfficeWorkload == "SharePoint" and Operation in ("FileSyncUploadedFull"))
// Remove operations where the user visited his OneDrive personal folder
| where not(OfficeWorkload == "OneDrive" and Site_Url endswith strcat(replace(@'[@\.]', @'_', UserId), "/"))
// Remove operations where the user previews or recycles files from 1:1 Teams chats
| where not(OfficeWorkload == "OneDrive" and Operation in ("FilePreviewed", "FileRecycled")
and OfficeObjectId has strcat("_", replace(@'\.', '_', tostring(split(UserId, "@")[-1])))
and SourceRelativeUrl has_any ("Documents/Microsoft Teams Chat Files", "Documents/Archivos de chat de Microsoft Teams")
)
// Remove operations of some characteristic jsons in Hub Sites
| where not(OfficeWorkload == "SharePoint" and SourceRelativeUrl == "_catalogs/hubsite")// and SourceFileName matches regex @"[a-f0-9]{8}\-[a-f0-9]{4}\-[a-f0-9]{4}\-[a-f0-9]{4}\-[a-f0-9]{12}\-\d{4}\.json")
// Custom enterprise filters
// It is recommended you filter a few specific site/operation pairs of your tenant that might generate noise for this query
// One example could be a welcome SharePoint site that is visited automatically by all users when opening a browser
// Another example, you should exclude Microsoft Teams Chat Files in SourceRelativeUrl paths (both in English and your native language), for OneDrive
| mv-expand QueryRange = range(now() - query_frequency + query_frequency/query_window_checks, now(), query_frequency/query_window_checks) to typeof(datetime)
| where TimeGenerated between ((QueryRange - query_frequency) .. QueryRange)
| summarize
StartTime = min(TimeGenerated),
EndTime = max(TimeGenerated),
DistinctSites = dcount(Site_),
DistinctObjects = dcount(OfficeObjectId),
FileExtensions = make_set_if(tolower(SourceFileExtension), isnotempty(SourceFileExtension)),
OperationCount = count(),
Site_Urls = make_set(Site_Url, 500),
SampleOfficeObjectIds = make_set(tostring(pack(Operation, OfficeObjectId)), 250),
Operations = make_set(Operation, 200),
ClientIPs = make_set_if(ClientIP, isnotempty(ClientIP)),
IsManagedDevices = make_set_if(IsManagedDevice, isnotempty(IsManagedDevice))
by UserId, QueryRange
| where DistinctSites > 1
| where not(array_length(Operations) == 1 and Operations[0] == "FilePreviewed")
| where EndTime > ago(query_frequency)
| extend Score = toint(pow(DistinctObjects, 2) * pow(DistinctSites, 2) * pow(array_length(FileExtensions), 2) / tolong(OperationCount))
| where Score > score_threshold
| summarize
StartTime = min(StartTime),
EndTime = max(EndTime),
DistinctSites = max(DistinctSites),
DistinctObjects = max(DistinctObjects),
FileExtensions = make_set(FileExtensions),
OperationCount = max(OperationCount),
Site_Urls = array_sort_asc(make_set(Site_Urls, 500)),
SampleOfficeObjectIds = array_sort_asc(make_set(SampleOfficeObjectIds, 250)),
Operations = make_set(Operations, 200),
ClientIPs = make_set(ClientIPs),
IsManagedDevices = make_set(IsManagedDevices),
Score = max(Score)
by UserId
| sort by Score desc
| project
StartTime,
EndTime,
UserId,
ClientIPs,
IsManagedDevices,
Score,
Operations,
OperationCount,
DistinctSites,
DistinctObjects,
FileExtensions,
Site_Urls,
SampleOfficeObjectIds