KQL Search

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