Alright class.
Microsoft ships a rule called "SharePointFileOperation via devices with previously unseen user agents". The name promises a hunt for clients nobody has seen before. The query does the opposite. It filters recent activity down to the user agents that appear most often, then compares one day of events against a thirteen day total and demands the day beat the total by a factor of twenty six. This is not a noisy rule. It is a rule that cannot fire (I mean it will - just not with the detection logic)
The Filter That Removes the Threat
Here is how the rule decides which user agents it will look at.
let FrequentUA = Baseevents
| summarize FUACount = count() by UserAgent, RecordType, Operation
| where FUACount >= threshold
| distinct UserAgent;
threshold is 5, and the description sells it as the number of documents needed to trigger. It is not. It is the number of times a user agent must have appeared in the last fortnight before the rule considers it. So FrequentUA is a list of the tenant's most common client strings. Then:
| where UserAgent in~ (FrequentUA)
Recent activity is filtered to that list. A client string nobody has seen has a count of zero, never enters FrequentUA, and is discarded before any scoring happens.
Work it through. An operator pulls documents with python-requests/2.31.0, a string that has never touched the tenant. Count is zero, so the row is deleted. Meanwhile every Chrome build and every OneDrive sync string in the estate sails through, because those are the frequent ones. The rule named after previously unseen user agents is the only rule in the content hub that guarantees it will never see one.
The Baseline That Cannot Be Beaten
The second half is worse, because it fails quietly.
let UserBaseLine = Baseevents
| summarize Count = count() by UserId, Operation, Site_Url
| summarize AvgCount = avg(Count) by UserId, Operation, Site_Url;
The first summarize collapses thirteen days into one row per account, operation and site. The second averages that single value, so AvgCount is not an average of anything. It is the thirteen day total wearing an average's name. There is no bin() anywhere in the query.
Now the comparison:
| extend Deviation = abs(UserAgentSeenCount - AvgCount) / AvgCount
...
| where Deviation > 25
UserAgentSeenCount covers one day, split further by user agent and by client IP. AvgCount covers thirteen days across every agent and every IP. A user who downloads thirty files a day from one site carries an AvgCount of 390. To clear Deviation > 25, that account would need more than 10,140 download events on that site, in one day, through one user agent, from one IP address. And note that the rule description calls 25 a deviation, which every reader will take as twenty five percent. It is two and a half thousand percent...
One more thing before we move on. The join is kind=inner on UserId, Operation and Site_Url. An account pulling documents from a site it has never opened has no baseline row, so the inner join deletes it. Absent history is the strongest signal this telemetry offers. Here it is used as a filter.
What This Rule Should Be Catching
The threat is not an unusual string. The threat is somebody holding valid credentials or a stolen token, pulling documents out of SharePoint and OneDrive with a client that belongs to them rather than to the user. The user agent is evidence of tooling. Volume and spread are the harm.
So the rebuild keeps novelty as its axis, but novelty per account rather than per tenant, and it scores the behaviour behind the new client instead of the string itself. One pass over fourteen days produces three products: the client families each account has already used, the sites each account has already touched, and how many accounts each client family covers across the tenant. The first decides whether a row is a candidate at all. The other two decide whether it is worth an analyst.
The gate is a single line:
| where RiskScore >= FireThreshold
FireThreshold is 100. One signal reaches it alone: bulk retrieval, meaning 500 or more distinct documents in a day through a client family the account has never used, outside a rollout. Nothing else does. A scripted client is 45. A client family covering fewer than three accounts in the tenant is 35. High volume, meaning 100 distinct files, is 40. Three or more sites the account has no history with is 25. Guest is 30, a directory role is 25.
A disabled account is 60, and that number is deliberate. Tokens issued before disablement keep working until they expire, and a client still finishing its work afterwards is expected behaviour rather than an incident. Sixty puts it under the threshold, so it needs a partner before anybody gets paged.
There is no ResultStatus filter. SharePointFileOperation records are written on completion and the field is empty on a large share of them, so filtering on it takes the rule to zero rows.
The degradation path is short. IdentityInfo needs UEBA enabled. Without it the disabled, guest and privileged signals return null, get coalesced to false, and go silent while the client and volume signals carry on. Guest detection has a second source that survives an empty IdentityInfo, which is the #EXT# marker in the account name.
The last point is the one worth remembering. Novelty is only trusted where there is history to be novel against. An account needs 50 file operations in the baseline window before an absent client family counts for anything. If the connector breaks for a fortnight, every client is new for everybody, and a rule built on absence would page the entire tenant at once. This one returns nothing instead.
The RiskIndicators Field
Scripted client: python-requests/ | Client rare in tenant (0 prior accounts) | High volume: 187 files | Reached 6 sites with no account history
Each indicator is derived from the weight that produced it rather than from the underlying boolean, so the string can never disagree with the score it explains.
The combination to chase is the scripted client with no tenant history reaching sites the account has never opened. Volume is optional in that one. It is enumeration, and enumeration usually arrives before the download that matters.
The Blind Spots You Should Know About
An operator who copies the victim's own user agent is invisible here. The family is not new for that account, so the row never becomes a candidate. That is inherent to the axis and no amount of tuning fixes it. Pair this rule with a mass download detection that ignores the client string entirely (and start using Purview..)
Sync operations are out of scope. FileSyncDownloadedFull is how a rogue OneDrive client drains a library, and it is also how every laptop in the estate works, so it gets its own rule rather than a share of this one.
Records with an empty UserAgent are dropped, and that is a large share of app only and API traffic. It is not a small gap.
Accounts below 50 baseline events are excluded, so new starters are uncovered for their first fortnight. Ten or more accounts adopting the same client in one window mutes the novelty weights, so a simultaneous compromise of that many accounts running identical tooling scores lower than it deserves. Retrieval below 25 distinct files is never scored at all.
MITRE Mappings for the Updated Rule
T1213.002, Data from Information Repositories: SharePoint. The seed is scoped to SharePointFileOperation, so the mapping is direct.
T1530, Data from Cloud Storage. OneDrive libraries produce the same records through the same workload.
T1119, Automated Collection. The scripted client weight exists specifically to catch library based retrieval rather than a person clicking.
T1567, Exfiltration Over Web Service. The weakest of the four and I will say so plainly. Retrieval happens over the vendor's own web service, which makes it the closest available match, but this rule never observes the destination.
Tactics: Collection, Exfiltration.
Rule Settings
Run frequency: 1 day. Query period: 14 days
| where TimeGenerated >= ago(SeedWindow)
| where IngestedAt >= ago(DetectionWindow)
Severity: Medium. Event grouping: alert per result. Incident grouping: enabled, grouped by Account, twenty four hour lookback, closed incidents are not reopened.
Alert details override: description format only. The alert name format is left empty on purpose, so every alert carries the rule name and the rule stays countable in SecurityAlert.
Entity mapping: Account with Name from AccountName and UPNSuffix from AccountUPNSuffix; IP with Address from PrimaryIP.
Custom details: RiskScore, RiskIndicators, ClientFamily, UserAgent, FileCount, DownloadCount, UploadCount, SiteCount, NewSiteCount, NewSites, SourceIPs, IPCount, Department, JobTitle, Manager, Workloads, TenantAccountsSeen, NewAdoptersInWindow, UserBaselineEvents.
KQL
// =====================================================================
// SharePoint Mass File Access From a First-Time Client
// =====================================================================
// Description : Detects an account moving SharePoint or OneDrive documents
// through a client family it has not used in the preceding
// 14 days, scored on client type, volume, site spread and
// directory state. Client strings are normalised to a
// version free family, and rollout breadth mutes the
// novelty weights, so browser updates and estate wide
// deployments do not reach the gate.
// Type : Detection
//
// Tables : OfficeActivity, IdentityInfo
// Connectors : Office 365 (SharePoint); Microsoft Sentinel UEBA for IdentityInfo
// License : Office 365 E3 or above for the audit data. IdentityInfo needs
// UEBA enabled (Entra ID P1 minimum). Without it the disabled,
// guest and privileged signals go silent and the rule continues
// on client and volume signals.
//
// Tuning : - Set the rule query period to P14D; the 14d client and site
// lookups only resolve if the rule itself looks back that far
// - DetectionWindow - which events may fire; must equal the run
// frequency, change one and you change the other
// - IngestionLag - real OfficeActivity delay; measure with
// ingestion_time() - TimeGenerated and set at or above P99
// - MinFileCount - floor for a row to be scored at all
// - BulkFileCount - the only signal that fires on its own
// - RolloutUsers - adopters in one window that mark a deployment
//
// Known FPs : - Browser and Office version churn - ClientFamily strips digits
// so Chrome 139 and Chrome 140 are one family
// - Backup or bulk agents deployed to many accounts at once -
// rollout suppression zeroes the client and rare weights
// - Content migration tooling (ShareGate, Mover) on a dedicated
// account - no baseline on day one, own history by day two
// - Guests opening a shared library from a personal browser -
// scores 30 and stays below the gate
// - Disabled accounts finishing on pre-existing tokens - scores
// 60 and never fires alone
//
// Author : Bartosz Wysocki | https://www.itprofessor.cloud
// Version : 1.0 | 2026-08-14
// =====================================================================
// Which events may fire: must equal the rule's run frequency
let DetectionWindow = 1d;
// Real ingestion delay of OfficeActivity; widens sight, never firing
let IngestionLag = 12h;
// How far back client and site history is built
let BaselineWindow = 14d;
// How far back directory context is read
let IdentityLookback = 7d;
// Distinct files an account must touch through the new client to be scored
let MinFileCount = 25;
// Distinct files that count as a high volume day
let HighFileCount = 100;
// Distinct files that count as bulk retrieval
let BulkFileCount = 500;
// History an account needs before an absent client family means anything
let MinBaselineEvents = 50;
// Accounts a client family may already cover and still count as rare
let RareClientUsers = 3;
// New adopters in one window above which a client is treated as a rollout
let RolloutUsers = 10;
// Sites with no account history before the spread signal counts
let NewSiteThreshold = 3;
// Score at which a row becomes an incident
let FireThreshold = 100;
// Scoring: bulk retrieval fires alone, every other signal only stacks
let W_ScriptedClient = 45;
let W_UnknownClient = 25;
let W_RareClient = 35;
let W_VolumeBulk = 100;
let W_VolumeHigh = 40;
let W_NewSiteSpread = 25;
let W_DisabledAccount = 60;
let W_GuestAccount = 30;
let W_PrivilegedRole = 25;
// Operations in scope; sync operations belong to a companion rule
let FileOperations = dynamic(["FileDownloaded", "FileUploaded"]);
// SharePoint records its own service activity under these names
let SystemAccounts = dynamic(["app@sharepoint", "system"]);
// Client strings that identify scripted or library based access
let ScriptedPattern = "(python|urllib|requests|curl|wget|libwww|go-http|okhttp|java/|httpclient|axios|node-fetch|httpx|aiohttp|powershell|restsharp|rclone|postman|insomnia|msal)";
// Client strings that identify a browser or a Microsoft first party client
let KnownClientPattern = "(mozilla|applewebkit|gecko|chrome|safari|edg|firefox|opera|microsoft office|skydrivesync|onedrive|sharepoint|outlook|teams|excel|word|powerpoint|onenote)";
// Strips version numbers so a browser update does not read as a new client
let ClientFamily = (ua:string) {
trim(" +", replace_regex(replace_regex(tolower(ua), "[0-9]+", ""), "[. _]+", " "))
};
// Widest event age the seed may look at
let SeedWindow = DetectionWindow + IngestionLag;
// Client and site history, ending before the seed can see anything
let BaselineGrain = materialize(
OfficeActivity
| where TimeGenerated between (ago(BaselineWindow) .. ago(SeedWindow))
| where RecordType == "SharePointFileOperation"
| where Operation in (FileOperations)
| where isnotempty(UserAgent)
| where isnotempty(UserId)
| extend UserId = tolower(UserId)
| where UserId !in (SystemAccounts)
| extend ClientId = ClientFamily(UserAgent)
| summarize BaselineEvents = count() by UserId, ClientId, Site_Url
);
// Client families each account has already used
let KnownClients = BaselineGrain
| distinct UserId, ClientId;
// Sites each account has already touched
let KnownSites = BaselineGrain
| distinct UserId, Site_Url;
// How many accounts each client family already covers across the tenant
let ClientReach = BaselineGrain
| summarize BaselineUsers = dcount(UserId) by ClientId;
// Accounts with enough history for a first time client to mean anything
let BaselinedUsers = BaselineGrain
| summarize UserBaselineEvents = sum(BaselineEvents) by UserId
| where UserBaselineEvents >= MinBaselineEvents;
// Directory context; without UEBA these signals go silent, never wrong
let Identity = IdentityInfo
| where TimeGenerated >= ago(IdentityLookback)
| where isnotempty(AccountUPN)
| extend UserId = tolower(AccountUPN)
| summarize arg_max(TimeGenerated, AccountDisplayName, JobTitle, Department, Manager,
IsAccountEnabled, AssignedRoles, UserType) by UserId
| project UserId, AccountDisplayName, JobTitle, Department, Manager,
IsDisabled = isnotnull(IsAccountEnabled) and IsAccountEnabled == false,
IsDirGuest = UserType =~ "Guest",
IsPrivileged = coalesce(array_length(todynamic(AssignedRoles)), 0) > 0;
// Accounts using a client family they have no history with
let Candidates = materialize(
OfficeActivity
| extend IngestedAt = ingestion_time()
| where TimeGenerated >= ago(SeedWindow)
| where IngestedAt >= ago(DetectionWindow)
| where RecordType == "SharePointFileOperation"
| where Operation in (FileOperations)
| where isnotempty(UserAgent)
| where isnotempty(UserId)
| extend UserId = tolower(UserId)
| where UserId !in (SystemAccounts)
| extend ClientId = ClientFamily(UserAgent)
| project TimeGenerated, UserId, ClientId, UserAgent, Operation, Site_Url,
OfficeObjectId, ClientIP, OfficeWorkload
| lookup kind=leftouter (KnownSites | extend SiteKnown = true) on UserId, Site_Url
| extend SiteKnown = coalesce(SiteKnown, false)
| summarize
StartTime = min(TimeGenerated),
EndTime = max(TimeGenerated),
FileCount = dcount(OfficeObjectId),
DownloadCount = countif(Operation == "FileDownloaded"),
UploadCount = countif(Operation == "FileUploaded"),
SiteCount = dcount(Site_Url),
NewSiteCount = dcountif(Site_Url, SiteKnown == false),
NewSites = make_set_if(Site_Url, SiteKnown == false, 5),
SourceIPs = make_set(ClientIP, 5),
IPCount = dcount(ClientIP),
RawAgent = take_any(UserAgent),
Workloads = make_set(OfficeWorkload, 3)
by UserId, ClientId
| where FileCount >= MinFileCount
| lookup kind=inner (BaselinedUsers) on UserId
| join kind=leftanti (KnownClients) on UserId, ClientId
);
// How many accounts newly adopted each client family inside this window
let ClientAdoption = Candidates
| summarize WindowAdopters = dcount(UserId) by ClientId;
Candidates
| lookup kind=leftouter (ClientReach) on ClientId
| lookup kind=leftouter (ClientAdoption) on ClientId
| lookup kind=leftouter (Identity) on UserId
| extend BaselineUsers = coalesce(BaselineUsers, 0)
| extend WindowAdopters = coalesce(WindowAdopters, 1)
| extend IsRollout = WindowAdopters >= RolloutUsers
| extend IsScripted = ClientId matches regex ScriptedPattern
| extend IsKnownClient = ClientId matches regex KnownClientPattern
| extend IsRareClient = BaselineUsers < RareClientUsers and not(IsRollout)
| extend IsGuest = coalesce(IsDirGuest, false) or UserId contains "#ext#"
| extend IsDisabled = coalesce(IsDisabled, false)
| extend IsPrivileged = coalesce(IsPrivileged, false)
| extend ClientWeight = case(IsRollout, 0,
IsScripted, W_ScriptedClient,
not(IsKnownClient), W_UnknownClient,
0)
| extend VolumeWeight = case(FileCount >= BulkFileCount and not(IsRollout), W_VolumeBulk,
FileCount >= HighFileCount, W_VolumeHigh,
0)
| extend RiskScore = ClientWeight + VolumeWeight
+ iff(IsRareClient, W_RareClient, 0)
+ iff(NewSiteCount >= NewSiteThreshold, W_NewSiteSpread, 0)
+ iff(IsDisabled, W_DisabledAccount, 0)
+ iff(IsGuest, W_GuestAccount, 0)
+ iff(IsPrivileged, W_PrivilegedRole, 0)
| where RiskScore >= FireThreshold
| extend Risk_1 = iff(ClientWeight == W_ScriptedClient, strcat("Scripted client: ", ClientId), "")
| extend Risk_2 = iff(ClientWeight == W_UnknownClient, strcat("Unrecognised client: ", ClientId), "")
| extend Risk_3 = iff(IsRareClient, strcat("Client rare in tenant (", tostring(BaselineUsers), " prior accounts)"), "")
| extend Risk_4 = case(VolumeWeight == W_VolumeBulk, strcat("Bulk retrieval: ", tostring(FileCount), " files"),
VolumeWeight == W_VolumeHigh, strcat("High volume: ", tostring(FileCount), " files"),
"")
| extend Risk_5 = iff(NewSiteCount >= NewSiteThreshold, strcat("Reached ", tostring(NewSiteCount), " sites with no account history"), "")
| extend Risk_6 = iff(IsDisabled, "Account disabled in the directory", "")
| extend Risk_7 = iff(IsGuest, "Guest account", "")
| extend Risk_8 = iff(IsPrivileged, "Account holds a directory role", "")
| extend RiskIndicators = trim(@"\s\|\s*$", strcat(
iff(isnotempty(Risk_1), strcat(Risk_1, " | "), ""),
iff(isnotempty(Risk_2), strcat(Risk_2, " | "), ""),
iff(isnotempty(Risk_3), strcat(Risk_3, " | "), ""),
iff(isnotempty(Risk_4), strcat(Risk_4, " | "), ""),
iff(isnotempty(Risk_5), strcat(Risk_5, " | "), ""),
iff(isnotempty(Risk_6), strcat(Risk_6, " | "), ""),
iff(isnotempty(Risk_7), strcat(Risk_7, " | "), ""),
iff(isnotempty(Risk_8), strcat(Risk_8, " | "), "")
))
| extend GuestName = extract("^(.+)_[^_]+#ext#", 1, UserId)
| extend GuestDomain = extract("_([^_]+)#ext#", 1, UserId)
| extend AccountName = iff(isnotempty(GuestName), GuestName, tostring(split(UserId, "@", 0)[0]))
| extend AccountUPNSuffix = iff(isnotempty(GuestDomain), GuestDomain, tostring(split(UserId, "@", 1)[0]))
| extend PrimaryIP = tostring(SourceIPs[0])
| project StartTime, EndTime, RiskScore, RiskIndicators, UserId, AccountName, AccountUPNSuffix,
AccountDisplayName, JobTitle, Department, Manager, ClientId, RawAgent,
FileCount, DownloadCount, UploadCount, SiteCount, NewSiteCount, NewSites,
IPCount, PrimaryIP, SourceIPs, Workloads, BaselineUsers, WindowAdopters, UserBaselineEvents
| sort by RiskScore desc, EndTime desc
You can also download this as an analytic rule and import it directly to Sentinel.
Follow my repo - GitHub
What You Should Do Next
Run it in Logs with the gate commented out for a week and look at the score distribution. Set BulkFileCount above the largest legitimate single day browser download you can find in your own data rather than at my 500.
Check both client patterns against your own strings. Every tenant runs line of business applications with their own user agents, and any of yours missing from KnownClientPattern will score 25 every time it appears for a new account.
Decide who owns the sync path. This rule deliberately leaves it alone, and that is where the quiet library drain happens.
Class dismissed.
