Azure Data Explorer - API Management Monitoring Data

api-management-create-table.kql

.create table APIManagementDiagnosticLogs ( 
  Timestamp: datetime, 
  Category: string, 
  ResourceId: string, 
  OperationName: string, 
  Method: string, 
  Url: string, 
  BackendResponseCode: int, 
  ResponseCode: int, 
  ResponseSize: int, 
  Cache: string, 
  BackendTime: long, 
  RequestSize: long, 
  ApiId: string, 
  OperationId: string, 
  ClientProtocol: string, 
  BackendProtocol: string, 
  BackendId: string, 
  ApiRevision: string, 
  ClientTlsVersion: string, 
  BackendMethod: string, 
  BackendUrl: string, 
  DeploymentVersion: string, 
  Level: int, 
  IsRequestSuccess: bool, 
  DurationMs: long, 
  CallerIpAddress: string, 
  CorrelationId: string, 
  Location: string, 
  Result: string, 
  Truncated: long 
); 
.alter table APIManagementDiagnosticLogs policy update @'[{"Source": "DiagnosticRawRecords", "Query": "APIMDiagnosticLogsExpand()", "IsEnabled": "True", "IsTransactional": true}]';

api-management-transformation-function.kql

.create-or-alter function APIMDiagnosticLogsExpand() { 
  DiagnosticRawRecords 
  | mv-expand events = Records 
  | where isnotempty(events.operationName) and events.operationName startswith "Microsoft.ApiManagement/GatewayLogs" 
  | project 
    Timestamp = todatetime(events['time']), 
    Category = tostring(events.category), 
    ResourceId = tostring(events.resourceId), 
    OperationName = tostring(events.operationName), 
    Method = tostring(events.properties.method), 
    Url = tostring(events.properties.url), 
    BackendResponseCode = toint(events.properties.backendResponseCode), 
    ResponseCode = toint(events.properties.responseCode), 
    ResponseSize = toint(events.proeprties.responseSize), 
    Cache = tostring(events.properties.cache), 
    BackendTime = tolong(events.properties.backendTime), 
    RequestSize = tolong(events.properties.requestSize), 
    ApiId = tostring(events.properties.apiId), 
    OperationId = tostring(events.properties.operationId), 
    ClientProtocol = tostring(events.properties.clientProtocol), 
    BackendProtocol = tostring(events.properties.backendProtocol), 
    BackendId = tostring(events.properties.backendId), 
    ApiRevision = tostring(events.properties.apiRevision), 
    ClientTlsVersion = tostring(events.properties.clientTlsVersion), 
    BackendMethod = tostring(events.properties.backendMethod), 
    BackendUrl = tostring(events.properties.backendUrl), 
    DeploymentVersion = tostring(events.DeploymentVersion), 
    Level = toint(events.Level), 
    IsRequestSuccess = tobool(events.isRequestSuccess), 
    DurationMs = tolong(events.durationMs), 
    CallerIpAddress = tostring(events.callerIpAddress), 
    CorrelationId = tostring(events.correlationId), 
    Location = tostring(events.location), 
    Result = tostring(events.resultType), 
    Truncated = tolong(events.truncated) 
}