AZURE SQL DB AND LOG ANALYTICS BETTER TOGETHER – PART #1
Published Aug 08 2019 02:44 AM 23.4K Views
Microsoft

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

clipboard_image_11.png

 

Check also other parts

1 – FIRST CREATE AN LOG ANALYTICS WORKSPACE

Click on Create a Resource and search for Log Analytics

clipboard_image_12.png

clipboard_image_13.png

 

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

clipboard_image_14.png

 

3 – ENABLE DIAGNOSTICS TO LOG ANALYTICS

*This configuration is done PER DATABASE

Click on Diagnostics Settings and then Turn On Diagnostics

clipboard_image_15.png



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

clipboard_image_16.png



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

clipboard_image_17.png

 

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

clipboard_image_18.png

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-e...

 

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

clipboard_image_19.png



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

 

 

 

 

 

 

clipboard_image_20.png

 

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

clipboard_image_21.png

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

More information can be found

 

Check also Part 2 and 3

 

Republishing original post published at https://fonsecasergio.wordpress.com/2019/03/30/azure-sql-db-and-log-analytics-part-1/

 

1 Comment
Version history
Last update:
‎Jul 01 2020 07:34 AM
Updated by: