KQL Search

let benign_files = dynamic(["/SiteAssets/__siteIcon__.jpg"]);
let _BenignUsers = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "SharePointAdministrator" and Notes has "[Default]"
    | summarize make_list(ActorPrincipalName)
);
let _MonitoredOfficeFiles =
    _GetWatchlist("UUID-AuditOfficeFiles")
    | project
        Site_ = tostring(Site_),
        Site_Url,
        SourceRelativeUrl,
        SourceFileName,
        Auditors = Auditor,
        MonitoredOperation,
        AlertSeverity = Severity,
        OfficeObjectId,
        Notes
    | mv-apply split(MonitoredOperation, " ") to typeof(string) on (
        summarize
            ValidOperations = make_list_if(MonitoredOperation, isnotempty(MonitoredOperation) and not(MonitoredOperation startswith "-")),
            ExcludedOperations = make_list_if(replace_string(MonitoredOperation, "-", ""), isnotempty(MonitoredOperation) and MonitoredOperation startswith "-")
    )
;
OfficeActivity
| where OfficeWorkload in ("SharePoint", "OneDrive")
| where not(UserId in (_BenignUsers))
| where not(OfficeObjectId has_any (benign_files))
| lookup kind=inner _MonitoredOfficeFiles on Site_
| where not(array_length(ValidOperations) > 0 and not(ValidOperations has Operation))
| where not(array_length(ExcludedOperations) > 0 and ExcludedOperations has Operation)
| where not(isnotempty(SourceRelativeUrl1) and SourceRelativeUrl != SourceRelativeUrl1)
| where not(isnotempty(SourceFileName1) and SourceFileName != SourceFileName1)
| summarize
    TimeGenerated = min(TimeGenerated),
    ClientIPs = make_set(ClientIP, 250),
    Operations = make_set(Operation, 250),
    Files = make_set(OfficeObjectId, 250),
    FileNames = make_set(strcat(SourceRelativeUrl, SourceFileName), 250),
    UserAgents = make_set(UserAgent, 250),
    MachineIds = make_set(MachineId, 250),
    take_any(Site_Url)
    by OfficeWorkload, UserId, Site_, AlertSeverity, Auditors
| extend File = iff(array_length(FileNames) == 1, tostring(FileNames[0]), "")
| extend AlertDescription = strcat(
    'This rule detects operations with specified SharePoint files. This description presents only the information from one of the events. The information of the rest of the events may appear at the Entities table.\n\nThis activity was performed by the account: "', UserId, '"\n\n',
    'This activity affected the SharePoint files from: "', Site_Url, '"',
    iff(isnotempty(File),
        strcat('\n\nThe file "', File, '" was "', tostring(Operations[0]), '".'),
        ""
        )
    )
| project
    TimeGenerated,
    OfficeWorkload,
    UserId,
    ClientIP = tostring(ClientIPs[0]),
    ClientIPs,
    Operations,
    Site_Url,
    File,
    Files,
    UserAgents,
    MachineIds,
    AlertSeverity,
    AlertDescription,
    Auditors