Microsoft SentinelKQLAnalytic Rules
Fixing the "External user added and removed in short timeframe" Analytic Rule
Alright class.
Today: External user added and removed in short timeframe, a content hub rule that watches Microsoft Teams for a guest being added to a team and removed again shortly afterwards.
The premise is good. Someone who has taken over an internal account can invite an external identity they control, drop it into a team worth reading, let it pull the files, then remove the membership and hope nobody looks. The pair is the cover up. This rule does not catch it, misses about half the pattern it claims to detect, and when it does fire it names the wrong team.
The join has no team in it
TeamsAdd
| join kind=inner (TeamsDel) on $left.MemberAdded == $right.MemberRemoved
| where TimeDeleted > TimeAdded
The join key is the guest UPN. The team is carried in both branches and never used to match anything.
Contoso has chris_northwind.com#EXT#@contoso.onmicrosoft.com, a partner contact of two years standing.
- 09:10, Alice adds Chris to Partner Programme.
- 09:40, Bob removes Chris from Legacy Migration 2019, a dead team, during housekeeping.
Two unrelated events, two teams, two people, both benign. The UPNs match, TimeDeleted > TimeAdded passes, the rule fires.
What it should be looking for
The pair is not a finding. It is a trigger, and it happens dozens of times a month in any tenant with partners.
The finding is what the guest did in between. Forty minutes and sixty documents out of the channel Files tab is data theft. Four minutes and no sign-in is somebody who picked the wrong Chris from the member picker. The Teams telemetry is identical. The evidence is in SharePoint, in sign-in logs and in the directory.
The false positive you will actually see
The correction. A team owner opens the member picker, types three letters, picks the wrong contact, adds them, notices, removes them. Constant, and a perfect pair inside two minutes. Access reviews and entitlement expiry remove guests in batches from a service account. And external board members and auditors get added for a meeting and removed afterwards, every quarter, for years.
Write out the note the analyst would put on that last one when closing it. Long-standing guest. Signed in with MFA. Read the pack, took no copy. The firm already has four other guests in our directory. Every fact in that note is computable, so the rule computes them and the row never reaches a human: reading without downloading scores 35, and the established supplier weight takes it to 15.
That is what the negative weight is for. Three or more guests already carrying the same home domain is positive evidence of a real relationship, and it subtracts 20. It is capped there on purpose. A download still scores 65, so a stolen supplier identity clears the gate at exactly 45 with maximum exculpation applied.
The rule hands the rest of the closure checks over as columns: whether the guest signed in and with what factor, from where, what they opened and downloaded, how long they held membership, how many guests share their domain, and whether the adding account is enabled, who manages it and what roles it holds.
Silent on purpose: any pair with no sign-in and no file activity, however short the residency. Non-interactive sign-ins are excluded entirely, because token refresh continues after the removal and proves nothing.
The rebuild
| where RiskScore >= FireThreshold
FireThreshold is 45, and one signal clears it alone:
let W_GuestDownload = 65; // clears the gate through full exculpation
A download, sync or copy out of SharePoint or OneDrive between the add and the removal fires regardless of everything else. The rest stack: opening content without copying, 20; an interactive sign-in inside the window, 15; an invitation or redemption seen in the last fourteen days, 20; more than one team, 20; a repeat pattern on the adding account, 25; risky sign-ins on that account, 25.
The RiskIndicators field
Guest downloaded or synced 61 items across 2 sites while inside the team | Guest signed in interactively during the membership window from Lagos, NG | Guest invitation or redemption observed 1 days ago
One field, readable in the incident list, worth about twenty minutes of pivoting per alert.
The combination to chase is downloads plus an invitation seen days ago. An identity invited this morning, added, used to pull files, then removed is not a mistake, it is a sequence. Find who sent the invitation, because that is where the compromise starts. The guest is only tooling.
The blind spots you should know about
Shared channels are out of scope. A guest left in place is invisible by design, because this rule owns the add and remove axis and guest arrival belongs to the invitation rule in this series. Teams chat is not covered, so read-on-screen-and-photograph scores 35 and stays silent.
The query remembers fourteen days, so absence proves nothing. A guest invited earlier is simply not aged, and the recency signal stays quiet rather than calling them new. A first contact at a real supplier gets no exculpation and scores higher than they deserve.
The repeat check only counts guests the same account both added and removed, so add by the attacker and cleanup by the helpdesk never accumulates. And an auditor who genuinely bulk downloads in a scheduled window fires at 45. That is deliberate.
MITRE mappings for the updated rule
T1078.004, Valid Accounts: Cloud Accounts. The external identity is a legitimate cloud account used as the path into internal content.
T1098, Account Manipulation. Adding an external identity to a team is a membership change made to obtain access. No cloud group sub-technique fits cleanly, so this stays at technique level.
T1213.002, Data from Information Repositories: SharePoint. The signal carrying the rule is the guest reading and copying team content, which lives in the team SharePoint site.
T1070.009, Indicator Removal: Clear Persistence. The closest documented match for clearing the access artefact just created. Nearest fit rather than exact, and I would rather say so.
Rule settings
Frequency: every 1 hour. DetectionWindow in the query matches it.
Query period: 14 days, equal to the largest lookup in the query.
Severity: Medium. A download is strong evidence of access, not confirmation of loss.
Event grouping: trigger an alert for each event. The query collapses to one row per guest.
Incident grouping: on, eight hours, Selected, by Account. The adding account is an entity, so one compromised account sweeping several guests arrives as one incident rather than six.
Entity mapping: Account on GuestAccountName and GuestUPNSuffix, the guest as the tenant sees them. Account on GuestHomeName and GuestHomeDomain, the guest as their employer sees them, which is what you hunt on. Account on AddedByName and AddedBySuffix. IP on GuestSignInIP and on AddedByIP. The removing account is deliberately not an entity: housekeeping is often one shared administrator, and mapping them would glue unrelated incidents together.
No alert display name override. The rule name stays static so volume stays countable in SecurityAlert.
Custom details: RiskScore, RiskIndicators, GuestHomeUPN, GuestSeenDays, PartnerGuests, TeamsAffected, ResidencyMins, AddedBy, RemovedBy, AddedByDept, AddedByTitle, AddedByManager, AddedByRoles, AddedByEnabled, AddedByRisk, PriorPairs, FilesDownloaded, TopFiles, GuestMFA.
KQL
// =====================================================================
// External Guest Added And Removed From Teams With Evidence Of Access
// =====================================================================
// Description : Detects an external guest added to a Microsoft Teams team and removed from
// that same team within two hours, then scores the pair on what the guest did
// while inside: SharePoint or OneDrive downloads, file opens, interactive sign
// ins, invitation recency, multi team churn, and repeat behaviour and risk on
// the adding account. The benign correction (wrong contact added and removed
// within minutes, no sign in, no file activity) scores zero and is suppressed
// by the score gate, and an established supplier relationship subtracts 20.
// Type : Detection
//
// Tables : OfficeActivity, SigninLogs, AuditLogs, IdentityInfo
// Connectors : Microsoft 365 (Teams plus SharePoint and OneDrive workloads),
// Microsoft Entra ID (SigninLogs and AuditLogs), UEBA for IdentityInfo
// License : Microsoft 365 E3 with unified audit logging; Entra ID P2 adds the actor risk
// signal via RiskLevelDuringSignIn, which is hidden without it
//
// Tuning : - Set the rule query period to P14D; the 14d onboarding, partner domain and
// actor risk lookups only resolve if the rule itself looks back that far
// - Set the rule frequency to PT1H; DetectionWindow must equal it. The seed is
// bounded on ingestion_time() so a late removal still fires exactly once
// - IngestionLag - real OfficeActivity delay, widens sight not firing
// - GovernanceAccounts - guest lifecycle service accounts, fill before enabling
// - MaxResidency - longest add to remove gap that still counts, default 2h
// - PartnerFloor - guests sharing a home domain before the supplier
// relationship counts as established, default 3
// - AdderLookback - repeat pattern scan, default 7d, most expensive lookup
// - TeamGuid falls back to TeamName if the tenant does not populate it
//
// Known FPs : - Wrong contact picked in the member picker and removed immediately - no sign
// in and no file activity, scores 0 or below, suppressed by the gate
// - Access review and entitlement expiry batches - add the service account to
// GovernanceAccounts
// - External board member or auditor reading a pack and leaving - scores 35,
// taken to 15 by the established supplier weight
// - IT staff testing guest access - repeats score 25 alone, below the gate
// - Non-interactive token refresh - excluded by design, it continues after
// removal and proves nothing
//
// Author : Bartosz Wysocki | https://www.itprofessor.cloud
// Version : 1.0 | 2026-08-12
// =====================================================================
// ---------------------------------------------------------------------
// Tuning
// ---------------------------------------------------------------------
// Which events may fire: must equal the rule's run frequency
let DetectionWindow = 1h;
// Real ingestion delay of the seed table; widens sight, not firing
let IngestionLag = 4h;
let MaxResidency = 2h; // longest add to remove gap that still counts
let HistoryLookback = 14d; // onboarding, actor risk and partner domain lookups
let AdderLookback = 7d; // repeat pattern scan, the most expensive lookup
let IdentityLookback = 14d; // IdentityInfo snapshot window
let RepeatThreshold = 2; // prior guests this actor both added and removed before it is a pattern
let PartnerFloor = 3; // other guests on the same home domain before the relationship is established
// Event time reach of the seed, wide enough to pair a removal with its add
let SeedEventWindow = DetectionWindow + IngestionLag + MaxResidency;
// ---------------------------------------------------------------------
// Scoring, additive, gated at FireThreshold
// ---------------------------------------------------------------------
let W_GuestDownload = 65; // guest took a copy of team content, clears the gate through full exculpation
let W_GuestFileAccess = 20; // guest opened content but took no copy
let W_GuestSignedIn = 15; // successful interactive guest sign in inside the membership window
let W_GuestRecent = 20; // invitation or redemption observed inside the history window
let W_MultiTeam = 20; // same guest added and removed across more than one team
let W_RepeatAdder = 25; // this actor has run the same add and remove pattern before
let W_AdderRisk = 25; // adding account had a medium or high risk sign in, needs Entra ID P2
let W_EstablishedPartner = -20; // exculpatory cap: home domain already supplies several guests
let FireThreshold = 45;
// ---------------------------------------------------------------------
// Lists
// ---------------------------------------------------------------------
let GovernanceAccounts = dynamic([]); // guest lifecycle service accounts, lower case
let DownloadOps = dynamic(["FileDownloaded", "FileSyncDownloadedFull", "FileSyncDownloadedPartial", "FileCopied"]);
let ReadOps = dynamic(["FileAccessed", "FileAccessedExtended", "FilePreviewed", "ListItemViewed", "PageViewed"]);
// ---------------------------------------------------------------------
// Helpers
// ---------------------------------------------------------------------
// chris_northwind.com#EXT#@contoso.onmicrosoft.com becomes chris@northwind.com.
// contains, never has: #EXT# is not a term, so has "#EXT#" is false on every value.
let ToHomeUPN = (upn: string) {
let stub = tostring(split(upn, "#EXT#", 0)[0]);
let domain = extract(@"_([^_]+)$", 1, stub);
tolower(iff(upn contains "#EXT#" and isnotempty(domain),
strcat(substring(stub, 0, strlen(stub) - strlen(domain) - 1), "@", domain),
upn))
};
// One row per external member per Teams membership event.
let ExternalMemberOps = (op: string, eventWindow: timespan) {
OfficeActivity
| extend IngestedAt = ingestion_time()
| where TimeGenerated >= ago(eventWindow)
| where OfficeWorkload == "MicrosoftTeams"
| where Operation == op
| where Members contains "#EXT#"
| mv-apply M = todynamic(Members) on (
where tostring(M.UPN) contains "#EXT#"
| project GuestUPN = tolower(tostring(M.UPN))
)
| project TimeGenerated, IngestedAt, GuestUPN, TeamName,
TeamKey = tolower(iff(isnotempty(TeamGuid), TeamGuid, TeamName)),
Actor = tolower(UserId), ActorIP = ClientIP
};
// ---------------------------------------------------------------------
// Seed: removals ingested in this run interval, paired to their own add
// ---------------------------------------------------------------------
let Removals =
ExternalMemberOps("MemberRemoved", SeedEventWindow)
| where IngestedAt >= ago(DetectionWindow)
| project TimeRemoved = TimeGenerated, GuestUPN, TeamKey, TeamName, RemovedBy = Actor;
let Additions =
ExternalMemberOps("MemberAdded", SeedEventWindow)
| project TimeAdded = TimeGenerated, GuestUPN, TeamKey, AddedBy = Actor, AddedByIP = ActorIP;
let Pairs =
Removals
| join kind=inner (Additions) on GuestUPN, TeamKey
| where TimeAdded < TimeRemoved and TimeRemoved - TimeAdded <= MaxResidency
| summarize arg_max(TimeAdded, TeamName, AddedBy, AddedByIP, RemovedBy) by GuestUPN, TeamKey, TimeRemoved
| where not(set_has_element(GovernanceAccounts, AddedBy))
| extend ResidencyMins = toint(datetime_diff("minute", TimeRemoved, TimeAdded));
// One row per guest, so a multi team sweep lands as a single finding.
let GuestSet = materialize(
Pairs
| summarize FirstAdded = min(TimeAdded), LastRemoved = max(TimeRemoved),
ResidencyMins = max(ResidencyMins), TeamCount = dcount(TeamKey),
TeamsAffected = strcat_array(make_set(TeamName, 10), ", "),
Adders = make_set(AddedBy, 5), Removers = make_set(RemovedBy, 5),
AddedByIP = take_any(AddedByIP)
by GuestUPN
| extend GuestHomeUPN = ToHomeUPN(GuestUPN),
AddedBy = tostring(Adders[0]), RemovedBy = tostring(Removers[0]),
SameActor = iff(array_length(set_difference(Removers, Adders)) == 0, "Yes", "No")
| extend GuestHomeDomain = tostring(split(GuestHomeUPN, "@", 1)[0])
);
// ---------------------------------------------------------------------
// Lookups, all attached to the seed so they decorate rows, never add them
// ---------------------------------------------------------------------
// What the guest touched in SharePoint or OneDrive between add and removal.
let GuestFileOps =
OfficeActivity
| where TimeGenerated >= ago(SeedEventWindow)
| where OfficeWorkload in ("SharePoint", "OneDrive")
| extend GuestUPN = tolower(UserId)
| where GuestUPN in ((GuestSet | project GuestUPN))
| project TimeGenerated, GuestUPN, Operation, SourceFileName, Site_Url
| join kind=inner (GuestSet | project GuestUPN, FirstAdded, LastRemoved) on GuestUPN
| where TimeGenerated between (FirstAdded .. LastRemoved)
| summarize Downloads = countif(set_has_element(DownloadOps, Operation)),
Reads = countif(set_has_element(ReadOps, Operation)),
SitesTouched = dcount(Site_Url),
TopFiles = strcat_array(make_set(SourceFileName, 8), ", ")
by GuestUPN;
// Interactive guest sign ins inside the membership window. Non-interactive token
// refresh is deliberately out of scope: it continues after removal and proves nothing.
let GuestSignIns =
SigninLogs
| where TimeGenerated >= ago(SeedEventWindow)
| where ResultType == 0
| extend GuestUPN = tolower(UserPrincipalName)
| where GuestUPN in ((GuestSet | project GuestUPN))
| project TimeGenerated, GuestUPN, IPAddress, AuthenticationRequirement,
GeoCity = tostring(LocationDetails.city),
GeoCountry = tostring(LocationDetails.countryOrRegion)
| join kind=inner (GuestSet | project GuestUPN, FirstAdded, LastRemoved) on GuestUPN
| where TimeGenerated between (FirstAdded .. LastRemoved)
| summarize SignInCount = count(),
arg_max(TimeGenerated, IPAddress, AuthenticationRequirement, GeoCity, GeoCountry) by GuestUPN
| project GuestUPN, SignInCount, GuestSignInIP = IPAddress,
GuestSignInGeo = strcat(GeoCity, ", ", GeoCountry),
GuestMFA = iff(AuthenticationRequirement =~ "multiFactorAuthentication", "Yes", "No");
// Invitation or redemption observed in the window. Both sides normalise to the home
// UPN, because the invite carries the real address and the redemption the mangled one.
let GuestOnboarding =
AuditLogs
| where TimeGenerated >= ago(HistoryLookback)
| where OperationName in ("Invite external user", "Redeem external user invite", "Add user")
| mv-apply TR = todynamic(TargetResources) on (
where tostring(TR.type) =~ "User" and isnotempty(tostring(TR.userPrincipalName))
| project TargetUPN = tolower(tostring(TR.userPrincipalName))
)
| extend GuestHomeUPN = ToHomeUPN(TargetUPN)
| where GuestHomeUPN in ((GuestSet | project GuestHomeUPN))
| summarize GuestSeenFrom = min(TimeGenerated) by GuestHomeUPN;
// Established supplier: other guests already carrying the same home domain.
let PartnerDomains =
IdentityInfo
| where TimeGenerated >= ago(IdentityLookback)
| where AccountUPN contains "#EXT#"
| summarize by AccountUPN = tolower(AccountUPN)
| extend GuestHomeDomain = tostring(split(ToHomeUPN(AccountUPN), "@", 1)[0])
| where GuestHomeDomain in ((GuestSet | project GuestHomeDomain))
| summarize PartnerGuests = dcount(AccountUPN) by GuestHomeDomain;
// Repeat pattern by the adding account. The window stops short of the detection
// window so the event being scored cannot baseline itself.
let AdderHistory =
OfficeActivity
| where TimeGenerated between (ago(AdderLookback) .. ago(DetectionWindow))
| where OfficeWorkload == "MicrosoftTeams"
| where Operation in ("MemberAdded", "MemberRemoved")
| where Members contains "#EXT#"
| extend Actor = tolower(UserId)
| where Actor in ((GuestSet | project AddedBy))
| mv-apply M = todynamic(Members) on (
where tostring(M.UPN) contains "#EXT#"
| project PastGuest = tolower(tostring(M.UPN))
)
| summarize Adds = countif(Operation == "MemberAdded"), Removes = countif(Operation == "MemberRemoved")
by Actor, PastGuest
| where Adds > 0 and Removes > 0
| summarize PriorPairs = dcount(PastGuest) by AddedBy = Actor;
// Standing directory context for the internal actor.
let ActorIdentity =
IdentityInfo
| where TimeGenerated >= ago(IdentityLookback)
| extend AddedBy = tolower(AccountUPN)
| where AddedBy in ((GuestSet | project AddedBy))
| summarize arg_max(TimeGenerated, Department, JobTitle, Manager, AssignedRoles, IsAccountEnabled) by AddedBy
| project AddedBy, AddedByDept = Department, AddedByTitle = JobTitle,
AddedByManager = tostring(Manager),
AddedByRoles = strcat_array(todynamic(AssignedRoles), ", "),
AddedByEnabled = tostring(IsAccountEnabled);
// Actor risk. Without Entra ID P2 the value is hidden and this stays silent.
let ActorRisk =
SigninLogs
| where TimeGenerated >= ago(HistoryLookback)
| where RiskLevelDuringSignIn in ("high", "medium")
| extend AddedBy = tolower(UserPrincipalName)
| where AddedBy in ((GuestSet | project AddedBy))
| summarize RiskSignIns = count(),
AddedByRisk = strcat_array(make_set(RiskLevelDuringSignIn, 2), ", ") by AddedBy;
// ---------------------------------------------------------------------
// Score and output
// ---------------------------------------------------------------------
GuestSet
| join kind=leftouter (GuestFileOps) on GuestUPN
| join kind=leftouter (GuestSignIns) on GuestUPN
| join kind=leftouter (GuestOnboarding) on GuestHomeUPN
| join kind=leftouter (PartnerDomains) on GuestHomeDomain
| join kind=leftouter (AdderHistory) on AddedBy
| join kind=leftouter (ActorIdentity) on AddedBy
| join kind=leftouter (ActorRisk) on AddedBy
| extend Downloads = coalesce(Downloads, 0), Reads = coalesce(Reads, 0),
SitesTouched = coalesce(SitesTouched, 0), SignInCount = coalesce(SignInCount, 0),
PriorPairs = coalesce(PriorPairs, 0), PartnerGuests = coalesce(PartnerGuests, 0),
GuestSeenDays = iff(isnotnull(GuestSeenFrom), toint(datetime_diff("day", now(), GuestSeenFrom)), int(null))
| extend
Risk_1 = iff(Downloads > 0, strcat("Guest downloaded or synced ", Downloads, " items across ", SitesTouched, " sites while inside the team"), ""),
Risk_2 = iff(Downloads == 0 and Reads > 0, strcat("Guest opened ", Reads, " items but took no copy"), ""),
Risk_3 = iff(SignInCount > 0, strcat("Guest signed in interactively during the membership window from ", GuestSignInGeo), ""),
Risk_4 = iff(isnotnull(GuestSeenFrom), strcat("Guest invitation or redemption observed ", GuestSeenDays, " days ago"), ""),
Risk_5 = iff(TeamCount > 1, strcat("Same guest added and removed across ", TeamCount, " teams"), ""),
Risk_6 = iff(PriorPairs >= RepeatThreshold, strcat(AddedBy, " added and removed ", PriorPairs, " other external accounts in the last 7 days"), ""),
Risk_7 = iff(isnotempty(AddedByRisk), strcat("Adding account had ", RiskSignIns, " sign ins rated ", AddedByRisk), ""),
Risk_8 = iff(PartnerGuests >= PartnerFloor, strcat("Established supplier: ", PartnerGuests, " guests already carry the domain ", GuestHomeDomain), "")
| extend RiskScore = iff(isnotempty(Risk_1), W_GuestDownload, 0)
+ iff(isnotempty(Risk_2), W_GuestFileAccess, 0)
+ iff(isnotempty(Risk_3), W_GuestSignedIn, 0)
+ iff(isnotempty(Risk_4), W_GuestRecent, 0)
+ iff(isnotempty(Risk_5), W_MultiTeam, 0)
+ iff(isnotempty(Risk_6), W_RepeatAdder, 0)
+ iff(isnotempty(Risk_7), W_AdderRisk, 0)
+ iff(isnotempty(Risk_8), W_EstablishedPartner, 0)
| where RiskScore >= FireThreshold
| 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 GuestAccountName = tostring(split(GuestUPN, "@", 0)[0]),
GuestUPNSuffix = tostring(split(GuestUPN, "@", 1)[0]),
GuestHomeName = tostring(split(GuestHomeUPN, "@", 0)[0]),
AddedByName = tostring(split(AddedBy, "@", 0)[0]),
AddedBySuffix = tostring(split(AddedBy, "@", 1)[0])
| project TimeGenerated = LastRemoved, StartTime = FirstAdded, EndTime = LastRemoved,
RiskScore, RiskIndicators, GuestUPN, GuestHomeUPN, GuestHomeDomain, GuestSeenDays,
PartnerGuests, TeamsAffected, TeamCount, ResidencyMins, AddedBy, RemovedBy, SameActor,
AddedByDept, AddedByTitle, AddedByManager, AddedByRoles, AddedByEnabled, AddedByRisk,
PriorPairs, FilesDownloaded = Downloads, FilesAccessed = Reads, TopFiles, SitesTouched,
GuestSignInIP, GuestSignInGeo, GuestMFA, AddedByIP,
GuestAccountName, GuestUPNSuffix, GuestHomeName, AddedByName, AddedBySuffix
| 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 ExternalMemberOps on its own over fourteen days first. If MemberAdded and MemberRemoved rows are not in OfficeActivity with a populated Members array, the rule cannot work and tuning will not change it.
Fill in GovernanceAccounts with your guest lifecycle service accounts. One line removes the most predictable source of repeat pairs.
Run it without the gate for fourteen days. Comment out the gate and see how pairs land across the range, including the negative ones. If your benign corrections do not sit at or below zero, you have a local pattern worth naming before go live.
Class dismissed.