Collect Logs and Metrics for the Azure SQL App

Step 1: Collect diagnostic logs from Azure Monitor by streaming to EventHub

1. 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
2. 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.
   1. Login into Azure Portal.
   2. Click Azure SQL. Select the SQL database from which you want to collect logs.
   3. 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.
   4. Enter a name.
   5. Check the Stream to an event hub box and click Event hub / Configure.
   6. Select an Azure subscription.
   7. Select the Event Hubs namespace you created in Step 2 of Collect Logs from Azure Monitor. It should start with “SumoAzureLogsNamespace<UniqueSuffix>”. 
   8. Select insights-operational-logs from the Select event hub name dropdown.
   9. Select RootManageSharedAccessKey from Select event hub policy name dropdown.
   10. Click OK to exit event hub configuration.
   11. Check the box under Logs labeled “Audit”. 
   12. Click Save.
       

Step 2: 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.

1. 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
2. Push metrics from Azure Monitor to Event Hub.
   1. From the left pane, select ALL Services.
      
   2. Search for and select "Monitor".
   3. In the Monitor pane, select Diagnostic Settings under Settings.
      
   4. Select the resource for which you want to export metrics. If diagnostics is not enabled click Turn on Diagnostics Settings.
      

   5. Once diagnostics are enabled, click Add a diagnostic setting.
      

   6. The Diagnostic Settings page appears.
      

      1. 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.
      2. 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.
      3. Check the AllMetrics box.
      4. Click OK.
      5. Save the Diagnostics Setting.

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