CODEWITHSUNDEEP
sqlazure-log-analytics
Lee Alderdice
Lee Alderdice

Reputation: 33

Get SQL Statement from Category Error in Azure Log Analytics

I am new to Log Analytics and while I can find that there are SQL errors happening and can count them, group them etc. I can't see in the documentation how to get at the SQL statement causing them.

Currently I have the below and was expecting to extend and project but the columns *_s are all empty.

AzureDiagnostics 
| where Category == "Errors" and error_number_d  == 241

What would I need to do from to get at the troublesome SQL?

Many thanks all.

Upvotes: 0

Views: 770

Answers (1)

krishg
krishg

Reputation: 6508

You need to enable diagnostics for that. Refer AZURE SQL DB AND LOG ANALYTICS BETTER TOGETHER for details. SQL statements can be found in statement_s column. For example:

//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

You can also add Azure SQL Analytics (Preview) to your Log Analytics workspace for that. Once you configure, the monitoring dashboard is shown. Screenshot that shows the monitoring dashboard.

Selecting any of the tiles, opens a drill-down report into the specific perspective. Once the perspective is selected, the drill-down report is opened.

Upvotes: 1

Related Questions