KQL Search

Query Details

New TABL Entry Using Office Activity

Query

let OA = OfficeActivity
| where TimeGenerated > ago(30m)
| where Operation == "New-TenantAllowBlockListItems"
| extend Value = extract(@'{"Name":"Entries","Value":"([^"]+)"', 1, Parameters)
| extend Comments = extract(@'{"Name":"Notes","Value":"([^"]+)"', 1, Parameters)
| extend Expiration = extract(@'{"Name":"NoExpiration","Value":"([^"]+)"', 1, Parameters)
| extend Remove_After = extract(@'{"Name":"RemoveAfter","Value":"([^"]+)"', 1, Parameters)
| extend Block_Type= extract(@'{"Name":"ListType","Value":"([^"]+)"', 1, Parameters)
| extend Is_Blocked= extract(@'{"Name":"Block","Value":"([^"]+)"', 1, Parameters)
| extend Is_Allowed= extract(@'{"Name":"Allow","Value":"([^"]+)"', 1, Parameters)
| extend Submission_User = extract(@'{"Name":"SubmissionUserId","Value":"([^"]+)"', 1, Parameters)
| extend AADSessionId = tostring(AppAccessContext.AADSessionId)
| project OA_Time = TimeGenerated, AADSessionId, Operation, Value, Comments, Expiration, Remove_After, Block_Type, Is_Blocked, Is_Allowed, Submission_User;
//
let SI = union SigninLogs, AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(2d)
| extend AADSessionId = tostring(SessionId)
| summarize arg_max(TimeGenerated, *) by AADSessionId
| project AADSessionId, UserPrincipalName;
OA
| join kind=leftouter SI on AADSessionId
| extend UserPrincipalName = iif(isempty(AADSessionId), "Unknown", UserPrincipalName)
| extend Submission_User = coalesce(Submission_User, UserPrincipalName)
| project OA_Time, Operation, Value, Comments, Expiration, Remove_After, Block_Type, Is_Blocked, Is_Allowed, Submission_User

Explanation

This query is designed to analyze and correlate data from Office activities related to tenant allow/block list operations with user sign-in logs. Here's a simplified breakdown:

  1. Extract Office Activities:

    • The query starts by filtering OfficeActivity logs to include only those generated in the last 30 minutes where the operation is "New-TenantAllowBlockListItems".
    • It extracts specific details from the Parameters field, such as the value being blocked or allowed, comments, expiration details, block type, and the user who submitted the entry.

  2. Prepare Sign-In Logs:

    • It combines data from SigninLogs and AADNonInteractiveUserSignInLogs for the last 2 days.
    • It extracts the Azure Active Directory (AAD) session ID and finds the most recent log entry for each session.

  3. Join and Enhance Data:

    • The query performs a left outer join between the Office activities and sign-in logs based on the AAD session ID.
    • If the session ID is missing, it assigns "Unknown" to the user principal name.
    • It ensures that the Submission_User field is populated, using the user principal name if necessary.

  4. Final Output:

    • The query projects a final set of columns, including the time of the Office activity, operation details, extracted values, and the user who submitted the entry.

In essence, this query helps identify and understand recent tenant allow/block list operations, linking them to user sign-in activities to provide context on who performed these actions.

Details

Jay Kerai profile picture

Jay Kerai

Released: May 13, 2026

Tables

OfficeActivitySigninLogsAADNonInteractiveUserSignInLogs

Keywords

OfficeActivitySigninLogsAADNonInteractiveUserSignInLogsParametersAppAccessContextAADSessionIdSessionIdUserPrincipalName

Operators

let|where>ago==extendextract@projectunionsummarizearg_maxbyjoinkind=leftouteroniifisemptycoalesce

Actions

GitHub