I need a KQL Query for Azure Sentinel for making an alert rule that triggers when one successful connection from an IP Address observed after 500 failure requests towards the application gateway

Question

I need a KQL Query for Azure Sentinel for making an alert rule that triggers when one successful connection from an IP Address after 500 consecutive failure requests towards the application gateway. So basically we need to fetch this from AzureDiagnosis table when the http status code of those 500 requests ( from same ip) is "400" or "404" and then the next attempt resulted in http status code "200".The value 500 can be set as a variable as failurecountThreshold using the let function.

Starting can be like....

AzureDiagnostics

| where Category =="ApplicationGatewayAccessLog"
| where OperationName == "ApplicationGatewayFirewall"

Answer 1

Hello, this query might get you started https://github.com/Azure/Azure-Sentinel/blob/cfc14ce6570fa6e1a60edc59dbe4b597695c0b96/Solutions/Azure%20Web%20Application%20Firewall%20(WAF)/Analytic%20Rules/MaliciousWAFSessions.yaml#L39

A simpler version would be something like this (I dont have the data to test, so I haven't run it) - you'll have to add lines 7 and 15

let threshold = 500;
let successCode = dynamic(['400','404']);
let failcode = dynamic(['200']);
AzureDiagnostics
| where Category =="ApplicationGatewayAccessLog"
| where OperationName == "ApplicationGatewayFirewall"
// check for the faliure here???? | where <something> in (failcode)
  | summarize FirstFail = min(TimeGenerated), LastFail = arg_max(TimeGenerated, *), FailCount = count() by SrcIpAddr, bin(TimeGenerated, 10m)
  | where FailCount > threshold
  | join kind=inner (
      AzureDiagnostics
      | where Category =="ApplicationGatewayAccessLog"
      | where OperationName == "ApplicationGatewayFirewall"
      // check for the sucess here ???   | where <something> in (successcode)
      | summarize FirstSuccess = min(TimeGenerated), SuccessCount = count() by SrcIpAddr
      | where SuccessCount > 0
  ) on SrcIpAddr
  | where FirstSuccess > LastFail
  | extend TimeDiff = datetime_diff("minute", FirstSuccess, LastFail)