KQL Search

Query Details

HomeAI ToolsDevice Query

VIP Mailbox Permission Change Detection

Query

//This query monitors mailbox permission changes for VIP users
//Correlates Office activity with sign-in logs and VIP watchlist
let OfficeActivityData = OfficeActivity
    | where Operation == "Add-MailboxPermission"
    | extend UserValue = tostring(parse_json(Parameters)[1].Value)
    | project TimeGenerated, UserId, OfficeObjectId, UserValue;
let SigninLogsData = SigninLogs
    | where isnotempty(UserPrincipalName)
    | project UserId, UserPrincipalName;
let VIPWatchlist = _GetWatchlist('VIPUsersv2')
    | project UserPrincipal = SearchKey;
let ActivityWithSigninData = OfficeActivityData
    | join kind=leftouter (SigninLogsData) on $left.UserValue == $right.UserId
    | extend FinalUserValue = iff(isnotempty(UserPrincipalName), UserPrincipalName, UserValue)
    | project TimeGenerated, UserId, OfficeObjectId, FinalUserValue
    | where not(FinalUserValue matches regex @"^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}$");
ActivityWithSigninData
| join kind=inner (VIPWatchlist) on $left.FinalUserValue == $right.UserPrincipal
| summarize Count = count() by TimeGenerated, UserId, OfficeObjectId, FinalUserValue
| project-rename Initiator = UserId, AddedUser = OfficeObjectId, ToUser = FinalUserValue

Explanation

This query is designed to track changes in mailbox permissions specifically for VIP users. Here's a simplified breakdown of what it does:

  1. Collect Office Activity Data: It starts by filtering Office activity logs to find events where mailbox permissions are added. It extracts relevant details like the time of the event, the user who initiated it, the mailbox affected, and the user to whom permissions were granted.

  2. Gather Sign-in Logs: It then collects sign-in logs, focusing on entries where the user's principal name is available.

  3. Access VIP Watchlist: The query retrieves a list of VIP users from a predefined watchlist.

  4. Correlate Data: It combines the Office activity data with sign-in logs to match user identifiers. If a user principal name is found, it uses that; otherwise, it defaults to the original user value.

  5. Filter Non-GUID Entries: It filters out entries where the user identifier looks like a GUID (a unique identifier format), focusing on more meaningful user identifiers.

  6. Identify VIP Activity: The query then checks if any of the mailbox permission changes involve users from the VIP watchlist.

  7. Summarize Results: Finally, it summarizes the data by counting how many permission changes occurred, grouped by the time of the event, the initiator of the change, the mailbox affected, and the VIP user involved. It renames these fields for clarity.

In essence, this query helps monitor and report on any changes to mailbox permissions that involve VIP users, providing insights into who initiated the changes and which mailboxes were affected.

Details

Dimitris Michos profile picture

Dimitris Michos

Released: November 10, 2024

Tables

OfficeActivitySigninLogs_GetWatchlist

Keywords

OfficeActivitySigninLogsVIPWatchlistUserPrincipalNameUserIdOfficeObjectIdTimeGeneratedParametersSearchKey

Operators

let|where==extendtostring()parse_json()projectisnotempty()_GetWatchlist()joinkind=leftouteron$left.$right.iff()matches regexkind=innersummarizecount()byproject-rename

Actions