Azure SQL DB and Log Analytics better together – Part #1


As a DBA you may want to query SQL Audit and SQL Diagnostics information. The easiest way to do this is sending to Log analytics that is part of Azure Monitor

You can also send this data to Event Hubs and storage accounts. On this post I will focus on Log Analytics

1 – First create an Log Analytics workspace

Click on Create a Resource and search for Log Analytics

2 – Enable Audit to Log Analytics

At Server level or at Database level, enable auditing and send log to Log Analytics and select the workspace you just created

3 – Enable Diagnostics to Log Analytics

*This configuration is done PER DATABASE

Click on Diagnostics Settings and then Turn On Diagnostics


Select to Send to Log Analytics and select the Log Analytics workspace. For this sample I will selected only Errors


4 – Quering Log Analytics

Just go to the Log Analytics workspace and query the database using a language called Kusto. More information and sample on sintax can be found on this link

A Kusto query is a read-only request to process data and return results. The request is stated in plain text, using a data-flow model designed to make the syntax easy to read, author, and automate. The query uses schema entities that are organized in a hierarchy similar to SQL’s: databases, tables, and columns.

The data we will work in this sample can be found on table AzureDiagnostics

Just write a query and click Run

4.1 Failed Logins sample

For this sample use query below to find failed logins

//Failed Logins
AzureDiagnostics
| where TimeGenerated >= ago(5d) //Events on last 5 days
| where action_name_s == "DATABASE AUTHENTICATION FAILED" 
| extend additional_information_xml=parse_xml(additional_information_s)
| extend Error_Code=additional_information_xml.login_information.error_code
| extend Error_State=additional_information_xml.login_information.error_state
| project 
    TimeGenerated, event_time_t,
    ResourceGroup, LogicalServerName_s, database_name_s,
    session_id_d, client_ip_s, application_name_s, session_server_principal_name_s,
    Error_Code, Error_State
| order by TimeGenerated desc 

Expanding this event we can see all information from this event

  • Date/Time
  • Server
  • DB
  • Application Name
  • User
  • IP
  • Error Code and Error State
  • etc

You can then use this Error Code and State to find what is the error description

In this case:

– Error 18456: Login failed for user ‘%.ls’.%.ls
– State 8: The password is incorrect

REF: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

4.2 Errors generic

Use the query below to find all errors or specific errors

//Errors Query
AzureDiagnostics
| where TimeGenerated >= ago(30d) // Last 30 days
| where Category =~ "Errors"
| where error_number_d == 8134 //Divide by zero error encountered.
//| where Message contains "divide" //Or search by text
| project 
    TimeGenerated,
    ResourceGroup,
    LogicalServerName_s,
    DatabaseName_s,
    Message,
    error_number_d, 
    Severity,
    state_d 
| order by TimeGenerated desc

You can find some information related to the errors. Can be useful to consolidate information, histogram, alerts, etc

*Notice that there is no information on who caused the error. Because this information came from Diagnostics information, not the audit


4.3 Errors detailed

With this other sample below you can get the queries that failed with full information on who did it, what query, etc.

//Detailed errors
AzureDiagnostics
| where TimeGenerated >= ago(15d)  //Last 15 days
| where Category =~ "SQLSecurityAuditEvents"
| where succeeded_s == "false"
| where additional_information_s contains "8134" //Looking for specift error. Ex Div by zero
| extend additional_information_xml=parse_xml(additional_information_s)
| extend failure_reason=additional_information_xml.batch_information.failure_reason
| project     
    ResourceGroup,
    LogicalServerName_s,
    database_name_s,
    session_id_d,
    action_name_s,
    client_ip_s,
    application_name_s,
    failure_reason,
    statement_s,
    additional_information_s

5 – You can also monitor using Azure SQL Analytics (Preview)

https://docs.microsoft.com/en-us/azure/azure-monitor/insights/azure-sql

I will not speak about it on this article


There will be more articles on this topic Azure SQL DB + Log Analytics

More information can be found

Advertisements

One thought on “Azure SQL DB and Log Analytics better together – Part #1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s