Unified Microsoft Graph Logs

Query

id: 1c20cf22-66db-4b7a-93c7-6fff8526e660
Function:
  Title: 'Function that normalizes the schema of GraphApiAuditEvents to match that of MicrosoftGraphActivityLogs. Optional, only the columns that are common to both tables are returned. In environments with availability of MicrosoftGraphActivityLogs, create a function with the same name which simply returns all entries of the table.'
  Version: '1.0.0'
  LastUpdated: '2025-07-30'
Category: Microsoft Defender XDR Function
FunctionName: UnifiedMicrosoftGraphLogs
FunctionAlias: UnifiedMicrosoftGraphLogs
FunctionQuery: |    
let UnifiedMicrosoftGraphLogs = (CallerObjectId:string="", CallerIpAddress:string="", GraphRequestId:string="", UniqueTokenId:string="") {
    GraphAPIAuditEvents
    | where (CallerObjectId == "" or AccountObjectId == CallerObjectId)
    | where (CallerIpAddress == "" or IpAddress == CallerIpAddress)
    | where (GraphRequestId == "" or ClientRequestId == GraphRequestId)
    | where (UniqueTokenId == "" or UniqueTokenIdentifier == UniqueTokenId)    
    | extend UserId = iff(EntityType == "user",AccountObjectId,"")
    | extend ServicePrincipalId = iff(EntityType == "app",AccountObjectId,"")
    | extend ResponseStatusCode = toint(ResponseStatusCode)
    | extend RequestDuration = toint(RequestDuration)
    | extend SignInActivityId = tostring(UniqueTokenIdentifier)
    | extend RequestId = OperationId    
    | project-rename AppId = ApplicationId, DurationMs = RequestDuration, IPAddress = IpAddress
    // Remove columns which exists in GraphAPIAuditEvents but does not exists MicrosoftGraphActivityLogs
    //| project-away Timestamp, AccountObjectId, EntityType, ReportId, UniqueTokenIdentifier, IdentityProvider
    // Sort columns in similar order than MicrosoftGraphActivityLogs
    | project-reorder 
        TimeGenerated, 
        Location, 
        RequestId, 
        OperationId, 
        ClientRequestId, 
        ApiVersion, 
        RequestMethod, 
        ResponseStatusCode, 
        IPAddress, 
        RequestUri, 
        DurationMs, 
        SignInActivityId, 
        AppId, 
        UserId, 
        ServicePrincipalId, 
        Scopes, 
        Type
};
UnifiedMicrosoftGraphLogs(CallerObjectId,CallerIpAddress,GraphRequestId,UniqueTokenId)

Explanation

This KQL query defines a function called UnifiedMicrosoftGraphLogs that processes data from the GraphAPIAuditEvents table to make it compatible with the MicrosoftGraphActivityLogs table. Here's a simple breakdown of what the function does:

Filtering: It filters the GraphAPIAuditEvents based on optional parameters such as CallerObjectId, CallerIpAddress, GraphRequestId, and UniqueTokenId. If these parameters are not provided, it doesn't filter by them.
Data Transformation:
- It adds new columns like UserId and ServicePrincipalId based on the EntityType.
- It converts ResponseStatusCode and RequestDuration to integers.
- It renames some columns to match the schema of MicrosoftGraphActivityLogs.
Column Management:
- It comments out the removal of columns that exist in GraphAPIAuditEvents but not in MicrosoftGraphActivityLogs.
- It reorders the columns to match the order in MicrosoftGraphActivityLogs.
Execution: The function is called with the provided parameters to return the processed data.

In essence, this function is designed to normalize and transform the GraphAPIAuditEvents data so that it aligns with the structure of MicrosoftGraphActivityLogs, making it easier to work with both datasets in a unified manner.

Details

Thomas Naunheim

Released: July 30, 2025

Tables

GraphAPIAuditEvents

Keywords

GraphAPIAuditEventsMicrosoftGraphActivityLogsUserAppApplicationIdIpAddressOperationIdRequestMethodResponseStatusCodeRequestUriScopesType

Operators

let|whereor==extendifftointtostringproject-renameproject-reorder

Actions

GitHub

KQL Search