In this post I am sharing with you my most common Log Analytics queries (KQL) I use in the daily business for troubleshooting traffic to the Application Gateway’s secured by Web Application Firewall (WAF) rules. This article is more a wiki than a blog post and will be always updated if new things come up.

Before you can run the queries please ensure, that you have enabled the Diagnostic Logs on the Application Gateway to be stored in the Log Analytics Workspace where you want to run the queries. To do this check out the following article in the Microsoft Documentation.

Queries for the Application Gateway Firewall Log

Use-Case: List all Application Gateway Firewall logs without any filter. [last updated 26.03.2021]

AzureDiagnostics
| where Category == "ApplicationGatewayFirewallLog"
| sort by TimeGenerated

Use-Case: List log entries for a dedicated hostname. I exclude entries which refer to rules you cannot disable to reduce the results which do not help. [last updated 26.03.2021]

AzureDiagnostics
| where Category == "ApplicationGatewayFirewallLog"
| where Message !contains "Mandatory rule. Cannot be disabled."
| where hostname_s == "your-hostname.com"
| sort by TimeGenerated

Use-Case: List log entries for a dedicated hostname summarized by the OWASP Rule ID. I exclude entries which refer to rules you cannot disable to reduce the results which do not help. [last updated 26.03.2021]

AzureDiagnostics
| where Category == "ApplicationGatewayFirewallLog"
| where Message !contains "Mandatory rule. Cannot be disabled."
| where hostname_s == "your-hostname.com"
| summarize by ruleId_s

Queries for the Application Gateway Access Log

Use-Case: List all Application Gateway Access logs without any filter. [last updated 26.03.2021]

AzureDiagnostics
| where Category == "ApplicationGatewayAccessLog"
| sort by TimeGenerated

Use-Case: Create an access view for all hosts based on 30 minutes intervals. [last updated 26.03.2021]

AzureDiagnostics
| where Category == "ApplicationGatewayAccessLog"
//| where host_s == "your-hostname.com"
| summarize count() by host_s, bin(TimeGenerated, 30m)
| render timechart

Use-Case: Create an access view for a dedicated API or URI of a host based on 30 minutes intervals. [last updated 26.03.2021]

AzureDiagnostics
| where Category == "ApplicationGatewayAccessLog"
| where requestUri_s == "/your-uri"
| summarize count() by requestUri_s, bin(TimeGenerated, 30m)
| render timechart

Use-Case: Get HTTP status codes for a dedicated host. [last updated 26.03.2021]

AzureDiagnostics
| where Category == "ApplicationGatewayAccessLog"
| where host_s == "your-hostname.com"
| summarize count() by httpStatus_d

Use-Case: Search for a dedicated status code on a dedicated host. [last updated 26.03.2021]

AzureDiagnostics
| where Category == "ApplicationGatewayAccessLog"
| where host_s == "your-hostname.com"
| where httpStatus_d == ""
| sort by TimeGenerated