Office Activity Activity With Monitored Office File

Query

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

Explanation

This query is designed to detect and summarize specific activities involving SharePoint and OneDrive files that are being monitored for security purposes. Here's a simplified breakdown of what the query does:

Define Benign Files and Users:
- It starts by defining a list of benign files (benign_files) that should be ignored in the analysis.
- It also retrieves a list of users (_BenignUsers) who are expected to perform significant activities, specifically those with the "SharePointAdministrator" role and a specific note.
Retrieve Monitored Files:
- It fetches a list of files and operations that are being monitored from a watchlist (_MonitoredOfficeFiles). This includes details like site URLs, file names, auditors, and operations that are either valid or excluded from monitoring.
Filter Office Activities:
- The query filters activities from the OfficeActivity table to focus on SharePoint and OneDrive workloads.
- It excludes activities by benign users and those involving benign files.
- It performs an inner join with the monitored files to ensure only relevant activities are considered.
- It further filters out operations that are not of interest based on the valid and excluded operations lists.
Summarize and Extend Data:
- The query summarizes the filtered activities by grouping them based on workload, user, site, alert severity, and auditors.
- It collects various details such as client IPs, operations performed, file names, user agents, and machine IDs.
- It constructs an alert description for each activity, providing a human-readable summary of the detected operation.
Project Final Output:
- Finally, it selects and formats the relevant columns for output, including the time of the activity, user details, client IP, operations, site URL, file details, alert severity, and description.

In essence, this query is used to monitor and report on specific file operations in SharePoint and OneDrive, excluding known benign activities, and providing detailed alerts for security analysis.

Details

Jose Sebastián Canós

Released: October 31, 2024

Tables

_GetWatchlistOfficeActivity

Keywords

BenignFilesActivitySharePointAdministratorNotesActorPrincipalNameSiteSiteUrlSourceRelativeUrlSourceFileNameAuditorsMonitoredOperationAlertSeverityOfficeObjectIdOfficeActivityOfficeWorkloadUserIdOperationTimeGeneratedClientIPUserAgentMachineIdAlertDescription

Operators

letdynamictoscalar_GetWatchlistwherehassummarizemake_listprojecttostringmv-applysplittypeofonmake_list_ifisnotemptynotstartswithreplace_stringOfficeActivityinlookupkindinnerarray_lengthhas_anyminmake_setstrcatbyextendiff

Actions

GitHub

KQL Search