{"id":91912,"date":"2021-07-26T17:00:26","date_gmt":"2021-07-26T17:00:26","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=91912"},"modified":"2021-07-20T19:01:00","modified_gmt":"2021-07-20T19:01:00","slug":"azure-data-studio-kql-extension","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/azure-data-studio-kql-extension\/","title":{"rendered":"Log Analytics and Azure Data Studio: New Extension"},"content":{"rendered":"

You need to dig into old blogs I wrote before to fully understand how interesting this new extension is.<\/p>\n

On Saving Money with Log Analytics<\/a>\u00a0I mentioned how important log analytics is for azure clouds. This deserves more in depth analysis, which probably will be broken down in blog articles.<\/p>\n

\nOn Connecting to Log Analytics using Azure Data Studio and KQL<\/a>\u00a0 I introduced a solution to make Log Analytic queries in Python notebooks using Azure Data Studio<\/strong>. Comparing to the new feature I’m about to show this will seems like something from stone age.<\/p>\n

On Using Power BI to Query Log Analytics<\/a>\u00a0 I made demonstrations with Power BI<\/strong>, bringing log analysis to another level.<\/p>\n

The news:<\/strong> A new extension is in preview to allow connecting Azure Data Studio<\/strong> to Log Analytics<\/strong> as if you were connecting to a SQL<\/strong> database.<\/p>\n

The extension is in preview, so you need to install it on Azure Data Studio Insiders version, which brings preview features in advance for us. If you are lost in space and don’t have the Insiders version installed yet, you can get it here<\/a>\u00a0<\/p>\n

Once you installed Azure Data Studio Insiders<\/strong>, let’s follow the steps you need to start querying Azure Log Analytics<\/strong>.<\/p>\n

Install the Extension<\/h2>\n

This is easy and with no mystery: Use the extensions tab, look for Azure Monitor Log<\/strong> extension and install it.<\/p>\n

\"\"<\/p>\n

Create a Connection<\/h2>\n

The regular Create Connection<\/strong> window has one additional option, Azure Monitor Logs<\/strong>. We will need to fill some specific log analytics details:<\/p>\n

Connection Type:<\/strong> Azure Monitor Logs
\nWorkspace Id:<\/strong> You can find this information on your Azure portal, on your Log Analytics workspace.<\/p>\n

\"\"\u00a0\"\"<\/p>\n

\nAuthentication:<\/strong> You will need to authenticate against the Active Directory where your Log Analytics is. You will need to provide an account and Azure AD Tenant and the authentication will be made using this account
\nDatabase:<\/strong>\u00a0There is only one option. It will only be filled after you provide the authentication
\nServer Group:<\/strong> This is an ADS detail about how you organize your connections. You can create a group for all your log analytics connections.
\nName<\/strong>: the name of this connection on the UI<\/p>\n

 <\/p>\n

\"\"<\/p>\n

\nLook Around<\/h2>\n

Take a look around and check the result of your connection.<\/p>\n

    \n
  • In the Connections<\/strong> panel you are able to navigate the tables inside log analytics down to the level of fields. Only a few tables are shared across many services, while others are specific to one azure service.<\/li>\n<\/ul>\n

    \"\"<\/p>\n

      \n
    • Right click the log analytics connection in the Connections<\/strong> panel and select Manage<\/strong>. The tab that will open will show you the tables once again, but here you have the option to create a new query or a new notebook.<\/li>\n<\/ul>\n

      \"\"<\/p>\n

      \"\"<\/p>\n

      \nCreating a new Notebook<\/h2>\n

      Once you created a new Notebook, it will be connected to your Log Analytics<\/strong>. The kernel will be Log Analytics<\/strong> and not Kusto<\/strong>. Yes, I know Kusto<\/strong> is the Log Analytics<\/strong> language, but Azure Data Explorer<\/strong> already uses\u00a0Kusto<\/strong>\u00a0as Kernel. <\/b>The kernel needs to be Log Analytics<\/strong>.<\/p>\n

      \"\"<\/p>\n

      The notebook provides intellisense while you type your query, but doesn’t provide coloring options for the statements yet, the entire query is black, a boring black.<\/p>\n

      \"\"<\/p>\n

       <\/p>\n

      As we would expect from an ADS<\/strong> notebook, we can create graphics from the query results. For example, this is a graphic of weekly access on a website, by day of week:<\/p>\n

      \"\"<\/p>\n

       <\/p>\n

      The graphics in a notebook are not the same as a Log Analytics<\/strong> workbook. In order to achieve this line graphic I had to include a PIVOT<\/strong> function on the query, otherwise the graphic would not show this data, while on the workbook we could configure for this data.<\/p>\n

      Without the PIVOT<\/strong> function, this would be the result of the query:<\/p>\n

      \"\"<\/p>\n

      \u00a0<\/p>\n

      Using the EVALUATE<\/strong> statement and the PIVOT<\/strong> function, we turn the content of the column Day into columns, allowing us to create the line graphic above:<\/p>\n

      \u00a0<\/p>\n

      \"\"<\/p>\n

      \nSmall Pending Problems<\/h2>\n
        \n
      • The Azure Connection is lost from time to time. We need to refresh the connection. On the notebook, we can use the Change Connection<\/strong> for that. On the Connections panel, we can disconnect and connect again.<\/li>\n<\/ul>\n

        \"\"<\/p>\n

          \n
        • The code doesn’t use any different color for the statements<\/li>\n
        • The graphic features on a notebook may be more difficult to handle than on log analytics workbook. However, I don’t think this is really an issue, it’s more about different usages of the tools.<\/li>\n<\/ul>\n

          Conclusion<\/h2>\n

          Each day we get new tools to access Log Analytics, increasing the importance of this great tool for Azure Clouds.<\/p>\n","protected":false},"excerpt":{"rendered":"

          You need to dig into old blogs I wrote before to fully understand how interesting this new extension is. On Saving Money with Log Analytics\u00a0I mentioned how important log analytics is for azure clouds. This deserves more in depth analysis, which probably will be broken down in blog articles. On Connecting to Log Analytics using……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[145424,126250,126249,143562],"coauthors":[6810],"class_list":["post-91912","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-azure-data-studio","tag-kql","tag-kusto","tag-log-analytics"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/91912","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=91912"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/91912\/revisions"}],"predecessor-version":[{"id":92021,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/91912\/revisions\/92021"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=91912"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=91912"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=91912"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=91912"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}