Microsoft Sentinel Analytic Rules
cloudbrothers.infoAzure Sentinel RepoToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeBack to homepage

Response rows stateful anomaly on database

Back
Id9851c360-5fd5-4bae-a117-b66d8476bf5e
RulenameResponse rows stateful anomaly on database
DescriptionGoal: To detect anomalous data exfiltration. This query detects SQL queries that accessed a large number of rows, which is significantly higher than normal for this database.

The calculation is made inside recent time window (defined by ‘detectionWindow’ parameter), and the anomaly is calculated based on previous training window

(defined by ’trainingWindow’ parameter). The user can set the minimal threshold for anomaly by changing the threshold parameters volThresholdZ and volThresholdQ (higher thresholds will detect only more severe anomalies).
SeverityMedium
TacticsExfiltration
TechniquesT1537
T1567
Required data connectorsAzureSql
KindScheduled
Query frequency1h
Query period14d
Trigger threshold0
Trigger operatorgt
Source Urihttps://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Azure SQL Database solution for sentinel/Analytic Rules/Detection-VolumeResponseRowsStatefulAnomalyOnDatabase.yaml
Version1.1.1
Arm template9851c360-5fd5-4bae-a117-b66d8476bf5e.json
Deploy To Azure
let volumeThresholdZ = 3.0;                     // Minimal threshold for the Zscore to trigger anomaly (number of standard deviations above mean). If set higher, only very significant alerts will fire.
let volumeThresholdQ = volumeThresholdZ;        // Minimal threshold for the Qscore to trigger anomaly (number of Inter-Percentile Ranges above high percentile). If set higher, only very significant alerts will fire.
let volumeThresholdHardcoded = 500;             // Minimal value for the volume metric to trigger anomaly.
let detectionWindow = 1h;                       // The size of the recent detection window for detecting anomalies.  
let trainingWindow = detectionWindow + 14d;     // The size of the training window before the detection window for learning the normal state.
let monitoredColumn = 'ResponseRows';           // The name of the column for volumetric anomalies.
let processedData = materialize (
    AzureDiagnostics
    | where TimeGenerated >= ago(trainingWindow)
    | where Category == 'SQLSecurityAuditEvents' and action_id_s has_any ("RCM", "BCM") // Keep only SQL affected rows
    | project TimeGenerated, PrincipalName = server_principal_name_s, ClientIp = client_ip_s, HostName = host_name_s, ResourceId,
              ApplicationName = application_name_s, ActionName = action_name_s, Database = strcat(LogicalServerName_s, '/', database_name_s),
              IsSuccess = succeeded_s, AffectedRows = affected_rows_d,
              ResponseRows = response_rows_d, Statement = statement_s
    | extend QuantityColumn = column_ifexists(monitoredColumn, 0)
    | extend WindowType = case( TimeGenerated >= ago(detectionWindow), 'detection',
                                           (ago(trainingWindow) <= TimeGenerated and TimeGenerated < ago(detectionWindow)), 'training', 'other')
    | where WindowType in ('detection', 'training'));
let trainingSet =
    processedData
    | where WindowType == 'training'
    | summarize AvgVal = round(avg(QuantityColumn), 2), StdVal = round(stdev(QuantityColumn), 2), N = count(),
                P99Val = round(percentile(QuantityColumn, 99), 2), P50Val = round(percentile(QuantityColumn, 50), 2)
      by Database;
processedData
| where WindowType == 'detection'
| join kind = inner (trainingSet) on Database
| extend ZScoreVal = iff(N >= 20, round(todouble(QuantityColumn - AvgVal) / todouble(StdVal + 1), 2), 0.00),
         QScoreVal = iff(N >= 20, round(todouble(QuantityColumn - P99Val) / todouble(P99Val - P50Val + 1), 2), 0.00)
| extend IsVolumeAnomalyOnVal = iff((ZScoreVal > volumeThresholdZ and QScoreVal > volumeThresholdQ and QuantityColumn > volumeThresholdHardcoded), true, false), AnomalyScore = round((ZScoreVal + QScoreVal)/2, 0)
| project TimeGenerated, Database, PrincipalName, ClientIp, HostName, ApplicationName, ActionName, Statement,
          IsSuccess, ResponseRows, AffectedRows, IsVolumeAnomalyOnVal, AnomalyScore, ResourceId
| where IsVolumeAnomalyOnVal == 'true'
| sort by AnomalyScore desc, TimeGenerated desc
| extend Name = tostring(split(PrincipalName,'@',0)[0]), UPNSuffix = tostring(split(PrincipalName,'@',1)[0])
tags:
- SQL
status: Available
triggerThreshold: 0
relevantTechniques:
- T1537
- T1567
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Azure SQL Database solution for sentinel/Analytic Rules/Detection-VolumeResponseRowsStatefulAnomalyOnDatabase.yaml
requiredDataConnectors:
- dataTypes:
  - AzureDiagnostics
  connectorId: AzureSql
queryPeriod: 14d
tactics:
- Exfiltration
severity: Medium
triggerOperator: gt
description: |
  'Goal: To detect anomalous data exfiltration. This query detects SQL queries that accessed a large number of rows, which is significantly higher than normal for this database.
   The calculation is made inside recent time window (defined by 'detectionWindow' parameter), and the anomaly is calculated based on previous training window 
   (defined by 'trainingWindow' parameter). The user can set the minimal threshold for anomaly by changing the threshold parameters volThresholdZ and volThresholdQ (higher thresholds will detect only more severe anomalies).'  
query: |
  let volumeThresholdZ = 3.0;                     // Minimal threshold for the Zscore to trigger anomaly (number of standard deviations above mean). If set higher, only very significant alerts will fire.
  let volumeThresholdQ = volumeThresholdZ;        // Minimal threshold for the Qscore to trigger anomaly (number of Inter-Percentile Ranges above high percentile). If set higher, only very significant alerts will fire.
  let volumeThresholdHardcoded = 500;             // Minimal value for the volume metric to trigger anomaly.
  let detectionWindow = 1h;                       // The size of the recent detection window for detecting anomalies.  
  let trainingWindow = detectionWindow + 14d;     // The size of the training window before the detection window for learning the normal state.
  let monitoredColumn = 'ResponseRows';           // The name of the column for volumetric anomalies.
  let processedData = materialize (
      AzureDiagnostics
      | where TimeGenerated >= ago(trainingWindow)
      | where Category == 'SQLSecurityAuditEvents' and action_id_s has_any ("RCM", "BCM") // Keep only SQL affected rows
      | project TimeGenerated, PrincipalName = server_principal_name_s, ClientIp = client_ip_s, HostName = host_name_s, ResourceId,
                ApplicationName = application_name_s, ActionName = action_name_s, Database = strcat(LogicalServerName_s, '/', database_name_s),
                IsSuccess = succeeded_s, AffectedRows = affected_rows_d,
                ResponseRows = response_rows_d, Statement = statement_s
      | extend QuantityColumn = column_ifexists(monitoredColumn, 0)
      | extend WindowType = case( TimeGenerated >= ago(detectionWindow), 'detection',
                                             (ago(trainingWindow) <= TimeGenerated and TimeGenerated < ago(detectionWindow)), 'training', 'other')
      | where WindowType in ('detection', 'training'));
  let trainingSet =
      processedData
      | where WindowType == 'training'
      | summarize AvgVal = round(avg(QuantityColumn), 2), StdVal = round(stdev(QuantityColumn), 2), N = count(),
                  P99Val = round(percentile(QuantityColumn, 99), 2), P50Val = round(percentile(QuantityColumn, 50), 2)
        by Database;
  processedData
  | where WindowType == 'detection'
  | join kind = inner (trainingSet) on Database
  | extend ZScoreVal = iff(N >= 20, round(todouble(QuantityColumn - AvgVal) / todouble(StdVal + 1), 2), 0.00),
           QScoreVal = iff(N >= 20, round(todouble(QuantityColumn - P99Val) / todouble(P99Val - P50Val + 1), 2), 0.00)
  | extend IsVolumeAnomalyOnVal = iff((ZScoreVal > volumeThresholdZ and QScoreVal > volumeThresholdQ and QuantityColumn > volumeThresholdHardcoded), true, false), AnomalyScore = round((ZScoreVal + QScoreVal)/2, 0)
  | project TimeGenerated, Database, PrincipalName, ClientIp, HostName, ApplicationName, ActionName, Statement,
            IsSuccess, ResponseRows, AffectedRows, IsVolumeAnomalyOnVal, AnomalyScore, ResourceId
  | where IsVolumeAnomalyOnVal == 'true'
  | sort by AnomalyScore desc, TimeGenerated desc
  | extend Name = tostring(split(PrincipalName,'@',0)[0]), UPNSuffix = tostring(split(PrincipalName,'@',1)[0])  
name: Response rows stateful anomaly on database
version: 1.1.1
kind: Scheduled
id: 9851c360-5fd5-4bae-a117-b66d8476bf5e
queryFrequency: 1h
entityMappings:
- entityType: Account
  fieldMappings:
  - columnName: Name
    identifier: Name
  - columnName: UPNSuffix
    identifier: UPNSuffix
- entityType: IP
  fieldMappings:
  - columnName: ClientIp
    identifier: Address
- entityType: Host
  fieldMappings:
  - columnName: HostName
    identifier: HostName
- entityType: CloudApplication
  fieldMappings:
  - columnName: ApplicationName
    identifier: Name
- entityType: AzureResource
  fieldMappings:
  - columnName: ResourceId
    identifier: ResourceId
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "apiVersion": "2023-02-01-preview",
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/9851c360-5fd5-4bae-a117-b66d8476bf5e')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/9851c360-5fd5-4bae-a117-b66d8476bf5e')]",
      "properties": {
        "alertRuleTemplateName": "9851c360-5fd5-4bae-a117-b66d8476bf5e",
        "customDetails": null,
        "description": "'Goal: To detect anomalous data exfiltration. This query detects SQL queries that accessed a large number of rows, which is significantly higher than normal for this database.\n The calculation is made inside recent time window (defined by 'detectionWindow' parameter), and the anomaly is calculated based on previous training window \n (defined by 'trainingWindow' parameter). The user can set the minimal threshold for anomaly by changing the threshold parameters volThresholdZ and volThresholdQ (higher thresholds will detect only more severe anomalies).'\n",
        "displayName": "Response rows stateful anomaly on database",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "Name",
                "identifier": "Name"
              },
              {
                "columnName": "UPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "ClientIp",
                "identifier": "Address"
              }
            ]
          },
          {
            "entityType": "Host",
            "fieldMappings": [
              {
                "columnName": "HostName",
                "identifier": "HostName"
              }
            ]
          },
          {
            "entityType": "CloudApplication",
            "fieldMappings": [
              {
                "columnName": "ApplicationName",
                "identifier": "Name"
              }
            ]
          },
          {
            "entityType": "AzureResource",
            "fieldMappings": [
              {
                "columnName": "ResourceId",
                "identifier": "ResourceId"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Azure SQL Database solution for sentinel/Analytic Rules/Detection-VolumeResponseRowsStatefulAnomalyOnDatabase.yaml",
        "query": "let volumeThresholdZ = 3.0;                     // Minimal threshold for the Zscore to trigger anomaly (number of standard deviations above mean). If set higher, only very significant alerts will fire.\nlet volumeThresholdQ = volumeThresholdZ;        // Minimal threshold for the Qscore to trigger anomaly (number of Inter-Percentile Ranges above high percentile). If set higher, only very significant alerts will fire.\nlet volumeThresholdHardcoded = 500;             // Minimal value for the volume metric to trigger anomaly.\nlet detectionWindow = 1h;                       // The size of the recent detection window for detecting anomalies.  \nlet trainingWindow = detectionWindow + 14d;     // The size of the training window before the detection window for learning the normal state.\nlet monitoredColumn = 'ResponseRows';           // The name of the column for volumetric anomalies.\nlet processedData = materialize (\n    AzureDiagnostics\n    | where TimeGenerated >= ago(trainingWindow)\n    | where Category == 'SQLSecurityAuditEvents' and action_id_s has_any (\"RCM\", \"BCM\") // Keep only SQL affected rows\n    | project TimeGenerated, PrincipalName = server_principal_name_s, ClientIp = client_ip_s, HostName = host_name_s, ResourceId,\n              ApplicationName = application_name_s, ActionName = action_name_s, Database = strcat(LogicalServerName_s, '/', database_name_s),\n              IsSuccess = succeeded_s, AffectedRows = affected_rows_d,\n              ResponseRows = response_rows_d, Statement = statement_s\n    | extend QuantityColumn = column_ifexists(monitoredColumn, 0)\n    | extend WindowType = case( TimeGenerated >= ago(detectionWindow), 'detection',\n                                           (ago(trainingWindow) <= TimeGenerated and TimeGenerated < ago(detectionWindow)), 'training', 'other')\n    | where WindowType in ('detection', 'training'));\nlet trainingSet =\n    processedData\n    | where WindowType == 'training'\n    | summarize AvgVal = round(avg(QuantityColumn), 2), StdVal = round(stdev(QuantityColumn), 2), N = count(),\n                P99Val = round(percentile(QuantityColumn, 99), 2), P50Val = round(percentile(QuantityColumn, 50), 2)\n      by Database;\nprocessedData\n| where WindowType == 'detection'\n| join kind = inner (trainingSet) on Database\n| extend ZScoreVal = iff(N >= 20, round(todouble(QuantityColumn - AvgVal) / todouble(StdVal + 1), 2), 0.00),\n         QScoreVal = iff(N >= 20, round(todouble(QuantityColumn - P99Val) / todouble(P99Val - P50Val + 1), 2), 0.00)\n| extend IsVolumeAnomalyOnVal = iff((ZScoreVal > volumeThresholdZ and QScoreVal > volumeThresholdQ and QuantityColumn > volumeThresholdHardcoded), true, false), AnomalyScore = round((ZScoreVal + QScoreVal)/2, 0)\n| project TimeGenerated, Database, PrincipalName, ClientIp, HostName, ApplicationName, ActionName, Statement,\n          IsSuccess, ResponseRows, AffectedRows, IsVolumeAnomalyOnVal, AnomalyScore, ResourceId\n| where IsVolumeAnomalyOnVal == 'true'\n| sort by AnomalyScore desc, TimeGenerated desc\n| extend Name = tostring(split(PrincipalName,'@',0)[0]), UPNSuffix = tostring(split(PrincipalName,'@',1)[0])\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P14D",
        "severity": "Medium",
        "status": "Available",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Exfiltration"
        ],
        "tags": [
          "SQL"
        ],
        "techniques": [
          "T1537",
          "T1567"
        ],
        "templateVersion": "1.1.1",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}