Are you looking for a powerful query language to analyze your Azure SQL Database logs? Look no further! In this article, we will explore how you can leverage Kusto Query Language (KQL) to query and analyze audit data in Azure SQL Database.

Introduction to Kusto Query Language (KQL)

Kusto Query Language (KQL) is a read-only query language designed for processing real-time data from various Azure services, including Azure Log Analytics, Azure Application Insights, and Azure Security Center logs. If you are familiar with Transact-SQL (T-SQL), you will find that KQL is quite similar, with some slight differences.

For example, in T-SQL, we use the WHERE clause to filter records from a table:

SELECT * 
FROM Employees 
WHERE firstname='John'

In KQL, we can write the same query using the following syntax:

Employees 
| where firstname == 'John'

As you can see, KQL uses a pipe (|) to pass values to the next command, similar to PowerShell.

Similarly, in T-SQL, we use the ORDER BY clause to sort data in ascending or descending order:

SELECT * 
FROM Employees 
WHERE firstname='John'
ORDER BY empid

The equivalent KQL code is as follows:

Employees 
| where firstname == 'John'
| order by empid

As you can see, the query syntax for KQL is quite familiar to SQL Server database professionals.

Enabling Audit for Azure SQL Database

Before we dive into using KQL for log analysis, let’s first enable audit logs for Azure SQL Database. By default, audit logs are stored in Azure Storage, but for easier data retrieval, it is recommended to store them in Azure Log Analytics.

To configure Azure SQL Database audit logs in Azure Log Analytics, follow these steps:

1. Login to the Azure portal using your credentials.
2. Navigate to your Azure SQL Server.
3. Enable server-level auditing and select Log Analytics (Preview) as the audit log destination.
4. Create a new Log Analytics workspace and configure the necessary settings.
5. Save the audit configurations for Azure SQL Database.

Once the audit logs are configured, you can proceed with using KQL for log analysis.

Using KQL for Log Analysis

To start analyzing your Azure SQL Database logs using KQL, follow these steps:

1. Navigate to your Azure SQL Database in the Azure portal.
2. Click on “Logs” to access the log analytics workspace.
3. Click on “Get Started” to open the query editor for KQL queries.

In the query editor, you will see the available tables, including the AzureDiagnostics table, which contains the log data for your database.

Summarizing Client IP Connections

Let’s say you want to identify the client IP addresses and the number of connections for your Azure SQL Database. You can use the following KQL query:

AzureDiagnostics
| summarize count() by client_ip_s

This query uses the “summarize” function to generate an output table with the aggregate count of connections for each client IP address.

Counting Login Failures by IP Address

If you want to determine the IP addresses from where login failures are originating, you can use the following KQL query:

AzureDiagnostics
| where Category == 'SQLSecurityAuditEvents' and LogicalServerName_s == "azuredemoinstance"
| where action_id_s == 'DBAF'
| summarize count() by client_ip_s, OperationName, server_principal_name_s

This query filters the records for the “SQLSecurityAuditEvents” category and the specified logical server name. It then summarizes the count of login failures for each client IP address, operation name, and server principal name.

Listing Events for Azure SQL Database

To list events for a specific Azure SQL Database, you can use the following KQL query:

AzureDiagnostics
| where DatabaseName_s == "labazuresql"

This query filters the records for the specified database name. By default, it retrieves the events from the last hour, but you can adjust the time range as needed.

Fetching SQL Statements

If you want to fetch SQL statements from the audit logs, you can use the KQL language to filter records from the “statement_s” table. For example, to retrieve INSERT statements, you can use the following query:

AzureDiagnostics
| where statement_s contains "insert"

This query retrieves the complete INSERT statements from the audit logs.

Using KQL, you can also monitor performance data such as CPU and memory usage by configuring the appropriate diagnostics settings.

Conclusion

Kusto Query Language (KQL) provides a powerful and familiar way to query and analyze Azure SQL Database logs. By leveraging KQL, you can easily retrieve and analyze audit data, identify client IP connections, count login failures, list events, and fetch SQL statements. With its similarities to T-SQL, SQL Server database professionals can quickly adapt to using KQL for log analysis in Azure SQL Database.

So, why not give KQL a try and unlock the full potential of your Azure SQL Database logs?

Article Last Updated: 2021-03-16