Azure Data Explorer - Frontdoor Monitoring Data

frontdoor-create-table.kql

.create table FrontDoorDiagnosticLogs (  
  Timestamp: datetime, 
  Category: string, 
  ResourceId: string,  
  OperationName: string,  
  TrackingReference: string, 
  HttpMethod: string, 
  HttpVersion: string, 
  RequestUri: string, 
  RequestBytes: long, 
  ResponseBytes: long,
  UserAgent: string, 
  ClientIP: string, 
  ClientPort: int,
  SocketIP: string, 
  TimeToFirstBye: decimal, 
  TimeTaken: decimal, 
  RequestProtocol: string, 
  SecurityProtocol: string, 
  RulesEngineMatchNames: dynamic, 
  HttpStatusCode: int, 
  HttpStatusDetails: string, 
  PointOfPresence: string, 
  CacheStatus: string, 
  ErrorInfo: string, 
  Endpoint: string, 
  RoutingRuleName: string, 
  HostName: string, 
  OriginUrl: string, 
  OriginIP: string, 
  OriginName: string, 
  Referer: string, 
  ClientCountry: string, 
  Domain: string, 
  SecurityCipher: string, 
  SecurityCurves: string, 
  Policy: string, 
  PolicyRuleName: string, 
  PolicyAction:string, 
  PolicyMode:string, 
  PolicyDetails:dynamic
); 
.alter table FrontDoorDiagnosticLogs policy update @'[{"Source": "DiagnosticRawRecords", "Query": "FrontDoorDiagnosticLogsExpand()", "IsEnabled": "True", "IsTransactional": true}]'; 

frontdoor-transformation-function.kql

.create-or-alter function FrontDoorDiagnosticLogsExpand() {  
  DiagnosticRawRecords  
  | mv-expand events = Records  
  | where isnotempty(events.operationName) and events.category in~ ("FrontDoorWebApplicationFirewallLog", "Microsoft.Cdn/Profiles/AccessLog/Write", "Microsoft.Cdn/Profiles/AccessLog/Read") 
  | limit 10  
  | project  
    Timestamp = todatetime(events['time']),  
    Category = tostring(events.category),  
    ResourceId = tostring(events.resourceId),  
    OperationName = tostring(events.operationName),  
    TrackingReference = tostring(events.properties.trackingReference),  
    HttpMethod = tostring(events.properties.httpMethod),  
    HttpVersion = tostring(events.properties.httpVersion),  
    RequestUri = tostring(events.properties.requestUri),  
    RequestBytes = tolong(events.properties.requestBytes),  
    ResponseBytes = tolong(events.properties.responseBytes),  
    UserAgent = tostring(events.properties.userAgent),  
    ClientIP = iff(isnotnull(events.properties.clientIp), tostring(events.properties.clientIp), tostring(events.properties.clientIP)), 
    ClientPort = toint(events.properties.clientPort),  
    SocketIP = iff(isnotnull(events.properties.socketIp), tostring(events.properties.socketIp), tostring(events.properties.socketIP)), 
    TimeToFirstBye = todecimal(events.properties.timeToFirstByte),  
    TimeTaken = todecimal(events.properties.timeTaken),  
    RequestProtocol = tostring(events.properties.requestProtocol),  
    SecurityProtocol = tostring(events.properties.securityProtocol),  
    RulesEngineMatchNames = events.properties.rulesEngineMatchNames,  
    HttpStatusCode = toint(events.properties.httpStatusCode),  
    HttpStatusDetails = tostring(events.properties.httpStatusDetails),  
    PointOfPresence = tostring(events.properties.pop),  
    CacheStatus = tostring(events.properties.cacheStatus),  
    ErrorInfo = tostring(events.properties.ErrorInfo),  
    Endpoint = tostring(events.properties.endpoint),  
    RoutingRuleName = tostring(events.properties.routingRuleName),  
    HostName = iff(isnotnull(events.properties.host), tostring(events.properties.host) ,tostring(events.properties.hostName)),  
    OriginUrl = tostring(events.properties.originUrl),  
    OriginIP = tostring(events.properties.originIp),  
    OriginName = tostring(events.properties.originName),  
    Referer = tostring(events.properties.referer),  
    ClientCountry = tostring(events.properties.clientCountry),  
    Domain = tostring(events.properties.domain),  
    SecurityCipher = tostring(events.properties.securityCipher),  
    SecurityCurves = tostring(events.properties.securityCurves), 
    Policy = tostring(events.proeprties.policy), 
    PolicyRuleName = tostring(events.properties.ruleName), 
    PolicyAction = tostring(events.properties.action), 
    PolicyMode = tostring(events.properties.policyMode), 
    PolicyDetails = events.properties.details 
}