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 OfficeActivity table, 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

OfficeActivity

Keywords

SharePointAdminRolePermissionChangesAuditTrailComplianceAccessReviewPrivilegeEscalationOfficeActivity

Operators

letagoinextendtostringparse_jsontolowerhashas_anydynamicsummarizecountmake_setcountifminmaxbysortdescproject

Actions

GitHub

KQL Search