Combine KQL Queries into a single query

September 14, 2022

I have two separate KQL queries I’m using on Azure Log analytics to find blocks on an Azure WAF, the first one finds the transactionid or requests that were blocked.

//Query 1
AzureDiagnostics
| where ResourceType == "APPLICATIONGATEWAYS" and OperationName == "ApplicationGatewayFirewall"
| where hostname_s in ('www.website.com')
| where Message !startswith "Mandatory"
| where action_s in ('Blocked', 'Detected')
| project transactionId_g

To find all the parts of the request that combine to generate the block I then copy/paste the transactionid’s to a 2nd query and that all requests that are part of that transaction which gives me the data I need to create exceptions on the WAF if it’s a false positive.

//Query 2
AzureDiagnostics
| where ResourceType == "APPLICATIONGATEWAYS" and OperationName == "ApplicationGatewayFirewall"
| where Message !startswith "Mandatory"
| where transactionId_g == "8eb316fd-4a5b-66c7-0136-fc67e21d282b"
| project transactionId_g, TimeGenerated, clientIp_s, hostname_s, requestUri_s, ruleId_s, ruleSetVersion_s, ruleGroup_s, action_s, Message, details_message_s, details_data_s
| sort by transactionId_g

Is there a way to combine these two queries together into a single query that finds the transactionid’s of blocked requests, then shows ALL log lines containing those transactionid’s?

>Solution :

if there’s a single transaction ID returned by the 1st query, you could use the toscalar() function:

let tid = toscalar(
    AzureDiagnostics
    | where ResourceType == "APPLICATIONGATEWAYS" and OperationName == 
    "ApplicationGatewayFirewall"
    | where hostname_s in ('www.website.com')
    | where action_s in ('Blocked', 'Detected')
    | where Message !startswith "Mandatory"
    | project transactionId_g
    | take 1
);
AzureDiagnostics
| where transactionId_g == tid // <-- this is where you use the result of the 1st subquery
| where ResourceType == "APPLICATIONGATEWAYS" and OperationName == "ApplicationGatewayFirewall"
| where Message !startswith "Mandatory"
| project transactionId_g, TimeGenerated, clientIp_s, hostname_s, requestUri_s, ruleId_s, ruleSetVersion_s, ruleGroup_s, action_s, Message, details_message_s, details_data_s
| sort by transactionId_g

or, if the first query returns a set of transaction IDs and not a single one, you could use the in operator:

let tids = 
    AzureDiagnostics
    | where ResourceType == "APPLICATIONGATEWAYS" and OperationName == 
    "ApplicationGatewayFirewall"
    | where hostname_s in ('www.website.com')
    | where action_s in ('Blocked', 'Detected')
    | where Message !startswith "Mandatory"
    | project transactionId_g
;
AzureDiagnostics
| where transactionId_g in (tids) // <-- this is where you use the result of the 1st subquery
| where ResourceType == "APPLICATIONGATEWAYS" and OperationName == "ApplicationGatewayFirewall"
| where Message !startswith "Mandatory"
| project transactionId_g, TimeGenerated, clientIp_s, hostname_s, requestUri_s, ruleId_s, ruleSetVersion_s, ruleGroup_s, action_s, Message, details_message_s, details_data_s
| sort by transactionId_g