End-user consent to app with mailbox and offline access delegated scopes

Original query, unchanged.

let detectionWindow = 1d;
AuditLogs
| where TimeGenerated > ago(detectionWindow)
| where LoggedByService =~ "Core Directory"
| where Category =~ "ApplicationManagement"
| where OperationName =~ "Consent to application"
| where TargetResources has "offline_access"
| mv-apply TargetResource = TargetResources on
  (
      where TargetResource.type =~ "ServicePrincipal"
      | extend AppDisplayName = tostring(TargetResource.displayName),
               ServicePrincipalId = tolower(tostring(TargetResource.id)),
               ModifiedProperties = TargetResource.modifiedProperties
  )
| mv-apply Property = ModifiedProperties on
  (
      where Property.displayName =~ "ConsentAction.Permissions"
      | extend ConsentFull = trim(@'"', tostring(Property.newValue))
  )
| where ConsentFull has "offline_access"
| where ConsentFull has_any ("Mail.Read", "Mail.ReadWrite", "Mail.Send", "MailboxSettings.ReadWrite")
| parse ConsentFull with * "ConsentType: " GrantConsentType ", Scope: " GrantScope "]" *
// Exact scope membership. has/has_any above is a cheap term-indexed pre-filter, but
// term matching tokenizes "Mail.Read" to mail/read and would over-match scopes like
// Mail.ReadBasic. Split the parsed scope string and require the exact scope names.
| extend GrantScopes = split(GrantScope, " ")
| where set_has_element(GrantScopes, "offline_access")
| where set_has_element(GrantScopes, "Mail.Read")
    or set_has_element(GrantScopes, "Mail.ReadWrite")
    or set_has_element(GrantScopes, "Mail.Send")
    or set_has_element(GrantScopes, "MailboxSettings.ReadWrite")
// End-user (delegated) consent only. Tenant-wide admin consent for all users
// surfaces as ConsentType "AllPrincipals" and is scoped out here on purpose.
| where GrantConsentType != "AllPrincipals"
| extend GrantInitiatedByUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
| extend GrantInitiatedByAadUserId = tostring(InitiatedBy.user.id)
| extend GrantIpAddress = iff(isnotempty(tostring(InitiatedBy.user.ipAddress)), tostring(InitiatedBy.user.ipAddress), tostring(InitiatedBy.app.ipAddress))
// AuditLogs does not guarantee the order of AdditionalDetails, so AdditionalDetails[0] silently
// misses the user agent whenever another key comes first. User agent is enrichment here and must
// never drop a consent event, so the array is padded before expanding it and the key is looked
// up by name in a property bag.
| extend AdditionalDetailPairs = iff(array_length(AdditionalDetails) > 0, AdditionalDetails, pack_array(bag_pack("key", "None", "value", "")))
| mv-apply AdditionalDetail = AdditionalDetailPairs on (summarize AdditionalDetailsBag = make_bag(bag_pack(tolower(tostring(AdditionalDetail.key)), tostring(AdditionalDetail.value))))
| extend GrantUserAgent = tostring(AdditionalDetailsBag["user-agent"])
| extend Name = tostring(split(GrantInitiatedByUserPrincipalName, '@')[0]), UPNSuffix = tostring(split(GrantInitiatedByUserPrincipalName, '@')[1])
| project TimeGenerated, GrantConsentType, GrantScope, GrantInitiatedByUserPrincipalName, GrantInitiatedByAadUserId, AppDisplayName, ServicePrincipalId, GrantIpAddress, GrantUserAgent, OperationName, ConsentFull, CorrelationId, Name, UPNSuffix