Skip to content

Instantly share code, notes, and snippets.

Created November 7, 2023 09:27
Show Gist options
  • Save kdemanuele/e20008d2a066fe5eb2ba0fbd7c284ac1 to your computer and use it in GitHub Desktop.
Save kdemanuele/e20008d2a066fe5eb2ba0fbd7c284ac1 to your computer and use it in GitHub Desktop.
Azure Data Explorer - Frontdoor Monitoring Data
.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,
.alter table FrontDoorDiagnosticLogs policy update @'[{"Source": "DiagnosticRawRecords", "Query": "FrontDoorDiagnosticLogsExpand()", "IsEnabled": "True", "IsTransactional": true}]';
.create-or-alter function FrontDoorDiagnosticLogsExpand() {
| 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(,
HttpMethod = tostring(,
HttpVersion = tostring(,
RequestUri = tostring(,
RequestBytes = tolong(,
ResponseBytes = tolong(,
UserAgent = tostring(,
ClientIP = iff(isnotnull(, tostring(, tostring(,
ClientPort = toint(,
SocketIP = iff(isnotnull(, tostring(, tostring(,
TimeToFirstBye = todecimal(,
TimeTaken = todecimal(,
RequestProtocol = tostring(,
SecurityProtocol = tostring(,
RulesEngineMatchNames =,
HttpStatusCode = toint(,
HttpStatusDetails = tostring(,
PointOfPresence = tostring(,
CacheStatus = tostring(,
ErrorInfo = tostring(,
Endpoint = tostring(,
RoutingRuleName = tostring(,
HostName = iff(isnotnull(, tostring( ,tostring(,
OriginUrl = tostring(,
OriginIP = tostring(,
OriginName = tostring(,
Referer = tostring(,
ClientCountry = tostring(,
Domain = tostring(,
SecurityCipher = tostring(,
SecurityCurves = tostring(,
Policy = tostring(events.proeprties.policy),
PolicyRuleName = tostring(,
PolicyAction = tostring(,
PolicyMode = tostring(,
PolicyDetails =
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment