KQL Search

Query Details

APIM AI Gateway - Failed request to exception correlation

APIM Request Exception Correlation

Query

let Requests=AppRequests
| where TimeGenerated > ago(24h)
| where SDKVersion startswith "apim:" or tostring(Properties["Service Type"]) =~ "API Management"
| where Success == false or toint(ResultCode) >= 400
| extend Caller=coalesce(UserAuthenticatedId, UserId, tostring(Properties["Subscription ID"]), tostring(Properties["SubscriptionId"]), tostring(Properties["subscriptionId"]), ClientIP, "unknown"), APIName=coalesce(tostring(Properties["API Name"]), Name), RequestId=tostring(Properties["Request Id"])
| project RequestTime=TimeGenerated, OperationId, RequestId, Caller, ClientIP, APIName, RequestOperation=Name, RequestUrl=Url, RequestResult=ResultCode, RequestDurationMs=DurationMs;
let Exceptions=AppExceptions
| where TimeGenerated > ago(24h)
| extend ExceptionType=tostring(column_ifexists('Type', column_ifexists('type', ''))), ExceptionMessage=coalesce(tostring(column_ifexists('OuterMessage', column_ifexists('outerMessage', ''))), tostring(column_ifexists('InnermostMessage', column_ifexists('innermostMessage', ''))), tostring(column_ifexists('Message', column_ifexists('message', '')))), ExceptionMethod=tostring(column_ifexists('Method', column_ifexists('method', '')))
| project ExceptionTime=TimeGenerated, OperationId, ExceptionType, ExceptionMessage, ExceptionMethod, ProblemId;
Requests
| join kind=inner Exceptions on OperationId
| project RequestTime, ExceptionTime, OperationId, RequestId, Caller, ClientIP, APIName, RequestOperation, RequestUrl, RequestResult, RequestDurationMs, ExceptionType, ExceptionMessage, ExceptionMethod, ProblemId
| order by RequestTime desc, ExceptionTime asc
| take 250

Explanation

This KQL query is designed to help investigate failed requests in an API Management (APIM) environment by correlating them with exceptions logged in Application Insights. Here's a simplified breakdown:

  1. Data Sources: The query uses two data sources:

    • AppRequests: Logs of API requests.
    • AppExceptions: Logs of exceptions.

  2. Time Frame: It looks at data from the past 24 hours.

  3. Filtering Requests:

    • It selects requests that either failed (Success == false) or had a result code of 400 or higher (indicating client or server errors).
    • It identifies requests related to API Management by checking if the SDK version starts with "apim:" or if the service type is "API Management".

  4. Request Details: For each request, it extracts details such as:

    • Time of the request
    • Operation ID
    • Caller information (like user ID or client IP)
    • API name and operation
    • URL, result code, and duration of the request

  5. Filtering Exceptions:

    • It extracts exceptions that occurred in the same 24-hour period.
    • It gathers details like exception type, message, method, and problem ID.

  6. Correlation:

    • The query joins the requests and exceptions on the OperationId, which is a unique identifier linking requests to exceptions.
    • It combines the details from both logs to provide a comprehensive view of each failed request and its related exception.

  7. Output:

    • The results are ordered by the request time (most recent first) and exception time (oldest first).
    • It limits the output to the top 250 entries.

  8. Purpose: This query is used for investigating failures in API requests by understanding the exceptions that might have caused them. It is not intended to identify security incidents but rather operational issues.

  9. Tags and Techniques: The query is tagged for use with Sentinel, and it aligns with the MITRE ATT&CK tactic of "Impact" and technique T1499, which relates to service disruption.