A better place to handle your KQL queries.

5 Aug

This time a short post on creating Kusto KQL queries.

If you are into Azure monitoring you probably have used the log query editor webpage a lot in the azure portal. And you noticed that sometimes its hard to edit the query and that is could be slow or even crash….

Also one of the main disadvantage is that you can’t attach a Git repo to it. So you end up in copy and pasting the final query into a Git connected repro..

To solve this I use the Kusto.Explorer.

You have 2 flavours of this tool: A web and a native version. (https://docs.microsoft.com/en-us/azure/data-explorer/kusto/tools/kusto-explorer)

I this example I use the native version (since I want to use a Git repro and want to avoid web apps) , you can download it here : Kusto.Explorer tool

After installing the tool and starting it you will have to add a connection. As below:

Step 1 and 2 i am not going to explain

At step 3 you enter the connection string. How do we get this connection string ?

  • Be sure you are logged on to the azure portal using the correct tenant.
  • Construct the connection string as : https://ade.loganalytics.io/subscriptions/<subscription GUID>/resourcegroups/<resource group name>/providers/microsoft.operationalinsights/workspaces/<workspace name>

    Use the Azure portal to lookup the to be replaced values.

For example it looks like: https://ade.loganalytics.io/subscriptions/b63b6221-4a46-4fc1-c192-a8f6549d6d11/resourcegroups/kustotest/providers/microsoft.operationalinsights/workspaces/LogAnalyticsTest

  • Tip: use a Alias to provide some meaning full name to this connection like Production or Test

At step 4 we set it to use AAD credentials. Important is that the account that you are using at your workstation is joined to an local AD that is synced with the AAD in azure. (aka Federated)

At step 5 you will be able to press Ok to connect to the Log analytics workspace.

If the connection was successful you will see under the alias you used the log tables. See picture below:

 

And at this step you are ready to open a new work book and edit your query

And you can even do graphs

 

Connection troubleshooting

If you are having issues connecting to the log analytics workspace it could be that your AAD account is not part of the tenant were the workspace is placed.

So for example you have a AAD account in tenant A and the workspace is in tenant B . You check the access and it looks fine since you have the correct permissions setup to access tenant B by opening the workspace in the azure portal of tenant B.

In this case you will have to add a special tag “Authority Id” to the connection string as below and provide the tenant ADD guid:

Authority Id=<tenant AAD guid>

Use the advanced option to specify it.

 

Happy KQL’ing !!

Michel Kamp

 

One Response to “A better place to handle your KQL queries.”

Trackbacks/Pingbacks

  1. Microsoft Cloud ve Datacenter Management Ağustos 2020 Bülten – Sertaç Topal - August 29, 2020

    […] A better place to handle your KQL queries […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: