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