RULE 18 M365 Guest Off Hours File Access

Query

// Rule    : M365 - SharePoint / OneDrive File Access by Guest During Off-Hours
// Severity: Medium
// Tactics : Collection, InitialAccess
// MITRE   : T1530 (Data from Cloud Storage), T1078.004
// Freq    : PT1H   Period: PT1H
// Description: Detects guest users (#EXT#) accessing SharePoint or OneDrive files
//              outside of typical business hours (22:00–06:00 UTC), which may
//              indicate a compromised guest account or unauthorised after-hours access.
//==========================================================================================

let LookbackPeriod         = 1h;
let OffHoursStart          = 22;    // 22:00 UTC
let OffHoursEnd            = 6;     // 06:00 UTC
let AccessThreshold        = 20;

OfficeActivity
| where TimeGenerated > ago(LookbackPeriod)
| where RecordType in ("SharePoint", "OneDrive")
| where UserId has "#EXT#"          // guest/external account
| where Operation in (
    "FileAccessed", "FileDownloaded", "FileViewed",
    "FilePreviewed", "FileCheckedOut")
| extend HourUTC = hourofday(TimeGenerated)
| where HourUTC >= OffHoursStart or HourUTC < OffHoursEnd
| summarize
    AccessCount     = count(),
    FilesAccessed   = make_set(SourceFileName, 15),
    SiteURLs        = make_set(SiteUrl, 5),
    ClientIPs       = make_set(ClientIP, 5),
    HoursActive     = make_set(HourUTC, 10)
    by UserId
| where AccessCount >= AccessThreshold
| extend GuestDomain = tostring(extract(@"#EXT#@(.+)", 1, UserId))
| extend AlertSeverity = case(
    AccessCount >= 100, "High",
    AccessCount >= 50,  "Medium",
    "Low")
| project
    TimeGenerated  = now(),
    UserId,
    GuestDomain,
    AccessCount,
    FilesAccessed,
    SiteURLs,
    ClientIPs,
    HoursActive,
    AlertSeverity

Explanation

This query is designed to detect potentially suspicious activity involving guest users accessing files on SharePoint or OneDrive during off-hours, which are defined as 10:00 PM to 6:00 AM UTC. Here's a simple breakdown of what the query does:

Time Frame: It looks at activities from the past hour.
User Type: It focuses on guest users, identified by the presence of "#EXT#" in their user ID.
Activity Type: It checks for specific file-related operations such as accessing, downloading, viewing, previewing, or checking out files.
Off-Hours Access: It filters these activities to only include those occurring during off-hours (10:00 PM to 6:00 AM UTC).
Summarization: For each guest user, it counts the number of file access activities and collects details such as the names of accessed files, site URLs, client IPs, and the specific hours during which the activities occurred.
Threshold: It only considers cases where the number of accesses is 20 or more.
Alert Severity: Based on the number of accesses, it assigns a severity level to the alert (High, Medium, or Low).
Output: The final output includes the current time, user ID, guest domain, access count, list of accessed files, site URLs, client IPs, hours active, and alert severity.

This query helps identify potential unauthorized access by guest users during times when such activity is less expected, which could indicate a compromised account or unauthorized access attempt.