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

Danaa Salam 40 Reputation points
2023-11-02T02:10:38.19+00:00

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"

Microsoft Sentinel
Microsoft Sentinel
A scalable, cloud-native solution for security information event management and security orchestration automated response. Previously known as Azure Sentinel.
1,026 questions
{count} votes

Accepted answer
  1. Clive Watson 5,951 Reputation points MVP
    2023-11-02T10:28:24.04+00:00

    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)
    
    0 comments No comments

0 additional answers

Sort by: Most helpful