A potentially malicious web request was executed against a web server

let queryperiod = 1d;
let mode = dynamic(['Blocked', 'Detected']);
let successCode = dynamic(['200', '101','204', '400','504','304','401','500']);
let sessionBin = 30m;
AGWFirewallLogs
| where TimeGenerated > ago(queryperiod)
| where Action  in (mode)
| sort by Hostname asc, ClientIp asc, TimeGenerated asc
| extend SessionBlockedStarted = row_window_session(TimeGenerated, queryperiod, 10m, ((ClientIp != prev(ClientIp)) or (Hostname != prev(Hostname))))
| summarize SessionBlockedEnded = max(TimeGenerated), SessionBlockedCount = count() by Hostname, ClientIp, SessionBlockedStarted
| extend TimeKey = range(bin(SessionBlockedStarted, sessionBin), bin(SessionBlockedEnded, sessionBin), sessionBin)
| mv-expand TimeKey to typeof(datetime)
| join kind = inner(
    AGWAccessLogs
    | where TimeGenerated > ago(queryperiod)
    | where  (isempty(HttpStatus) or HttpStatus in (successCode))
    | extend TimeKey = bin(TimeGenerated, sessionBin)
    | extend Hostname = coalesce(Host,OriginalHost)
    | extend ClientIp = tostring(ClientIp)
) on TimeKey, Hostname, ClientIp
| where TimeGenerated between (SessionBlockedStarted..SessionBlockedEnded)
| extend
    OriginalRequestUriWithArgs = column_ifexists("OriginalRequestUriWithArgs", ""),
    ServerStatus = column_ifexists("ServerStatus", "")
| summarize
    SuccessfulAccessCount = count(),
    UserAgents = make_set(UserAgent, 250),
    RequestURIs = make_set(RequestUri, 250),
    OriginalRequestURIs = make_set(OriginalRequestUriWithArgs, 250),
    SuccessCodes = make_set(HttpStatus, 250),
    SuccessCodes_BackendServer = make_set(ServerStatus, 250),
    take_any(SessionBlockedEnded, SessionBlockedCount)
    by Hostname, ClientIp, SessionBlockedStarted 
| where SessionBlockedCount > SuccessfulAccessCount
| extend BlockvsSuccessRatio = SessionBlockedCount/toreal(SuccessfulAccessCount)
| sort by BlockvsSuccessRatio desc, SessionBlockedStarted asc
| project-reorder SessionBlockedStarted, SessionBlockedEnded, Hostname, ClientIp, SessionBlockedCount, SuccessfulAccessCount, BlockvsSuccessRatio, SuccessCodes, RequestURIs, OriginalRequestURIs, UserAgents

name: A potentially malicious web request was executed against a web server
tactics:
- InitialAccess
requiredDataConnectors:
- connectorId: WAF
  dataTypes:
  - AzureDiagnostics
triggerOperator: gt
id: 46ac55ae-47b8-414a-8f94-89ccd1962178
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Azure%20Web%20Application%20Firewall%20%28WAF%29/Analytic%20Rules/MaliciousWAFSessions.yaml
description: |
  'Detects unobstructed Web Application Firewall (WAF) activity in sessions where the WAF blocked incoming requests by computing the ratio between blocked requests and unobstructed WAF requests in these sessions (BlockvsSuccessRatio metric).
  A high ratio value for a given client IP and hostname calls for further investigation of the WAF data in that session, due to the significantly high number of blocked requests and a few unobstructed logs that may be malicious but have passed undetected through the WAF. The successCode variable defines what the detection thinks is a successful status code and should be altered to fit the environment.'
kind: Scheduled
entityMappings:
- entityType: IP
  fieldMappings:
  - columnName: ClientIp
    identifier: Address
queryFrequency: 1d
relevantTechniques:
- T1190
query: |
  let queryperiod = 1d;
  let mode = dynamic(['Blocked', 'Detected']);
  let successCode = dynamic(['200', '101','204', '400','504','304','401','500']);
  let sessionBin = 30m;
  AGWFirewallLogs
  | where TimeGenerated > ago(queryperiod)
  | where Action  in (mode)
  | sort by Hostname asc, ClientIp asc, TimeGenerated asc
  | extend SessionBlockedStarted = row_window_session(TimeGenerated, queryperiod, 10m, ((ClientIp != prev(ClientIp)) or (Hostname != prev(Hostname))))
  | summarize SessionBlockedEnded = max(TimeGenerated), SessionBlockedCount = count() by Hostname, ClientIp, SessionBlockedStarted
  | extend TimeKey = range(bin(SessionBlockedStarted, sessionBin), bin(SessionBlockedEnded, sessionBin), sessionBin)
  | mv-expand TimeKey to typeof(datetime)
  | join kind = inner(
      AGWAccessLogs
      | where TimeGenerated > ago(queryperiod)
      | where  (isempty(HttpStatus) or HttpStatus in (successCode))
      | extend TimeKey = bin(TimeGenerated, sessionBin)
      | extend Hostname = coalesce(Host,OriginalHost)
      | extend ClientIp = tostring(ClientIp)
  ) on TimeKey, Hostname, ClientIp
  | where TimeGenerated between (SessionBlockedStarted..SessionBlockedEnded)
  | extend
      OriginalRequestUriWithArgs = column_ifexists("OriginalRequestUriWithArgs", ""),
      ServerStatus = column_ifexists("ServerStatus", "")
  | summarize
      SuccessfulAccessCount = count(),
      UserAgents = make_set(UserAgent, 250),
      RequestURIs = make_set(RequestUri, 250),
      OriginalRequestURIs = make_set(OriginalRequestUriWithArgs, 250),
      SuccessCodes = make_set(HttpStatus, 250),
      SuccessCodes_BackendServer = make_set(ServerStatus, 250),
      take_any(SessionBlockedEnded, SessionBlockedCount)
      by Hostname, ClientIp, SessionBlockedStarted 
  | where SessionBlockedCount > SuccessfulAccessCount
  | extend BlockvsSuccessRatio = SessionBlockedCount/toreal(SuccessfulAccessCount)
  | sort by BlockvsSuccessRatio desc, SessionBlockedStarted asc
  | project-reorder SessionBlockedStarted, SessionBlockedEnded, Hostname, ClientIp, SessionBlockedCount, SuccessfulAccessCount, BlockvsSuccessRatio, SuccessCodes, RequestURIs, OriginalRequestURIs, UserAgents
triggerThreshold: 0
queryPeriod: 1d
version: 1.0.6
status: Available
severity: Medium

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "apiVersion": "2024-01-01-preview",
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/46ac55ae-47b8-414a-8f94-89ccd1962178')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/46ac55ae-47b8-414a-8f94-89ccd1962178')]",
      "properties": {
        "alertRuleTemplateName": "46ac55ae-47b8-414a-8f94-89ccd1962178",
        "customDetails": null,
        "description": "'Detects unobstructed Web Application Firewall (WAF) activity in sessions where the WAF blocked incoming requests by computing the ratio between blocked requests and unobstructed WAF requests in these sessions (BlockvsSuccessRatio metric).\nA high ratio value for a given client IP and hostname calls for further investigation of the WAF data in that session, due to the significantly high number of blocked requests and a few unobstructed logs that may be malicious but have passed undetected through the WAF. The successCode variable defines what the detection thinks is a successful status code and should be altered to fit the environment.'\n",
        "displayName": "A potentially malicious web request was executed against a web server",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "ClientIp",
                "identifier": "Address"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Azure%20Web%20Application%20Firewall%20%28WAF%29/Analytic%20Rules/MaliciousWAFSessions.yaml",
        "query": "let queryperiod = 1d;\nlet mode = dynamic(['Blocked', 'Detected']);\nlet successCode = dynamic(['200', '101','204', '400','504','304','401','500']);\nlet sessionBin = 30m;\nAGWFirewallLogs\n| where TimeGenerated > ago(queryperiod)\n| where Action  in (mode)\n| sort by Hostname asc, ClientIp asc, TimeGenerated asc\n| extend SessionBlockedStarted = row_window_session(TimeGenerated, queryperiod, 10m, ((ClientIp != prev(ClientIp)) or (Hostname != prev(Hostname))))\n| summarize SessionBlockedEnded = max(TimeGenerated), SessionBlockedCount = count() by Hostname, ClientIp, SessionBlockedStarted\n| extend TimeKey = range(bin(SessionBlockedStarted, sessionBin), bin(SessionBlockedEnded, sessionBin), sessionBin)\n| mv-expand TimeKey to typeof(datetime)\n| join kind = inner(\n    AGWAccessLogs\n    | where TimeGenerated > ago(queryperiod)\n    | where  (isempty(HttpStatus) or HttpStatus in (successCode))\n    | extend TimeKey = bin(TimeGenerated, sessionBin)\n    | extend Hostname = coalesce(Host,OriginalHost)\n    | extend ClientIp = tostring(ClientIp)\n) on TimeKey, Hostname, ClientIp\n| where TimeGenerated between (SessionBlockedStarted..SessionBlockedEnded)\n| extend\n    OriginalRequestUriWithArgs = column_ifexists(\"OriginalRequestUriWithArgs\", \"\"),\n    ServerStatus = column_ifexists(\"ServerStatus\", \"\")\n| summarize\n    SuccessfulAccessCount = count(),\n    UserAgents = make_set(UserAgent, 250),\n    RequestURIs = make_set(RequestUri, 250),\n    OriginalRequestURIs = make_set(OriginalRequestUriWithArgs, 250),\n    SuccessCodes = make_set(HttpStatus, 250),\n    SuccessCodes_BackendServer = make_set(ServerStatus, 250),\n    take_any(SessionBlockedEnded, SessionBlockedCount)\n    by Hostname, ClientIp, SessionBlockedStarted \n| where SessionBlockedCount > SuccessfulAccessCount\n| extend BlockvsSuccessRatio = SessionBlockedCount/toreal(SuccessfulAccessCount)\n| sort by BlockvsSuccessRatio desc, SessionBlockedStarted asc\n| project-reorder SessionBlockedStarted, SessionBlockedEnded, Hostname, ClientIp, SessionBlockedCount, SuccessfulAccessCount, BlockvsSuccessRatio, SuccessCodes, RequestURIs, OriginalRequestURIs, UserAgents\n",
        "queryFrequency": "P1D",
        "queryPeriod": "P1D",
        "severity": "Medium",
        "status": "Available",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "InitialAccess"
        ],
        "techniques": [
          "T1190"
        ],
        "templateVersion": "1.0.6",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}