Azure SQL Managed Instance - How to find SQL Error and Error Severity > 16 in log analytics workspace

Question

We are not able to find SQL Error and Errors with Severity > 16 in log analytics workspace.

We have enabled SQLSecurityAuditEvents and Resource usage Stats.However we are not able to find any error when we simulate the errors

Answer 1

Hello Nitya V,

I understood that you want to query SQL errors on log analytics workspace. In that case I suggest first to check the diagnostic settings configuration on the Azure resources level, Be sure that is sending the logs to the proper logs analytics workspace, here some link to check:

• https://learn.microsoft.com/en-us/azure/azure-monitor/essentials/create-diagnostic-settings?tabs=portal
• https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/monitoring-sql-managed-instance-azure-monitor?view=azuresql
• https://learn.microsoft.com/en-us/azure/azure-sql/database/metrics-diagnostic-telemetry-logging-streaming-export-configure?view=azuresql&tabs=azure-portal

After verify that the logs in the logs analytics workspace you can try with this KQL example to query the error with the criterial required:

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/monitoring-sql-managed-instance-azure-monitor-reference?view=azuresql#supported-resource-logs-for-microsoftsqlmanagedinstances

AzureDiagnostics
| where ResourceType == "SQLManagedInstance"
| where Category == "SQLSecurityAuditEvents"
| where action_name_s contains "error" or severity_s > 16 

If this information help you to address your question, please Accept the answer.

Luis