Azure Data Studio is a great tool and supports way more than only SQL. Recently, Azure Data Studio included the support to Kusto language, or KQL.
Kusto is a query language used for massive amounts of streamed data, perfect for logs. That’s why Kusto was initially created for Azure Monitor and Azure Log Analytics.
However, Azure Data Studio only supports Kusto connections with Azure Data Explorer, not Log Analytics. All this huge focus on Azure Data Explorer given by Microsoft still puzzles me. Log Analytics is a ready-to-use monitoring solution for cloud and on-premises environment.
On the other hand, Azure Data Explorer is a database storage MPP (Massive Parallel Processing) solution for massive stream storage – logs – allowing anyone to create their own monitoring solution using it as a storage, but not a ready-to-use solution as Log Analytics.
Considering these differences, the use cases for Log Analytics seems related to migrations to the cloud and all the following monitoring work. Log Analytics can be a fundamental tool on the process of cloud adoption, because it gives numbers to the process. These numbers allow the IT Administrators to do in the cloud what only a few really manage in an organized way: Keep baselines and compare results after every environment change.
On the other hand, software houses creating custom solutions or, in some situations, solution providers trying to manage a big number of clients, may find Azure Data Explorer as a better solution for them. It puzzles me that one receives more attention than the other. What do you think? Let’s talk about on the comments.
Now it’s time to go direct to the point. Of course, you already noticed how I got disappointed by discovering Azure Data Studio couldn’t connect to Log Analytics. However, with some help from Microsoft community on twitter I was able to find a work around: We can use KqlMagic, a Python package, to connect to Log Analytics.
Yes, Azure Data Studio is so flexible it supports Python and we can install Python packages.
The Challenges and What Doesn’t Work
If you are thirsty for the solution, you can skip this part and go to the solution a bit below on the blog. However, I like to explain how I reached the solution and what didn’t work. At the bare minimum, you will find some great links and ideas.
What may appear easy in the beginning, is not at all. Let’s face the challenges:
- The article explaining how to do it only connects to a demo Log Analytics Workspace kept by Microsoft
- The github repo has a Get Started With KqlMagic and Log Analytics which redirects you to an online executable notebook with some explanations about the connection string, but using the demo workspace
- If you ask help to KqlMagic itself, the information you get is the same on the notebook, and it doesn’t help to much when trying to connect to a workspace.
The Existing Documentation
This is the documentation we have:
(1) authentication with appkey works only for the demo.
(2) username/password works only on corporate network.
(3) alias is optional.
(4) if credentials are missing, and a previous connection was established the credentials will be inherited.
(5) if secret (password / clientsecret) is missing, user will be prompted to provide it.
(6) if tenant is missing, and a previous connection was established the tenant will be inherited.
(7) a not quoted value, is a python expression, that is evaluated and its result is used as the value. This is how you can parametrize the connection string
(8) anonymous authentication, is NO authentication, for the case that your cluster is local.
The numbered items help to exclude the options that don’t work:
- The connection string on the demo only works for the demo
- We can’t use anonymous, for sure
- I tried to leave some parameters missing, but it didn’t work for me, KqlMagic always complains about the missing parameters
I can ensure you: The solution is above, but it is still difficult to find.
More Documentation
Another closer look on the KqlMagic git repo and we find this documentation about authentication methods on the repo:
-
AAD Username/password – Provide your AAD username and password.
-
AAD application – Provide your AAD tenant ID, AAD app ID and app secret.
-
code – Provide only your AAD username, and authenticate yourself using a code, generated by ADAL.
-
certificate – Provide your AAD tenant ID, AAD app ID, certificate and certificate-thumbprint (supported only with Azure Data Explorer)
-
appid/appkey – Provide you application insight appid, and appkey (supported only with Application Insights)
We have a match
Finally, it’s just a matter of linking the dots: On the sample connection strings, the one which makes more sense mention an AppId inserted as the clientId key value, even against the advise that App Ids work only for the demo. On the other hand, the list of authentication methods mentioned above includes an AAD Application. We have a match!
I don’t remember exactly how I found this great link about Log Analytics API , but it includes information about how to register an application with Azure Active Directory. The final result is not exactly what we need, because the documentation focus is make a direct access to the API. However, we can still follow this link to register the application on Azure Active Directory.
The Solution
After many attempts, I finally figured out how to fit the information I have in the connection string. You need to follow the steps below:
- Create an App Registration on Azure Active Directory
- Create a client secret
- The client secret goes on the connection string, but not the client secret name. clientId is not the name of the client secret.
- Set the Api Permissions to Log Analytics Read as application permission (not delegation)
- On Manifest of the AAD application, copy the appId. The appId is the value to fill the clientId property on the connection string. This was one of the most challenging discoveries.
- On the Log Analytics workspace, copy the Id of the workspace. Workspace property on the connection string is the Id, not the name. This was another though discovery.
- On the Log Analytics workspace, Access Control (IAM) => Add => Add Role Assignment
- On the next window, give permission to the app registration to read the logs. You will find the app registration as a user.
- On the Azure Active Directory, copy the tenantId
The connection string you need to use:
|
1 |
%kql loganalytics://tenant='<<your tenant id>>';clientid='<<yourappid>>';clientsecret='<<your client secret>>';workspace='<<Your log analytics workspace id>>';alias='<<workspace name>>' |
Further Studies
- Metrics Advisor in Cognitive Services has Azure Data Explorer as its only Kusto source. It’s unable to connect to Log Analytics. However, Log Analytics Export is also a feature in preview, so we can export from Log Analytics to Metrics Advisor. There is a lot to discover here.
- Azure Data Explorer has a proxy which enables Data Explorer to connect to Log Analytics. Log Analytics becomes an additional node in the cluster. I think this solution may be useful in two situations:
- You are already using Azure Data Explorer for something (whatever) and you would like to keep everything in a single place.
- You are a solution provider managing hundreds of different Log Analytics services for different clients. The best architecture would be create a single place where you can query everything. This 2nd solution seems very interesting. If I’m not mistaken, we would need to use Azure Lighthouse as well.
- Metrics Advisor can connect to Azure Data Explorer. So, if we create an Azure Data Explorer and configure a proxy, we can use Metrics Advisor to analyze Log Analytics data through the proxy. This seems very interesting.
- Configuring the proxy, we can use the regular Azure Data Studio Kusto extension for Data Explorer and we can query Log Analytics data as well.
Load comments