KQL Search

Query Details

HomeAI ToolsDevice Query

HUNT 05 M365 Share Point External Sharing Audit 30d

Query

// Hunt    : M365 - SharePoint / OneDrive External Sharing Full Audit (30d)
// Purpose : Enumerate all external sharing events from SharePoint and OneDrive
//           over 30 days, with domain classification, file types, and anonymous
//           link identification. Supports exfiltration path reconstruction.
// Tables  : OfficeActivity
// Period  : P30D
//==========================================================================================

let LookbackDays = 30d;

OfficeActivity
| where TimeGenerated > ago(LookbackDays)
| where RecordType in ("SharePoint", "OneDrive", "SharePointSharingOperation", "SharePointFileOperation")
| where Operation in (
    "SharingInvitationCreated", "AnonymousLinkCreated",
    "SharingSet", "AddedToSecureLink",
    "SecureLinkUsed", "AnonymousLinkUsed",
    "SharingInvitationAccepted")
| extend
    TargetUser    = tostring(TargetUserOrGroupName),
    TargetDomain  = tostring(split(TargetUserOrGroupName, "@")[1]),
    FileExt       = tostring(extract(@"(\.[a-zA-Z0-9]+)$", 1, SourceFileName)),
    SharingType   = tostring(parse_json(Event_Data).SharingType),
    LinkScope     = tostring(parse_json(Event_Data).LinkScope)
| extend
    IsAnonymous   = (Operation has "Anonymous" or LinkScope == "Anyone"),
    IsExternal    = isnotempty(TargetDomain)
        and TargetDomain !has "onmicrosoft.com"
        and TargetUser has "@"
| summarize
    TotalShares      = count(),
    AnonymousShares  = countif(IsAnonymous),
    ExternalShares   = countif(IsExternal),
    UniqueRecipients = dcount(TargetUser),
    FileTypes        = make_set(FileExt, 10),
    SampleFiles      = make_set(SourceFileName, 10),
    SiteURLs         = make_set(Site_Url, 5),
    RecipientDomains = make_set(TargetDomain, 10)
    by UserId
| sort by AnonymousShares desc
| project
    UserId,
    TotalShares,
    AnonymousShares,
    ExternalShares,
    UniqueRecipients,
    RecipientDomains,
    FileTypes,
    SampleFiles,
    SiteURLs

Explanation

This query is designed to analyze external sharing activities in SharePoint and OneDrive over the past 30 days. Here's a simplified breakdown of what it does:

  1. Data Source: It examines records from the OfficeActivity table, focusing on events related to SharePoint and OneDrive.

  2. Time Frame: It looks at activities that occurred in the last 30 days.

  3. Event Types: The query filters for specific sharing operations, such as creating sharing invitations, anonymous links, and secure links, as well as when these links are used or accepted.

  4. Data Extraction:

    • It extracts the target user's email and domain.
    • It identifies the file extension of shared files.
    • It determines the type of sharing and the scope of links (e.g., anonymous).

  5. Classification:

    • It checks if a share is anonymous or external (i.e., shared with users outside the organization).

  6. Aggregation:

    • It counts the total number of shares, anonymous shares, and external shares.
    • It counts unique recipients and lists up to 10 different file types and sample file names.
    • It lists up to 5 different site URLs and 10 recipient domains.

  7. Sorting and Output:

    • The results are sorted by the number of anonymous shares in descending order.
    • The final output includes user IDs along with their sharing statistics and details.

Overall, this query helps identify and analyze patterns in external sharing activities, which can be useful for security audits and understanding data exfiltration risks.

Details

David Alonso profile picture

David Alonso

Released: March 18, 2026

Tables

OfficeActivity

Keywords

SharePointOneDriveExternalSharingDomainFileTypesAnonymousLinkExfiltrationPathReconstructionOfficeActivityTimeGeneratedRecordTypeOperationTargetUserTargetDomainFileExtSharingTypeLinkScopeIsAnonymousIsExternalTotalSharesAnonymousSharesExternalSharesUniqueRecipientsFileTypesSampleFilesSiteURLsRecipientDomainsUserId

Operators

letagoinextendtostringsplitextractparse_jsonhasisnotemptyandsummarizecountcountifdcountmake_setbysortdescproject

Actions