Kusto Query Language (KQL) is used to write queries in Azure Data Explorer, Azure Monitor Log Analytics, Azure Sentinel, and more.

Here’s a comprehensive guide on using KQL (Kusto Query Language) to analyze SQL query audit logs in Azure Log Analytics, covering different approaches and examples:

Prerequisites

  • Enable Auditing: Ensure SQL Auditing is enabled for your Azure SQL databases, sending logs to a Log Analytics workspace.
  • Access Log Analytics: Navigate to the Log Analytics workspace where the audit logs are stored.

Key Tables

  • AzureDiagnostics: Base table containing various logs, including SQL audit events.
  • SQLSecurityAuditEvents: Dedicated table with structured audit data for easier querying.

Ways to Query SQL Audit Logs

  1. Direct Querying of AzureDiagnostics Table:
AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| project TimeGenerated, DatabaseName, LoginName, Statement
| order by TimeGenerated desc

2. Querying SQLSecurityAuditEvents Table (Recommended):

SQLSecurityAuditEvents
| summarize count() by ActionName
| order by count_ desc


Here’s a comprehensive guide on using KQL (Kusto Query Language) to analyze SQL query audit logs in Azure Log Analytics, covering different approaches and examples:

Prerequisites:

  • Enable Auditing: Ensure SQL Auditing is enabled for your Azure SQL databases, sending logs to a Log Analytics workspace.
  • Access Log Analytics: Navigate to the Log Analytics workspace where the audit logs are stored.

Key Tables:

  • AzureDiagnostics: Base table containing various logs, including SQL audit events.
  • SQLSecurityAuditEvents: Dedicated table with structured audit data for easier querying.

Ways to Query SQL Audit Logs:

  1. Direct Querying of AzureDiagnostics Table:Code snippetAzureDiagnostics | where Category == "SQLSecurityAuditEvents" | project TimeGenerated, DatabaseName, LoginName, Statement | order by TimeGenerated desc Use code with caution. Learn morecontent_copy
  2. Querying SQLSecurityAuditEvents Table (Recommended):Code snippetSQLSecurityAuditEvents | summarize count() by ActionName | order by count_ desc

Example Queries:

  • List recent queries for a specific database:
SQLSecurityAuditEvents
| where DatabaseName == "MyDatabase"
| where ActionName == "BATCH_COMPLETED_GROUP"
| project TimeGenerated, DatabaseName, LoginName, Statement
| order by TimeGenerated desc
  • Find queries with long execution times:
SQLSecurityAuditEvents
| where ActionName == "BATCH_COMPLETED_GROUP"
| where DurationMs > 10000
| project TimeGenerated, DatabaseName, LoginName, Statement, DurationMs
| order by DurationMs desc
  • Identify failed login attempts:
SQLSecurityAuditEvents
| where ActionName == "LOGIN_FAILED"
| project TimeGenerated, LoginName, ClientIP


Here’s a comprehensive guide on using KQL (Kusto Query Language) to analyze SQL query audit logs in Azure Log Analytics, covering different approaches and examples:

Prerequisites:

  • Enable Auditing: Ensure SQL Auditing is enabled for your Azure SQL databases, sending logs to a Log Analytics workspace.
  • Access Log Analytics: Navigate to the Log Analytics workspace where the audit logs are stored.

Key Tables:

  • AzureDiagnostics: Base table containing various logs, including SQL audit events.
  • SQLSecurityAuditEvents: Dedicated table with structured audit data for easier querying.

Ways to Query SQL Audit Logs:

  1. Direct Querying of AzureDiagnostics Table:Code snippetAzureDiagnostics | where Category == "SQLSecurityAuditEvents" | project TimeGenerated, DatabaseName, LoginName, Statement | order by TimeGenerated desc Use code with caution. Learn morecontent_copy
  2. Querying SQLSecurityAuditEvents Table (Recommended):Code snippetSQLSecurityAuditEvents | summarize count() by ActionName | order by count_ desc Use code with caution. Learn morecontent_copy

Example Queries:

  • List recent queries for a specific database:Code snippetSQLSecurityAuditEvents | where DatabaseName == "MyDatabase" | where ActionName == "BATCH_COMPLETED_GROUP" | project TimeGenerated, DatabaseName, LoginName, Statement | order by TimeGenerated desc Use code with caution. Learn morecontent_copy
  • Find queries with long execution times:Code snippetSQLSecurityAuditEvents | where ActionName == "BATCH_COMPLETED_GROUP" | where DurationMs > 10000 | project TimeGenerated, DatabaseName, LoginName, Statement, DurationMs | order by DurationMs desc Use code with caution. Learn morecontent_copy
  • Identify failed login attempts:Code snippetSQLSecurityAuditEvents | where ActionName == "LOGIN_FAILED" | project TimeGenerated, LoginName, ClientIP Use code with caution. Learn morecontent_copy

Additional Tips:

  • Filter by time range: Use where TimeGenerated >= ago(1d) to filter for events in the last day.
  • Search for specific text: Use where Statement contains "SELECT *" to find queries containing a specific string.
  • Join with other tables: Enrich audit data by joining with other tables in Log Analytics (e.g., AzureActivityLog).
  • Visualize results: Use Log Analytics charts and dashboards to create visual representations of audit data.
  • Create alerts: Set up alerts to notify you of critical events or unusual activity.

SQLSecurityAuditEvents


SQLSecurityAuditEvents is a dedicated table within Azure Log Analytics that stores structured audit data related to security events and activities within Azure SQL databases.
It’s specifically designed to facilitate easy analysis and monitoring of security-related actions, providing valuable insights for compliance, threat detection, and overall database security.

Key Features:

  • Organized Structure: Stores audit events in a structured format, making it easy to query and analyze using KQL (Kusto Query Language).
  • Detailed Information: Captures a rich set of details for each security event, including:
    • Timestamp
    • Database name
    • Login name
    • Statement executed
    • Success or failure
    • Client IP address
    • Duration
    • Action name (e.g., LOGIN_FAILED, BATCH_COMPLETED_GROUP, BACKUP_RESTORE_GROUP)
  • Centralized Logging: Integrates with Azure Log Analytics, enabling centralized storage and management of audit logs across multiple databases and servers.
  • Compatibility: Works with both Azure SQL Database and Azure Synapse Analytics.

Benefits:

  • Enhanced Security Insights: Enables granular monitoring of database activities for security and compliance purposes.
  • Threat Detection: Helps identify potential security threats like failed login attempts, unauthorized access, or suspicious queries.
  • Compliance Support: Assists in meeting regulatory compliance requirements that mandate database auditing.
  • Troubleshooting: Facilitates investigation of database issues and performance problems.
  • Integration with Azure Security Services: Can be integrated with other Azure security tools for comprehensive threat protection and analysis.

Tips on using AdditionalFields column

  • You will need to typecast data prior to performing further operations on it. For example, if a column exists called Perf1Sec_i as well as a property in AdditionalFields called Perf2Sec, and you want to calculate total perf by adding both values, use something like: AzureDiagnostics | extend TotalPerfSec = Perf1Sec_i + toint(AdditionalFields.Perf2Sec) | .....
  • Use Where clauses to reduce the data volume to the smallest possible prior to writing any complex logic to significantly improve performance. TimeGenerated is one column that should always be reduced to the smallest possible window. In the case of AzureDiagnostics, an additional filter should also always be included at the top of the query around the resource types that are being queried using the ResourceType column.
  • When querying very large volumes of data, it is sometimes more efficient to do a filter on AdditionalFields as a whole rather than parsing it. For example, for large volumes of data AzureDiagnostics | where AdditionalFields has "Perf2Sec" is often more efficient than AzureDiagnostics | where isnotnull(toint(AdditionalFields.Perf2Sec)).

Azure Diagnostics mode

The following services use Azure diagnostics mode for their resource logs and send data to the Azure Diagnostics table.

  • Analysis Services
  • Application Gateways
  • Automation Accounts
  • Azure Database for MariaDB servers
  • Azure Database for MySQL servers
  • Azure Database for PostgreSQL servers
  • Azure Database for PostgreSQL servers v2
  • Batch accounts
  • CDN profiles
  • Cognitive Services
  • Data Lake Analytics
  • DataLake Storage Gen1
  • Device Provisioning Services
  • Digital Twins
  • Event Grid Topics
  • Event Hubs
  • ExpressRoute circuits
  • Front Doors
  • Integration accounts
  • Key Vault
  • Kubernetes services
  • Load balancers
  • Logic Apps
  • Media services
  • Network interfaces
  • Network Security Groups
  • P2S VPN Gateways
  • Power BI Embedded
  • Public IP addresses
  • Recovery Services vaults(Site Recovery)
  • Search services
  • Service Bus
  • SQL databases
  • SQL managed Instances
  • SQL servers
  • Stream Analytics jobs
  • Traffic Manager profiles
  • Virtual networks
  • Virtual network gateways
  • VPN Gateways


Azure Diagnostics mode or resource-specific mode

Most Azure resources write data to the workspace in either Azure diagnostics or resource-specific mode without giving you a choice.

All Azure services will eventually use the resource-specific mode. As part of this transition, some resources allow you to select a mode in the diagnostic setting. Specify resource-specific mode for any new diagnostic settings because this mode makes the data easier to manage. 

Note :The following services use either Azure diagnostics mode or resource-specific mode for their resource logs depending on their configuration. When they use resource-specific mode, they do not send data to the AzureDiagnostics table.

  • API Management Services
  • Azure Cosmos DB
  • Data factories (V2)
  • IoT Hub
  • Recovery Services vaults(Backup)
  • Firewalls

Categories

  • Azure Resources
  • Security
  • Network


Solutions

  • LogManagement

Resource types

  • Application Gateways
  • CDN Profiles
  • Azure Cosmos DB
  • Event Grid Topics
  • Event Hubs
  • Firewalls
  • Key Vaults
  • Kubernetes Services
  • Recovery Services Vaults
  • Service Bus
  • Azure Database for MySQL Flexible Servers
  • Azure Database for PostgreSQL Flexible Servers
  • Media Services
  • Analysis Services
  • Batch Accounts
  • Cognitive Services
  • Event Grid Partner Namespaces
  • Event Grid Partner Topics
  • Event Grid System Topics
  • Azure Arc Enabled Kubernetes
  • Azure Arc Provisioned Clusters
  • IoT Hub
  • Logic Apps
  • API Management services
  • Automation account
  • Data factories
  • Data Lake Storage Gen1
  • Data Lake Analytics
  • Power BI Embedded
  • SQL Managed Instances
  • SQL Servers
  • SQL Databases
  • Azure Database for MySQL Servers
  • Azure Database for PostgreSQL Servers
  • Azure Database for PostgreSQL Servers V2
  • Azure Database for MariaDB Servers
  • Device Provisioning Services
  • ExpressRoute Circuits
  • Front Doors
  • Network Interfaces
  • Network Security Groups
  • Public IP Addresses
  • Traffic Manager Profiles
  • Virtual Network Gateways
  • Virtual Private Network Gateways
  • Virtual Networks
  • Search Services
  • Stream Analytics jobs

Columns

ColumnTypeDescription
action_id_sString
action_name_sString
action_sString
ActivityId_gGuid
AdditionalFields
AdHocOrScheduledJob_sString
application_name_sString
audit_schema_version_dDouble
avg_cpu_percent_sString
avg_mean_time_sString
backendHostname_sString
Caller_sString
callerId_sString
CallerIPAddressString
calls_sString
CategoryString
client_ip_sString
clientInfo_sString
clientIP_sString
clientIp_sString
clientIpAddress_sString
clientPort_dDouble
code_sString
collectionName_sString
conditions_destinationIP_sString
conditions_destinationPortRange_sString
conditions_None_sString
conditions_protocols_sString
conditions_sourceIP_sString
conditions_sourcePortRange_sString
CorrelationIdString
count_executions_dDouble
cpu_time_dDouble
database_name_sString
database_principal_name_sString
DatabaseName_sString
db_id_sString
direction_sString
dop_dDouble
duration_dDouble
duration_milliseconds_dDouble
DurationMsBigInt
ElasticPoolName_sString
endTime_tDateTime
Environment_sString
error_code_sString
error_message_sString
errorLevel_sString
event_class_sString
event_sString
event_subclass_sString
event_time_tDateTime
EventName_sString
execution_type_dDouble
executionInfo_endTime_tDateTime
executionInfo_exitCode_dDouble
executionInfo_startTime_tDateTime
host_sString
httpMethod_sString
httpStatus_dDouble
httpStatusCode_dDouble
httpStatusCode_sString
httpVersion_sString
id_sString
identity_claim_appid_gGuid
identity_claim_ipaddr_sString
instanceId_sString
interval_end_time_dDouble
interval_start_time_dDouble
ip_sString
is_column_permission_sString
isAccessPolicyMatch_bBool
JobDurationInSecs_sString
JobFailureCode_sString
JobId_gGuid
jobId_sString
JobOperation_sString
JobOperationSubType_sString
JobStartDateTime_sString
JobStatus_sString
JobUniqueId_gGuid
LevelString
log_bytes_used_dDouble
logical_io_reads_dDouble
logical_io_writes_dDouble
LogicalServerName_sString
macAddress_sString
matchedConnections_dDouble
max_cpu_time_dDouble
max_dop_dDouble
max_duration_dDouble
max_log_bytes_used_dDouble
max_logical_io_reads_dDouble
max_logical_io_writes_dDouble
max_num_physical_io_reads_dDouble
max_physical_io_reads_dDouble
max_query_max_used_memory_dDouble
max_rowcount_dDouble
max_time_sString
mean_time_sString
MessageString
min_time_sString
msg_sString
num_physical_io_reads_dDouble
object_id_dDouble
object_name_sString
OperationNameString
OperationVersionString
partitionKey_sString
physical_io_reads_dDouble
plan_id_dDouble
policy_sString
policyMode_sString
primaryIPv4Address_sString
priority_dDouble
properties_enabledForDeployment_bBool
properties_enabledForDiskEncryption_bBool
properties_enabledForTemplateDeployment_bBool
properties_sString
properties_sku_Family_sString
properties_sku_Name_sString
properties_tenantId_gGuid
query_hash_sString
query_id_dDouble
query_max_used_memory_dDouble
query_plan_hash_sString
query_time_dDouble
querytext_sString
receivedBytes_dDouble
Region_sString
requestCharge_sString
requestQuery_sString
requestResourceId_sString
requestResourceType_sString
requestUri_sString
reserved_storage_mb_sString
ResourceString
resource_actionName_sString
resource_location_sString
resource_originRunId_sString
resource_resourceGroupName_sString
resource_runId_sString
resource_subscriptionId_gGuid
resource_triggerName_sString
resource_workflowId_gGuid
resource_workflowName_sString
ResourceGroupString
_ResourceIdStringA unique identifier for the resource that the record is associated with
ResourceProviderString
ResourceProviderString
ResourceTypeString
ResourceTypeString
response_rows_dDouble
resultCode_sString
ResultDescriptionString
ResultDescriptionString
resultDescription_ChildJobs_sString
resultDescription_ErrorJobs_sString
resultMessage_sString
ResultSignatureString
ResultTypeString
ResultTypeString
rootCauseAnalysis_sString
routingRuleName_sString
rowcount_dDouble
ruleName_sString
RunbookName_sString
RunOn_sString
schema_name_sString
sentBytes_dDouble
sequence_group_id_gGuid
sequence_number_dDouble
server_principal_sid_sString
session_id_dDouble