02 Observed Permission Usage

Query

MicrosoftGraphActivityLogs
| where TimeGenerated > ago(LookbackWindow)
| where isnotempty(ServicePrincipalId)
| extend RequestPath = tostring(parse_url(RequestUri).Path)
| extend ResourceApp = "Microsoft Graph"
| extend PermissionUsed = case(
    RequestPath matches regex @"(?i)/sites/[^/]+/lists/[^/]+/items", iff(RequestMethod in ("PATCH", "POST", "PUT", "DELETE"), "Sites.ReadWrite.All", "Sites.Read.All"),
    RequestPath matches regex @"(?i)/sites/[^/]+/drive",              iff(RequestMethod in ("PATCH", "POST", "PUT", "DELETE"), "Files.ReadWrite.All", "Files.Read.All"),
    RequestPath matches regex @"(?i)/sites/[^/]+$",                    iff(RequestMethod in ("PATCH", "POST", "PUT", "DELETE"), "Sites.ReadWrite.All", "Sites.Read.All"),
    RequestPath matches regex @"(?i)/me/drive|/users/[^/]+/drive",     iff(RequestMethod in ("PATCH", "POST", "PUT", "DELETE"), "Files.ReadWrite.All", "Files.Read.All"),
    RequestPath matches regex @"(?i)/messages|/mailFolders",           "Mail.Read",
    "Unmapped" // PLACEHOLDER -- extend this case() list with every Graph path shape your agents actually call; an "Unmapped" result must never be silently treated as evidence of use.
)
| extend ResolvedResourceId = extract(@"(?i)/sites/([^/]+)", 1, RequestPath) // PLACEHOLDER -- replace with a join to your GUID/hostname -> canonical-resource-id reference table.
| extend ResultType = case(
    ResponseStatusCode between (200 .. 299), "success",
    ResponseStatusCode in (401, 403),        "denied",
    ResponseStatusCode == 429,               "throttled",
    "error"
)
| project
    TimeGenerated,
    ServicePrincipalId,
    ResourceApp,
    PermissionUsed,
    PermissionClaims = Scopes,
    ApplicationClaims = Roles,
    RequestUri,
    RequestMethod,
    ResponseStatusCode,
    ResultType,
    ResolvedResourceId,
    UniqueTokenId
| where PermissionUsed != "Unmapped"
| order by TimeGenerated desc

About this query

Explanation

This query is designed to analyze Microsoft Graph API calls to determine the actual permissions used during those calls, as opposed to the permissions that were merely included in the access token. Here's a simplified breakdown of what the query does:

Time Frame: It looks at logs from the past 90 days.
Filter Logs: It filters the logs to include only those with a non-empty ServicePrincipalId.
Extract Information: It extracts the path from the request URI and identifies the resource application as "Microsoft Graph".
Determine Permissions Used: It classifies each API call based on the HTTP method and URL pattern to determine the specific permission required for that call. This is done using a series of conditional checks:
- For certain URL patterns related to SharePoint sites, drives, and mail, it assigns permissions like Sites.ReadWrite.All, Files.ReadWrite.All, or Mail.Read.
- If the URL pattern doesn't match any predefined cases, it labels the permission as "Unmapped".
Resource Identification: It attempts to extract a resource identifier from the URL, but notes that this identifier needs to be mapped to a canonical resource ID using an external reference table.
Result Classification: It classifies the result of each API call based on the HTTP response status code:
- Success for 2xx codes
- Denied for 401 or 403 codes
- Throttled for 429 codes
- Error for any other codes
Project Relevant Data: It selects and orders relevant columns for output, such as the time of the request, service principal ID, resource app, permissions used and claimed, request URI and method, response status, result type, and resolved resource ID.
Filter Unmapped Permissions: It excludes any entries where the permission used is labeled as "Unmapped".

The overall goal is to provide a clear view of which permissions were actually used in successful API calls, helping to differentiate between permissions that were merely included in a token and those that were actively utilized.