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

TI Map IP Entity to Azure SQL Security Audit Events

Back
Idd0aa8969-1bbe-4da3-9e76-09e5f67c9d85
RulenameTI Map IP Entity to Azure SQL Security Audit Events
DescriptionThis query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in SQL Security Audit Events.
SeverityMedium
TacticsImpact
Required data connectorsAzureSql
MicrosoftDefenderThreatIntelligence
ThreatIntelligence
ThreatIntelligenceTaxii
KindScheduled
Query frequency1h
Query period14d
Trigger threshold0
Trigger operatorgt
Source Urihttps://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/IPEntity_AzureSQL.yaml
Version1.3.1
Arm templated0aa8969-1bbe-4da3-9e76-09e5f67c9d85.json
Deploy To Azure
let dt_lookBack = 1h; // Look back 1 hour for AzureDiagnostics logs
let ioc_lookBack = 14d; // Look back 14 days for threat intelligence indicators
// Fetch threat intelligence indicators related to IP addresses
let IP_Indicators = ThreatIntelligenceIndicator
  | where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)
  | where TimeGenerated >= ago(ioc_lookBack)
  | extend TI_ipEntity = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)
  | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)
  | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(EmailSourceIpAddress), EmailSourceIpAddress, TI_ipEntity)
  | where ipv4_is_private(TI_ipEntity) == false and  TI_ipEntity !startswith "fe80" and TI_ipEntity !startswith "::" and TI_ipEntity !startswith "127."
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
  | where Active == true and ExpirationDateTime > now();
// Perform a join between IP indicators and AzureDiagnostics logs for SQL Security Audit events
IP_Indicators
  // Use innerunique to keep performance fast and result set low, as we only need one match to indicate potential malicious activity that needs investigation
  | join kind=innerunique (
      AzureDiagnostics
      | where TimeGenerated >= ago(dt_lookBack)
      | where ResourceProvider == 'MICROSOFT.SQL'
      | where Category == 'SQLSecurityAuditEvents'
      | extend SQLSecurityAuditEvents_TimeGenerated = TimeGenerated
      | extend ClientIP = column_ifexists("client_ip_s", "Not Available")
      | extend Action = column_ifexists("action_name_s", "Not Available")
      | extend Application = column_ifexists("application_name_s", "Not Available")
      | extend HostName = column_ifexists("host_name_s", "Not Available")
  )
  on $left.TI_ipEntity == $right.ClientIP
  // Filter out logs that occurred after the expiration of the corresponding indicator
  | where SQLSecurityAuditEvents_TimeGenerated < ExpirationDateTime
  // Group the results by IndicatorId and ClientIP, and keep the log entry with the latest timestamp
  | summarize SQLSecurityAuditEvents_TimeGenerated = arg_max(SQLSecurityAuditEvents_TimeGenerated, *) by IndicatorId, ClientIP
  // Select the desired output fields
  | project SQLSecurityAuditEvents_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore,
    TI_ipEntity, ResourceId, ClientIP, Action, Application, HostName, NetworkIP, NetworkDestinationIP, NetworkSourceIP, EmailSourceIpAddress, Type
  // Rename the timestamp field
  | extend timestamp = SQLSecurityAuditEvents_TimeGenerated
name: TI Map IP Entity to Azure SQL Security Audit Events
entityMappings:
- entityType: IP
  fieldMappings:
  - columnName: ClientIP
    identifier: Address
id: d0aa8969-1bbe-4da3-9e76-09e5f67c9d85
triggerThreshold: 0
severity: Medium
tactics:
- Impact
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/IPEntity_AzureSQL.yaml
queryPeriod: 14d
queryFrequency: 1h
version: 1.3.1
triggerOperator: gt
description: |
    This query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in SQL Security Audit Events.
query: |
  let dt_lookBack = 1h; // Look back 1 hour for AzureDiagnostics logs
  let ioc_lookBack = 14d; // Look back 14 days for threat intelligence indicators
  // Fetch threat intelligence indicators related to IP addresses
  let IP_Indicators = ThreatIntelligenceIndicator
    | where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)
    | where TimeGenerated >= ago(ioc_lookBack)
    | extend TI_ipEntity = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)
    | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)
    | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(EmailSourceIpAddress), EmailSourceIpAddress, TI_ipEntity)
    | where ipv4_is_private(TI_ipEntity) == false and  TI_ipEntity !startswith "fe80" and TI_ipEntity !startswith "::" and TI_ipEntity !startswith "127."
    | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
    | where Active == true and ExpirationDateTime > now();
  // Perform a join between IP indicators and AzureDiagnostics logs for SQL Security Audit events
  IP_Indicators
    // Use innerunique to keep performance fast and result set low, as we only need one match to indicate potential malicious activity that needs investigation
    | join kind=innerunique (
        AzureDiagnostics
        | where TimeGenerated >= ago(dt_lookBack)
        | where ResourceProvider == 'MICROSOFT.SQL'
        | where Category == 'SQLSecurityAuditEvents'
        | extend SQLSecurityAuditEvents_TimeGenerated = TimeGenerated
        | extend ClientIP = column_ifexists("client_ip_s", "Not Available")
        | extend Action = column_ifexists("action_name_s", "Not Available")
        | extend Application = column_ifexists("application_name_s", "Not Available")
        | extend HostName = column_ifexists("host_name_s", "Not Available")
    )
    on $left.TI_ipEntity == $right.ClientIP
    // Filter out logs that occurred after the expiration of the corresponding indicator
    | where SQLSecurityAuditEvents_TimeGenerated < ExpirationDateTime
    // Group the results by IndicatorId and ClientIP, and keep the log entry with the latest timestamp
    | summarize SQLSecurityAuditEvents_TimeGenerated = arg_max(SQLSecurityAuditEvents_TimeGenerated, *) by IndicatorId, ClientIP
    // Select the desired output fields
    | project SQLSecurityAuditEvents_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore,
      TI_ipEntity, ResourceId, ClientIP, Action, Application, HostName, NetworkIP, NetworkDestinationIP, NetworkSourceIP, EmailSourceIpAddress, Type
    // Rename the timestamp field
    | extend timestamp = SQLSecurityAuditEvents_TimeGenerated  
requiredDataConnectors:
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligence
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligenceTaxii
- dataTypes:
  - AzureDiagnostics
  connectorId: AzureSql
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: MicrosoftDefenderThreatIntelligence
kind: Scheduled
{
  "$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/d0aa8969-1bbe-4da3-9e76-09e5f67c9d85')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/d0aa8969-1bbe-4da3-9e76-09e5f67c9d85')]",
      "properties": {
        "alertRuleTemplateName": "d0aa8969-1bbe-4da3-9e76-09e5f67c9d85",
        "customDetails": null,
        "description": "This query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in SQL Security Audit Events.\n",
        "displayName": "TI Map IP Entity to Azure SQL Security Audit Events",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "ClientIP",
                "identifier": "Address"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/IPEntity_AzureSQL.yaml",
        "query": "let dt_lookBack = 1h; // Look back 1 hour for AzureDiagnostics logs\nlet ioc_lookBack = 14d; // Look back 14 days for threat intelligence indicators\n// Fetch threat intelligence indicators related to IP addresses\nlet IP_Indicators = ThreatIntelligenceIndicator\n  | where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)\n  | where TimeGenerated >= ago(ioc_lookBack)\n  | extend TI_ipEntity = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)\n  | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)\n  | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(EmailSourceIpAddress), EmailSourceIpAddress, TI_ipEntity)\n  | where ipv4_is_private(TI_ipEntity) == false and  TI_ipEntity !startswith \"fe80\" and TI_ipEntity !startswith \"::\" and TI_ipEntity !startswith \"127.\"\n  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId\n  | where Active == true and ExpirationDateTime > now();\n// Perform a join between IP indicators and AzureDiagnostics logs for SQL Security Audit events\nIP_Indicators\n  // Use innerunique to keep performance fast and result set low, as we only need one match to indicate potential malicious activity that needs investigation\n  | join kind=innerunique (\n      AzureDiagnostics\n      | where TimeGenerated >= ago(dt_lookBack)\n      | where ResourceProvider == 'MICROSOFT.SQL'\n      | where Category == 'SQLSecurityAuditEvents'\n      | extend SQLSecurityAuditEvents_TimeGenerated = TimeGenerated\n      | extend ClientIP = column_ifexists(\"client_ip_s\", \"Not Available\")\n      | extend Action = column_ifexists(\"action_name_s\", \"Not Available\")\n      | extend Application = column_ifexists(\"application_name_s\", \"Not Available\")\n      | extend HostName = column_ifexists(\"host_name_s\", \"Not Available\")\n  )\n  on $left.TI_ipEntity == $right.ClientIP\n  // Filter out logs that occurred after the expiration of the corresponding indicator\n  | where SQLSecurityAuditEvents_TimeGenerated < ExpirationDateTime\n  // Group the results by IndicatorId and ClientIP, and keep the log entry with the latest timestamp\n  | summarize SQLSecurityAuditEvents_TimeGenerated = arg_max(SQLSecurityAuditEvents_TimeGenerated, *) by IndicatorId, ClientIP\n  // Select the desired output fields\n  | project SQLSecurityAuditEvents_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore,\n    TI_ipEntity, ResourceId, ClientIP, Action, Application, HostName, NetworkIP, NetworkDestinationIP, NetworkSourceIP, EmailSourceIpAddress, Type\n  // Rename the timestamp field\n  | extend timestamp = SQLSecurityAuditEvents_TimeGenerated\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P14D",
        "severity": "Medium",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Impact"
        ],
        "templateVersion": "1.3.1",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}