{"id":35838,"date":"2021-09-16T09:00:52","date_gmt":"2021-09-16T16:00:52","guid":{"rendered":""},"modified":"2022-02-24T14:03:29","modified_gmt":"2022-02-24T22:03:29","slug":"enhance-your-log-analytics-data-exploration-in-azure-data-studio","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2021\/09\/16\/enhance-your-log-analytics-data-exploration-in-azure-data-studio\/","title":{"rendered":"Enhance your Log Analytics data exploration in Azure Data Studio"},"content":{"rendered":"

The Azure Monitor Logs extension in Azure Data Studio<\/a>\u00a0is now available in preview. The extension is supported in Azure Data Studio August 2021 release, v1.32.0.<\/p>\n

Administrators can enable platform logging and metrics to one of their Azure services such as Azure SQL and set the destination to Log Analytics workspace. By installing native Azure Monitor Logs extension in Azure Data Studio, users can connect, browse, and query against Log Analytics workspace. Data professionals who are using Azure SQL, Azure PostgreSQL, or Azure Data Explorer in Azure Data Studio can access the data in the Log Analytics workspace for diagnosis or auditing in that same development environment. This native Azure Monitor Logs extension allows Azure service customers to also author notebooks with Log Analytics kernel, all equipped with Intellisense.<\/p>\n

Examples for Azure SQL users<\/h2>\n

While the examples in this section are specific to Azure SQL scenarios, Azure Monitor Logs can also capture events from other Azure resources, such as Azure Functions or Azure Web Apps. Now, let\u2019s dive into these examples where users can leverage Log Analytics workspace data to better understand an Azure SQL database.<\/p>\n

Example One: Who dropped a table in my database?<\/h3>\n

Connect to your Log Analytics workspace that captures the Azure SQL audit events. Right-click and choose New Query from the menu. Copy the following query and paste it to Query editor in Azure Data Studio.<\/p>\n

AzureDiagnostics\n| where action_name_s == \"BATCH COMPLETED\"\n| where statement_s contains \"DROP TABLE\"\n| project TimeGenerated, Category, OperationName, server_principal_name_s, statement_s\n| sort by TimeGenerated desc\u00a0\n| take 10<\/pre>\n
\"exploring<\/p>\n
In this example, a good question to ask might be, why are these users executing a lot of drops during this time?<\/p>\n
Example Two: What type of errors happen?<\/h3>\n
Connect to your Log Analytics workspace that captures the Azure SQL error events. Right-click and choose New Notebook from the menu. Copy the following query and paste it to Query editor in Azure Data Studio.<\/p>\n
AzureDiagnostics\n|\u00a0where\u00a0OperationName\u00a0==\u00a0\"ErrorEvent\"\n|\u00a0extend\u00a0ErrorNumber\u00a0=\u00a0\u00a0toint(error_number_d)\u00a0\n|\u00a0summarize\u00a0event_count=count()\u00a0by\u00a0EventTime\u00a0=\u00a0bin(TimeGenerated,\u00a02d),\u00a0\u00a0ErrorNumber\n|\u00a0evaluate\u00a0pivot(ErrorNumber,\u00a0sum(event_count))\n|\u00a0sort\u00a0by\u00a0EventTime\u00a0asc<\/pre>\n
\"tbd\"<\/p>\n
In this example, we see a timeline with two-day intervals that maps error count by error number. A good question would be to understand why error 208 happens a lot.<\/p>\n
Overall benefits<\/h2>\n
Here are four key benefits of using Azure Monitor Logs extension in Azure Data Studio.<\/p>\n
    \n
  1. Efficiency in data exploration and data analysis. Users now have access to their SQL data sources in the same place as their Log Analytics workspaces. Users can also use Sand Dance extension to further enhance their data exploration and analysis.<\/li>\n
  2. Reproducible analysis and diagnosis with notebooks.<\/li>\n
  3. Improved DevOps troubleshooting experience with Azure Monitor Logs notebooks. This is illustrated in the previous examples.<\/li>\n
  4. Version control the queries and notebooks directly with git in Azure Data Studio. Users can also add these files as part of their CI\/CD pipelines in GitHub or Azure DevOps.<\/li>\n<\/ol>\n
    Other related extensions: Kusto (KQL) extension which works for Azure Data Explorer<\/a><\/p>\n
    How to get started<\/h2>\n