Officeactivity Activity With Monitored Office File
KQL detection rule by ep3p · MIT
No description provided
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