Azure Data Explorer (ADX) – measure twice, cut once

ADX and KQL – Azure Monitor Logs in Azure Data Studio

G’day,

I recently wrote about the Kusto Query Language Extension that’s available as an plugin for Azure Data Studio along with KQL Magic.

The main reason that people have been using KQL is usually as part of Azure Monitor Logs. And did you know that Azure Data Studio has another Extension available specifically for Azure Monitor Logs. This allows you to connect to a Log Analytics Workspace.

Both extensions are currently in preview at the time of writing, however, having used both, they seem fairly stable to me.

So, after a few seconds (probably less) the Azure Monitor Logs extension was fully installed and ready to go.

I confirmed this by creating a brand new note book and seeing that the ‘Log Analytics’ kernel was available for use.

The Log Analytics kernel in Azure Data Studio

Now we need to connect to our Log Analytics Workspace.

So, let’s set up a new connection in Azure Data Studio. You’ll now have a new connection type – “Azure Monitor Logs”

Set up a connection to a Log Analytics Workspace

If you’re struggling to find the Workspace ID of your Log Analytics resource, just head to the overview page of the resource in the Azure Portal

Create a new Notebook.

Now we can simply pick the connection in the dropdown and start writing some Kusto Query Language (KQL)

Select the Kernel (LogAnalytics) and the connection (I called my connection “Log Analytics”)

**Select the Kernal and the connection in Azure Data Studio**

Create a new code cell and enter some Kusto, here’s a starter.

// Error and exception count 
// Show a column chart of the number of the logs containing warnings or errors in the last hour, per application. 
FunctionAppLogs 
| where TimeGenerated > ago(24h)
| where Level == "Warning" or Level == "Error"
| summarize count_per_app = count() by _ResourceId
| sort by count_per_app desc 
| render columnchart

So, that’s the Azure Monitor Extension in Azure Data Explorer.

Have a great day

Cheers

Marty

KQL and ADX – Is this really like SQL?

Hopefully, you’re aware that Azure Data Explorer can be accessed by anybody with a valid Microsoft Account Identity or MSA for short. If you need a one, then hop along to Outlook.com and pick up a free MSA.

After that head along to dataexplorer.azure.com and login with your MSA. Then, you’ll find that Azure Data Explorer is attached to a cluster that you did not create. This will be the ‘help’ cluster that Microsoft uses in order to get folks up-to-speed on Azure Data Explorer and KQL.

Your screen (browser) should look something like this.

You’ll notice that you’re attached to the ‘help’ cluster. Which has 4 databases

ContosoSales
SampleLogs
SampleMetrics
Samples

And that an initial script is in the main window.

Notice too that right at the top of the main window (the one that contains the script) you can see the name of the ADX cluster and database that you are attached to.

ADX cluster and database name

The script that’s open in the main window is designed to run on the “@help/Samples” cluster / database.

Make sure you’re attached to that and highlight the first query (if you’re not on the correct database then you’ll just get an error)

This is Kusto Query Language or KQL

This is basically the equivilent of a T-SQL query of the form

SELECT TOP(10) * FROM from table

You’ll also notice that the next query in the script looks something like this

A KQL query

And if you were to translate this into T-SQL, you’re basically asking for a computed / derived column and specifying a list of columns that you’d like back.

If you’ve worked with T-SQL for sometime, you’ll probably see some similarities but a lot of differences.

ADX and KQL have been built from the ground up with Data Analytics in mind.

Like most things Microsoft, it’s been developed internally and consequently seen great internal adoption. Hence, it’s being made available as a product to the rest of the world.

KQL is used heavily in Log Analytics, where we can join different tables of immense size in order to interrogate performance issues with our Azure systems – and get the answers really quickly, even when we have terabytes (or even petabytes) of data involved.

This is the type of interrogation that people might have used products such as Splunk for previously.

It’s worthwhile playing with this ‘help’ cluster and getting to know the KQL language, it’s looking like the adoption rate could be huge – and it’s always good to know your options, even if you find out that this is not for you.

Also, have a look at the connection string. Press the ‘Edit’ button in the bar that states the name of the ‘help’ cluster

ADX cluster properties (connection string and display name)

And we see the query string, and have the opportunity to change the display name of the cluster – if we like.

If you wish to query the cluster inside Azure Data Studio using the KQL extension then that’s possible too – see this post for details.

While KQL might be a little different to T-SQL, most database professionals will easily ‘acclimatise’ to the new language and be up and running quite quickly.

Watch this blog for more future tips.

Hope that helps somebody get up and running with ADX and KQL

Have a great day

Cheers

Marty.

ADX and KQL – Free Cluster for learning purposes

G’day,

I’ve been looking around a bit lately at Azure Data Explorer (ADX) Clusters and the Kusto Query Language (KQL).

I recently learned that Microsoft is letting people experience the wonders of ADX for free.

I assumed – wrongly as it turned out – that you would need an Azure subscription in order to do this, but in fact you do not.

In order to test this, I grabbed myself a brand new free email address from outlook.com

I then opened a brand new InPrivate window in the Edge browser and made 100% sure that none of my other Microsoft accounts were being picked up.

The next step was to navigate to http://aka.ms/kustofree in the InPrivate window

I was immediately asked to log in with my new outlook.com account, which I duly did. After that I was transported to https://dataexplorer.azure.com/freecluster

Note that this new email address has to be an MSA, although you do not have to sign up for any Azure account – free or otherwise.

After that, there’s a big button in the middle of the screen that will allow you to create a brand new free cluster – and database as it turns out.

Fill in a few details in the subsequent dialog box

Use your own cluster and database names above. Accept the terms and conditions and enjoy.

As you might expect, you get all of the core features – but you’ll get some more if you upgrade at some stage. The table on this page will outline exactly what you are getting. The free cluster lasts for one year – but the docs seem to indicate that this could be extended. However, you’ll probably have a good idea of functionality after one year.

So, what are you waiting for, try out the new free cluster.

We’ll look at getting some data in there next time, but if you can’t wait for that there’s some more details here

Have a great day

cheers

Marty.

Azure Data Explorer – Using KQL Magic inside Azure Data Studio

One of my previous blogs looked at using the free resources that Microsoft provides to link to an Azure Data Explorer Cluster and start using Kusto Query Language.

In this post, I want to look at doing a similar thing, but this time using Azure Data Studio (ADS). The cluster that we will link too will be the same one that we previously used when we link to an ADX cluster via the Azure Portal.

Open Azure Data Studio (ADS) and install KQL addin.

Now select “New Notebook” – then select the Python Kernel

If you don’t have a python installation then don’t worry, as soon as we execute our first command then ADS will prompt you to install one.

I’m going to summarize the instructions in this post and add my take to them, if you want to switch to that post (or use the two combined) feel free.

The first two code cells, just set up KQL for us

Here’s the actual code

!pip install Kqlmagic –no-cache-dir –upgrade

%reload_ext Kqlmagic

The next part require authentication – but all you require is a Microsoft account (outlook , hotmail ect)

here’s the code cell

%kql AzureDataExplorer://tenant=”Microsoft.com”;code;cluster=’help’;database=’Samples’

When you run the cell, you’ll be given a code and a link to a Microsoft site.

Simply navigate to the link, authenticate using a Microsoft account and type in the code.

You’ll get a message stating everything is fine and then you’ll be able to head back to ADS and carry on with the final statements they will query the database.

Next, let’s run some code

And the results are

Here’s another KQL statement

The %%kql is a “Magic” command that signals to the kernel that what we are about to run is not Python – but Kusto Query Language!

Hence KQL Magic!

Hope this helps somebody

Cheers

Marty

Azure Data Explorer – A great way to learn Kusto Query Language

Log Analytics is a great resource for storing our monitoring data from Azure.

But in order to get the best from your nicely acquired monitoring data, you’ll need a way to investigate it. You can do that in the Azure Portal by clicking around in the UI.

Kusto Query Language is the way to investigate data that is stored in Log Analytics.

We can either experiment in our own log analytics workspace – or we can use the workspace that Microsoft provide for such things.

The Log Analytics Playground

You can find that here

You’ll need to authenticate with a Microsoft account (eg outlook, hotmail)

Personally, I think a great resource for learning Kusto Query Language (KQL) is the Azure Data Explorer (ADX) cluster that Microsoft provides.

You can find that here

There’s a bit more real estate – but you choose 🙂

The ADX cluster is clean and easy to find your way around

There’s enough data in there to run some KQL queries – and get to grips with the language.

If you’re looking to learn Kusto Query language then I’d suggest heading along to this resource.

Hope this helps somebody.

Cheers

Marty

Azure Data Explorer – Setting up a Cluster

I’ve worked with Log Analytics quite a bit over the years and that’s taken me down the road of the Kusto Query Language – or KQL

So, using Azure Data Explorer Clusters seemed the next logical step on that journey.

Log Analytics is great for storing and querying diagnostic data from Azure (or any other infrastructure) – but I decided that I might want to investigate my own data.

So, with that in mind – I decided to set up my own Azure Data Explorer Cluster and start looking around.

It’s easy enough to find in the Azure Portal – I chose to favorite it as I expect to be using it a fair bit over the next little while

Find “Azure Data Explorer Clusters” via the Azure Portal search bar

You can also get to it via a shortcut

Select “Azure Data Explorer Clusters” – optionally favorite

Next, I select “Create” and then filled in the minimum amount of information needed to get up and running.

Just a few things to focus on here – firstly, the combination of the cluster name and the region is going to form part of a URI – so this has to be unique, and there are certain formats that the name has to meet, but you’ll soon know if you have not met them as the screen will call them out instantly.

Additionally, I’m just creating a Dev cluster here – note that there is no SLA.

Also, the region that I chose has Availability Zones – yours might not, or you might choose not to take advantage of them. I chose to include all three.

You’ll then notice that you can step through all of the remain setup.

Additional setup (can be skipped – I accepted the defaults)

I simply accepted the defaults, but it’s great to know you can define additional settings at this stage.

It’ll take a little time to set the cluster up – but you’ll soon be up and running.

And once we are up and running we can see that the URI is made up of the cluster name and region

In order to create a database on the cluster head to the resource, click on “Databases” in the left hand side menu (scrolling may be needed), click “Add database” near the top of the screen and then enter a name for the database in the fly-out pane.

Create a new database on out ADX cluster

That’s it – we now have our first Azure Data Explorer Cluster created with a new shiny database.

Hope this help somebody.

Cheers

Marty.