Welcome to the “Introducing” series. In the previous blog post, we introduced Log Analytics. In this blog post, we will introduce the query language used in a variety of areas including Log Analytics (introduced in the previous blog post).
What is Kusto?
Kusto or KQL (the Kusto Query Language) is a language that is used to process data and return results. It is an extremely powerful query language that can be used to perform complex queries on data stored in a variety of sources including Log Analytics.
Key pieces of Kusto:
- Queries start with the table that information that the data is stored in. As an example, a query can be as simple as “Usage”. That query shows the usage records for data sent into Log Analytics.
- Queries are extended by using a pipe (|). Example: “Usage | where TimeGenerated > now(-2hours)” shows any Usage type data written in the last 2 hours. Pipes can continue to be added such as here: “Usage | where TimeGenerated > now(-2hours) | project DataType, Quantity” which shows specific fields from the query.
- Project is extremely useful when you want to choose specific fields to show. You can also use project-away to remove specific fields from being shown.
- “let” can be used to define a variable. As an example, this takes the results of the previous query and defines it as a variable called RecentUsage. Note the ; at the end to indicate the completion of a query.
let RecentUsage = Usage | where TimeGenerated > now(-2hours) | project DataType, Quantity;
- “Sort” makes it easy to order how your data is shown. Example:
let RecentUsage = Usage | where TimeGenerated > now(-2hours) | project DataType, Quantity | sort by Quantity desc;
- Unions and Joins can be used to bring together types of data, making almost anything possible in Kusto.
The Kusto language reference has proven to be invaluable to me, I highly recommend it and I use the search functionality on this site regularly to find specific types of commands in Kusto and how to use them.
A quick history of Kusto
Work on Kusto started at Microsoft in 2013. In 2015, KQL was released to the world as part of Application Insights. In 2017, Log Analytics was ported to Kusto/ADX (Azure Data Explorer). Details on the timeline and how it interacts with OMS, Log Analytics and Application Insights are below:
- 2013 – Kusto started
- 2014 – Visual Studio Application Insights preview (based on Elastic Search)
- 2014 – Operational Insights (based on Lucene/Solr)
- 2015 – Log Analytics in Operation Management Suite (OMS)
- Management, Monitoring, Security
- 2015 – Application Insights re-platformed to Kusto, KQL is introduced to the world for the first time
- 2016 – Application Insights GA
- 2017 – Log Analytics re-platformed to Kusto/ADX
- 2018 – OMS is deprecated; Azure Monitor is announced
- 2019 – “soft rename” into Azure Monitor Logs; Sentinel launch
- 2020 – Application Insights on top of Log Analytics
Where is Kusto used?
Kusto is used in a variety of places in Azure and even outside of Azure. Areas that I am currently aware of that it is used include:
- Log Analytics
- Application Insights
- Windows Defender ATP
- Azure Security Center
- Azure Sentinel
- CMPivot (used in System Center Configuration Manager)
- Kusto is also integrated with Logic Apps, Flow, and other Microsoft technologies
How to use Kusto to get data out of Log Analytics
Queries that you run in Kusto can easily have their data exported by choosing the “Export” option shown below. Data can be exported via CSV (comma-separated values) or as an M Query. CSV files are often used when exporting data to work with it in Microsoft Excel (part of the solutions available in the Microsoft 365 cloud).
M Queries are used by applications such as PowerBI to provide a method to integrate data stored in sources such as Log Analytics.
You can also query Log Analytics workspaces using Kusto to gather data and use it in automation solutions such as Flow or LogicApps.
How to use Kusto to act upon data in Log Analytics
Once there is data in the Log Analytics workspace, you can use Kusto queries to take action upon the results that come back from those queries. Within Azure Monitor, you can create alerts that provide notifications when specific conditions occur based on the data that you have collected. To take the example from the Log Analytics blog post in this series, we could generate an alert when a group of CPUs is being over-utilized over some time to indicate that we should consider adding more compute resources. Alert rules can also be used to perform actions such as calling a webhook, which in turn can be used to perform an automated action. To bring this all together, if the group of CPUs that are providing a web application is over-taxed for over an hour, we could use an alert rule in Azure Monitor to call a webhook to automation to add more compute resources. Within our managed automation team use a similar approach to this to reformat the alerts into a structure that works better with our ticketing system.
Additional Reference material:
- Language reference: Overview – Azure Data Explorer | Microsoft Docs
- Getting started with Kusto: Getting started with Kusto | Microsoft Docs
- Kusto best practices: Query best practices – Azure Data Explorer | Microsoft Docs
- Where to start a query: https://docs.microsoft.com/en-us/azure/log-analytics/log-analytics-log-search
- Free course introduction: https://azure.microsoft.com/en-us/updates/free-query-language-course-la-ai/
- App Insights: https://blogs.msdn.microsoft.com/bharry/2016/03/28/introducing-application-analytics/
- Query reference: https://docs.loganalytics.io/docs/Language-Reference
- Azure Training series: The Kusto Query Language – Azure Training Series (azure-training.com)
- Pluralsight: https://www.pluralsight.com/courses/kusto-query-language-kql-from-scratch
Thank you to Oleg Ananiev for his information on the history of Kusto!