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

Syntax errors stateful anomaly on database

Back
Idc815008d-f4d1-4645-b13b-8b4bc188d5de
RulenameSyntax errors stateful anomaly on database
DescriptionThis query batches of distinct SQL queries that failed with error codes that might indicate malicious attempts to gain illegitimate access to the data. When blind type of attacks are performed (such as SQL injection of fuzzying), the attempted queries are often malformed and fail on wrong syntax (error 102) or wrong escaping (error 105). Thus, if a large number of different queries fail on such errors in a short amount of time, this might indicate attempted attack.
SeverityMedium
TacticsInitialAccess
TechniquesT1190
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-ErrorsSyntaxStatefulAnomalyOnDatabase.yaml
Version1.1.1
Arm templatec815008d-f4d1-4645-b13b-8b4bc188d5de.json
Deploy To Azure
let monitoredStatementsThreshold = 1;           // Minimal number of monitored statements in the slice to trigger an anomaly.
let trainingSlicesThreshold = 5;                // The maximal amount of slices with monitored statements in the training window before anomaly detection is throttled.
let timeSliceSize = 1h;                         // The size of the single timeSlice for individual aggregation.
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 monitoredErrors = pack_array(102, 105);     // List of sql error codes relevant for this detection.
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,
              Error = case( additional_information_s has 'error_code', toint(extract("<error_code>([0-9.]+)", 1, additional_information_s))
                    , additional_information_s has 'failure_reason', toint(extract("<failure_reason>Err ([0-9.]+)", 1, additional_information_s))
                    , 0),
              State = case( additional_information_s has 'error_state', toint(extract("<error_state>([0-9.]+)", 1, additional_information_s))
                    , additional_information_s has 'failure_reason', toint(extract("<failure_reason>Err ([0-9.]+), Level ([0-9.]+)", 2, additional_information_s))
                    , 0),
              AdditionalInfo = additional_information_s, timeSlice = floor(TimeGenerated, timeSliceSize)
    | summarize countEvents = count(), countStatements = dcount(Statement), countStatementsWithError = dcountif(Statement, Error in (monitoredErrors))
        , anyMonitoredStatement = anyif(Statement, Error in (monitoredErrors)), anyInfo = anyif(AdditionalInfo, Error in (monitoredErrors))
        by Database, ClientIp, ApplicationName, PrincipalName, timeSlice,HostName,ResourceId
    | extend WindowType = case( timeSlice >= ago(detectionWindow), 'detection',
                                           (ago(trainingWindow) <= timeSlice and timeSlice < ago(detectionWindow)), 'training', 'other')
    | where WindowType in ('detection', 'training'));
let trainingSet =
    processedData
    | where WindowType == 'training'
    | summarize countSlicesWithErrors = dcountif(timeSlice, countStatementsWithError >= monitoredStatementsThreshold)
        by Database;
processedData
| where WindowType == 'detection' 
| join kind = inner (trainingSet) on Database
| extend IsErrorAnomalyOnStatement = iff(((countStatementsWithError >= monitoredStatementsThreshold) and (countSlicesWithErrors <= trainingSlicesThreshold)), true, false)
    , anomalyScore = round(countStatementsWithError/monitoredStatementsThreshold, 0)
| where IsErrorAnomalyOnStatement == 'true'
| sort by anomalyScore desc, timeSlice desc
| extend Name = tostring(split(PrincipalName,'@',0)[0]), UPNSuffix = tostring(split(PrincipalName,'@',1)[0])    
triggerOperator: gt
queryFrequency: 1h
description: |
    'This query batches of distinct SQL queries that failed with error codes that might indicate malicious attempts to gain illegitimate access to the data. When blind type of attacks are performed (such as SQL injection of fuzzying), the attempted queries are often malformed and fail on wrong syntax (error 102) or wrong escaping (error 105). Thus, if a large number of different queries fail on such errors in a short amount of time, this might indicate attempted attack.'
status: Available
kind: Scheduled
triggerThreshold: 0
requiredDataConnectors:
- connectorId: AzureSql
  dataTypes:
  - AzureDiagnostics
version: 1.1.1
queryPeriod: 14d
name: Syntax errors stateful anomaly on database
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Azure SQL Database solution for sentinel/Analytic Rules/Detection-ErrorsSyntaxStatefulAnomalyOnDatabase.yaml
id: c815008d-f4d1-4645-b13b-8b4bc188d5de
tags:
- SQL
tactics:
- InitialAccess
relevantTechniques:
- T1190
severity: Medium
entityMappings:
- fieldMappings:
  - identifier: Name
    columnName: Name
  - identifier: UPNSuffix
    columnName: UPNSuffix
  entityType: Account
- fieldMappings:
  - identifier: Address
    columnName: ClientIp
  entityType: IP
- fieldMappings:
  - identifier: HostName
    columnName: HostName
  entityType: Host
- fieldMappings:
  - identifier: Name
    columnName: ApplicationName
  entityType: CloudApplication
- fieldMappings:
  - identifier: ResourceId
    columnName: ResourceId
  entityType: AzureResource
query: |
  let monitoredStatementsThreshold = 1;           // Minimal number of monitored statements in the slice to trigger an anomaly.
  let trainingSlicesThreshold = 5;                // The maximal amount of slices with monitored statements in the training window before anomaly detection is throttled.
  let timeSliceSize = 1h;                         // The size of the single timeSlice for individual aggregation.
  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 monitoredErrors = pack_array(102, 105);     // List of sql error codes relevant for this detection.
  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,
                Error = case( additional_information_s has 'error_code', toint(extract("<error_code>([0-9.]+)", 1, additional_information_s))
                      , additional_information_s has 'failure_reason', toint(extract("<failure_reason>Err ([0-9.]+)", 1, additional_information_s))
                      , 0),
                State = case( additional_information_s has 'error_state', toint(extract("<error_state>([0-9.]+)", 1, additional_information_s))
                      , additional_information_s has 'failure_reason', toint(extract("<failure_reason>Err ([0-9.]+), Level ([0-9.]+)", 2, additional_information_s))
                      , 0),
                AdditionalInfo = additional_information_s, timeSlice = floor(TimeGenerated, timeSliceSize)
      | summarize countEvents = count(), countStatements = dcount(Statement), countStatementsWithError = dcountif(Statement, Error in (monitoredErrors))
          , anyMonitoredStatement = anyif(Statement, Error in (monitoredErrors)), anyInfo = anyif(AdditionalInfo, Error in (monitoredErrors))
          by Database, ClientIp, ApplicationName, PrincipalName, timeSlice,HostName,ResourceId
      | extend WindowType = case( timeSlice >= ago(detectionWindow), 'detection',
                                             (ago(trainingWindow) <= timeSlice and timeSlice < ago(detectionWindow)), 'training', 'other')
      | where WindowType in ('detection', 'training'));
  let trainingSet =
      processedData
      | where WindowType == 'training'
      | summarize countSlicesWithErrors = dcountif(timeSlice, countStatementsWithError >= monitoredStatementsThreshold)
          by Database;
  processedData
  | where WindowType == 'detection' 
  | join kind = inner (trainingSet) on Database
  | extend IsErrorAnomalyOnStatement = iff(((countStatementsWithError >= monitoredStatementsThreshold) and (countSlicesWithErrors <= trainingSlicesThreshold)), true, false)
      , anomalyScore = round(countStatementsWithError/monitoredStatementsThreshold, 0)
  | where IsErrorAnomalyOnStatement == 'true'
  | sort by anomalyScore desc, timeSlice desc
  | extend Name = tostring(split(PrincipalName,'@',0)[0]), UPNSuffix = tostring(split(PrincipalName,'@',1)[0])      
{
  "$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/c815008d-f4d1-4645-b13b-8b4bc188d5de')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/c815008d-f4d1-4645-b13b-8b4bc188d5de')]",
      "properties": {
        "alertRuleTemplateName": "c815008d-f4d1-4645-b13b-8b4bc188d5de",
        "customDetails": null,
        "description": "'This query batches of distinct SQL queries that failed with error codes that might indicate malicious attempts to gain illegitimate access to the data. When blind type of attacks are performed (such as SQL injection of fuzzying), the attempted queries are often malformed and fail on wrong syntax (error 102) or wrong escaping (error 105). Thus, if a large number of different queries fail on such errors in a short amount of time, this might indicate attempted attack.'\n",
        "displayName": "Syntax errors 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-ErrorsSyntaxStatefulAnomalyOnDatabase.yaml",
        "query": "let monitoredStatementsThreshold = 1;           // Minimal number of monitored statements in the slice to trigger an anomaly.\nlet trainingSlicesThreshold = 5;                // The maximal amount of slices with monitored statements in the training window before anomaly detection is throttled.\nlet timeSliceSize = 1h;                         // The size of the single timeSlice for individual aggregation.\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 monitoredErrors = pack_array(102, 105);     // List of sql error codes relevant for this detection.\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              Error = case( additional_information_s has 'error_code', toint(extract(\"<error_code>([0-9.]+)\", 1, additional_information_s))\n                    , additional_information_s has 'failure_reason', toint(extract(\"<failure_reason>Err ([0-9.]+)\", 1, additional_information_s))\n                    , 0),\n              State = case( additional_information_s has 'error_state', toint(extract(\"<error_state>([0-9.]+)\", 1, additional_information_s))\n                    , additional_information_s has 'failure_reason', toint(extract(\"<failure_reason>Err ([0-9.]+), Level ([0-9.]+)\", 2, additional_information_s))\n                    , 0),\n              AdditionalInfo = additional_information_s, timeSlice = floor(TimeGenerated, timeSliceSize)\n    | summarize countEvents = count(), countStatements = dcount(Statement), countStatementsWithError = dcountif(Statement, Error in (monitoredErrors))\n        , anyMonitoredStatement = anyif(Statement, Error in (monitoredErrors)), anyInfo = anyif(AdditionalInfo, Error in (monitoredErrors))\n        by Database, ClientIp, ApplicationName, PrincipalName, timeSlice,HostName,ResourceId\n    | extend WindowType = case( timeSlice >= ago(detectionWindow), 'detection',\n                                           (ago(trainingWindow) <= timeSlice and timeSlice < ago(detectionWindow)), 'training', 'other')\n    | where WindowType in ('detection', 'training'));\nlet trainingSet =\n    processedData\n    | where WindowType == 'training'\n    | summarize countSlicesWithErrors = dcountif(timeSlice, countStatementsWithError >= monitoredStatementsThreshold)\n        by Database;\nprocessedData\n| where WindowType == 'detection' \n| join kind = inner (trainingSet) on Database\n| extend IsErrorAnomalyOnStatement = iff(((countStatementsWithError >= monitoredStatementsThreshold) and (countSlicesWithErrors <= trainingSlicesThreshold)), true, false)\n    , anomalyScore = round(countStatementsWithError/monitoredStatementsThreshold, 0)\n| where IsErrorAnomalyOnStatement == 'true'\n| sort by anomalyScore desc, timeSlice 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": [
          "InitialAccess"
        ],
        "tags": [
          "SQL"
        ],
        "techniques": [
          "T1190"
        ],
        "templateVersion": "1.1.1",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}