SOLVED

Syntax Errors on WAFv2 Log analytics

Copper Contributor

trying to execute:

AzureDiagnostics
| where Category == ApplicationGatewayFirewallLog and action_s == Blocked
| summarize count(details_message_s) by details_message_s, bin(TimeGenerated, 5m)
| render barchart
 
to check the  WafV2 Fw logs 
and get a syntax error 
i get the same when i try to do 
 
AzureDiagnostics
| where ResouceProvider == "MICROSOFT.NETWORK" and Category == "ApplicationGatewayFirewalllog"
| where requestUri_s -- "/" and action_s == "Blocked"
| summarize count () by ruleID_s
 
the resource provider and "action_s" are not recognized for some reason...
 
 
 
 
9 Replies

Hi @Leon_K ,

I do not have those logs in a demo environment but executing the first query does not give me any syntax errors just 0 records. Can you give more details?

 

The second query you have posted truly give syntax error but that is because you are using '--' instead of '==' I would assume for filtering on requestUri_s.

@Leon_K 

 

Do you have those logs?

AzureDiagnostics
| where Category startswith "ApplicationGateway" 
| summarize count() by Category 

https://docs.microsoft.com/en-us/azure/application-gateway/application-gateway-diagnostics 

@CliveWatson  no blocked logs as its  test environment, but i at the least expect it to show "0 logs found".

 

 

thats strange, i would expect it to give me the same
but all i get is:
SYNTAX ERROR

Syntax Error

Support id: 4d685e97-a316-4efa-aeab-43110d965800

The only thing i get to go by is that the log analytics highlights the
“ApplicationGatewayFirewallLog” and action_s == “Blocked”
part as bad syntax...
re: the -- vs == that was my typo posting here, but for some reason the error i get for syntax there is on action_s


AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK" and Category == "ApplicationGatewayFirewalllog"
| where requestUri_s == "/" and action_s == "Blocked"
| summarize count () by ruleID_s
SYNTAX ERROR

'where' operator: Failed to resolve column or scalar expression named 'action_s'

Support id: 26c9911a-d269-4b3e-ab0a-db27405f2f8c

@Leon_K 

 

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.NETWORK" //and Category == "ApplicationGatewayFirewalllog"
| where requestUri_s == "/" and action_s != "Blocked"
| summarize count () by action_s, ruleId_s

I don't have that data, nor is it in the demo portal, but ruleId_s and action_s seem to work in this test query

Go to Log Analytics and Run Query 

 

best response confirmed by Stanislav Zhelyazkov (MVP)
Solution

Hi@Leon_K 

Now I get what is the error about. Some columns will not exist if there was no data for them at any point in time. The action_s column will appear in the schema only if at some point such data was ingested. At the time of ingestion that column will be created in the schema. Best way is configure your App GW to send diagnostic logs to Log Analytics and once data is ingested for that log the column would appear and you will not get errors. Of course Kusto is powerful language so you can do other things like if column do not exists put some default value and create it. I have answered that question here:

https://techcommunity.microsoft.com/t5/Azure-Log-Analytics/Getting-Failed-to-resolve-scalar-expressi...

 

I will mark this reply as answer but if you need some guidance on diagnostic logs let me know.

So, it does not exist even as an option before the FW log pushes it? oh wow
Thanks

@Leon_K Yes, certain columns are created only when first data for them arrives. Similar the way it is with custom logs via data ingestion api. Some columns are available out of the box but that is for example such that are common across all diagnostic logs like Category. The current schema for your workspace can be seen in Logs view.

1 best response

Accepted Solutions
best response confirmed by Stanislav Zhelyazkov (MVP)
Solution

Hi@Leon_K 

Now I get what is the error about. Some columns will not exist if there was no data for them at any point in time. The action_s column will appear in the schema only if at some point such data was ingested. At the time of ingestion that column will be created in the schema. Best way is configure your App GW to send diagnostic logs to Log Analytics and once data is ingested for that log the column would appear and you will not get errors. Of course Kusto is powerful language so you can do other things like if column do not exists put some default value and create it. I have answered that question here:

https://techcommunity.microsoft.com/t5/Azure-Log-Analytics/Getting-Failed-to-resolve-scalar-expressi...

 

I will mark this reply as answer but if you need some guidance on diagnostic logs let me know.

View solution in original post