Query Details
HUNT 10 M365 Share Point Admin Permission Audit 90d
Query
// Hunt : M365 - SharePoint Admin Role and Permission Changes (90d)
// Purpose : Full audit trail of SharePoint site permission escalations,
// site collection admin grants, and sharing policy changes over 90 days.
// Supports access review, privilege escalation, and compliance audits.
// Tables : OfficeActivity
// Period : P90D
//==========================================================================================
let LookbackDays = 90d;
OfficeActivity
| where TimeGenerated > ago(LookbackDays)
| where RecordType == "SharePoint"
| where Operation in (
"SiteCollectionAdminAdded", "SiteCollectionAdminRemoved",
"PermissionLevelAdded", "PermissionLevelRemoved",
"GroupMemberAdded", "GroupMemberRemoved",
"SitePermissionsModified", "SharingPolicyChanged",
"SiteCollectionCreated", "SiteDeleted")
| extend
TargetUser = tostring(TargetUserOrGroupName),
RoleName = tostring(parse_json(Event_Data).RoleAssignment),
SiteLower = tolower(Site_Url)
| extend
IsAdmin = Operation has "Admin" or RoleName has "Full Control",
IsGuest = TargetUser has "#EXT#" or TargetUser has "guest",
IsSensitive = SiteLower has_any (dynamic([
"hr", "legal", "finance", "exec", "security", "payroll", "board"
]))
| summarize
OpCount = count(),
Operations = make_set(Operation, 10),
Targets = make_set(TargetUser, 10),
SitesAffected = make_set(Site_Url, 10),
AdminOps = countif(IsAdmin),
GuestOps = countif(IsGuest),
FirstChange = min(TimeGenerated),
LastChange = max(TimeGenerated)
by UserId
| sort by AdminOps desc
| project
UserId,
OpCount,
AdminOps,
GuestOps,
Operations,
Targets,
SitesAffected,
FirstChange,
LastChange
Explanation
This query is designed to track and analyze changes in SharePoint permissions and roles over the past 90 days. It focuses on identifying and summarizing activities related to permission escalations, admin role assignments, and sharing policy modifications. Here's a breakdown of what the query does:
-
Time Frame: It looks at data from the last 90 days.
-
Data Source: The query uses the
OfficeActivitytable, specifically filtering for records related to SharePoint. -
Operations of Interest: It filters for specific operations such as adding or removing site collection admins, modifying permissions, changing sharing policies, and creating or deleting site collections.
-
Data Enrichment:
- Extracts the target user or group name and role assignment.
- Converts site URLs to lowercase for consistent comparison.
- Flags operations involving admin roles or full control, guest users, and sensitive sites (e.g., HR, legal, finance).
-
Summary Metrics:
- Counts the total number of operations per user.
- Lists up to 10 unique operations, target users, and affected sites.
- Counts operations involving admin roles and guest users.
- Identifies the first and last change timestamps for each user.
-
Sorting and Presentation:
- Sorts the results by the number of admin operations in descending order.
- Projects key information such as user ID, operation counts, and time of changes.
The query is useful for auditing and reviewing access changes, detecting privilege escalations, and ensuring compliance with security policies.
Details
David Alonso
Released: March 18, 2026
Tables
Keywords
Operators