Syntax errors stateful anomaly on database

id: c815008d-f4d1-4645-b13b-8b4bc188d5de
name: Syntax errors stateful anomaly on database
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.'
severity: Medium
requiredDataConnectors:
  - connectorId: AzureSql
    dataTypes:
      - AzureDiagnostics
queryFrequency: 1h
queryPeriod: 14d
triggerOperator: gt
triggerThreshold: 0
status: Available
tactics:
  - InitialAccess
relevantTechniques:
  - T1190 
tags:
  - SQL
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'
    | project TimeGenerated = timeSlice, Database, ClientIp, ApplicationName, PrincipalName, HostName, ResourceId, countEvents, countStatements, countStatementsWithError, anyMonitoredStatement, anyInfo, anomalyScore 
    | extend Name = tostring(split(PrincipalName,'@',0)[0]), UPNSuffix = tostring(split(PrincipalName,'@',1)[0])    
entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: Name
        columnName: Name
      - identifier: UPNSuffix
        columnName: UPNSuffix
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: ClientIp
  - entityType: Host
    fieldMappings:
      - identifier: HostName
        columnName: HostName
  - entityType: CloudApplication
    fieldMappings:
      - identifier: Name
        columnName: ApplicationName
  - entityType: AzureResource
    fieldMappings:
      - identifier: ResourceId
        columnName: ResourceId
alertDetailsOverride:
  alertDisplayNameFormat: 'Syntax errors stateful anomaly on database {{Database}}'
  alertDescriptionFormat: 'An anomaly was detected on database {{Database}} with {{countStatementsWithError}} statements with monitored errors in the last hour. Investigate the database activity for potential malicious attempts to gain illegitimate access to the data.'
version: 1.1.2
kind: Scheduled