Collect Logs and Metrics for the Azure SQL App
This page has instructions for collecting logs and metrics for the Azure SQL App, as well as a sample log message and a query sample.
Collect diagnostic logs from Azure Monitor by streaming to EventHub
In this step, you configure a pipeline for shipping logs from Azure Monitor to an Event Hub, on to an Azure Function, and finally to an HTTP Source on a Hosted Collector in Sumo Logic. Azure Monitor collects metrics and logs. The pipeline is described on Collect Logs from Azure Monitor.
- Perform Steps 1 and Step 2 of Collect Logs from Azure Monitor.
In Step 1, you create an HTTP Source. When you configure the, plan your source category to ease the querying process. A hierarchical approach allows you to make use of wildcards. For example:
Azure/DB/SQL/Logs
- Push logs from Azure Monitor to Event Hub. Various Azure Services connect to Azure Monitor to send monitoring data to an Event Hub. For more information, see Azure Monitor: Send monitoring data to an event hub and How do I set up Azure platform monitoring data to be streamed to an event hub? in Azure help.
- Login into Azure Portal.
- Click Azure SQL. Select the SQL database from which you want to collect logs.
- In the Monitoring Section, the Diagnostic Settings blade displays any existing settings. Click Edit Setting if you want to change your existing settings, or click Add diagnostic setting to add a new one. You can have a maximum of three settings.
- Enter a name.
- Check the Stream to an event hub box and click Event hub / Configure.
- Select an Azure subscription.
- Select the Event Hubs namespace you created in Step 2 of Collect Logs from Azure Monitor. It should start with “SumoAzureLogsNamespace<UniqueSuffix>”.
- Select insights-operational-logs from the Select event hub name dropdown.
- Select RootManageSharedAccessKey from Select event hub policy name dropdown.
- Click OK to exit event hub configuration.
- Check the box under Logs labeled “Audit”.
- Click Save.
Collect metrics from Azure Monitor by streaming to EventHub
In this step, you configure a pipeline for shipping metrics from Azure Monitor to an Event Hub, on to an Azure Function, and finally to an HTTP Source on a hosted collector in Sumo Logic. The pipeline is described on Collect Metrics from Azure Monitor.
- Perform Steps 1 and Step 2 of Collect Metrics from Azure Monitor.
In Step 1, you create an HTTP source. When you configure the, plan your source category to ease the querying process. A hierarchical approach allows you to make use of wildcards. For example:
Azure/DB/SQL/Metrics
- Push metrics from Azure Monitor to Event Hub.
- From the left pane, select ALL Services.
- Search for and select "Monitor".
- In the Monitor pane, select Diagnostic Settings under Settings.
- Select the resource for which you want to export metrics. If diagnostics is not enabled click Turn on Diagnostics Settings.
-
Once diagnostics are enabled, click Add a diagnostic setting.
-
The Diagnostic Settings page appears.
- In the left pane
- Enter a name for the diagnostic setting.
- Click the Stream to an event hub checkbox.
- Select Configure event hub. The right pane appears.
- In the right pane:
- Choose a Subscription.
- Select SumoMetricsNamespace<UniqueSuffix> as the event hub namespace.
- Select insights-metrics-pt1m as the event hub name.
- Select an event hub policy name. You can use the default policy RootManageSharedAccessKey.
- Check the AllMetrics box.
- Click OK.
- Save the Diagnostics Setting.
- In the left pane
- From the left pane, select ALL Services.
Sample log messages
ErrorEvent
{"LogicalServerName":"npande-test-db-server","SubscriptionId":"c088dc46-d123-12ad-a8b7-9a123d45ad6a","ResourceGroup":"npandeTestDBResGrp","time":"2018-07-09T05:08:32.679Z","resourceId":"/SUBSCRIPTIONS/c088dc46-d123-12ad-a8b7-9a123d45ad6a/RESOURCEGROUPS/NPANDETESTDBRESGRP/PROVIDERS/MICROSOFT.SQL/SERVERS/NPANDE-TEST-DB-SERVER/DATABASES/NPANDETESTDB2","category":"Errors","operationName":"ErrorEvent","properties":{"ElasticPoolName":"","DatabaseName":"npandeTestDB2","query_hash":"0","query_plan_hash":"0","message":"Invalid object name 'inventory123'.","error_number":208,"severity":16,"user_defined":false,"state":1}}
DatabaseWaitStatisticsEvent
{"LogicalServerName":"npande-test-db-server","SubscriptionId":"c088dc46-d123-12ad-a8b7-9a123d45ad6a"","ResourceGroup":"npandeTestDBResGrp","time":"2018-07-09T05:13:34.520Z","resourceId":"/SUBSCRIPTIONS/c088dc46-d123-12ad-a8b7-9a123d45ad6a"/RESOURCEGROUPS/NPANDETESTDBRESGRP/PROVIDERS/MICROSOFT.SQL/SERVERS/NPANDE-TEST-DB-SERVER/DATABASES/NPANDETESTDB","category":"DatabaseWaitStatistics","operationName":"DatabaseWaitStatistcsEvent","properties":{"ElasticPoolName":"","DatabaseName":"npandeTestDB","start_utc_date":"2018-07-09T05:13:34.520Z","end_utc_date":"2018-07-09T05:18:36.050Z","wait_type":"WRITELOG","delta_max_wait_time_ms":0,"delta_signal_wait_time_ms":0,"delta_wait_time_ms":12,"delta_waiting_tasks_count":2}}
Query Sample
Top 10 Errors
_sourceCategory=Azure/DB/SQL/Logs ErrorEvent "\"operationName\":\"ErrorEvent\""
| json "LogicalServerName", "SubscriptionId", "ResourceGroup", "resourceId", "category", "operationName", "properties" nodrop
| json field=properties "severity", "error_number", "DatabaseName", "message", "user_defined", "state"
| where operationName="ErrorEvent"
| count as eventCount by message
| top 10 message by eventCount, message asc