Hi Folks,
In today’s scenario when enterprise infrastructure gets lots of hits and complex configuration in place, its unavoidable to track the records and data generated through communication, events happening between services and their sub components.
In such cases it’s needed to have any tool or query language which can help us to generate event and metadata records generated for services, infrastructure and events. here comes the introduction and understanding of KQL (Kusto Query Language).
KQL is a read-only query language. The syntax is similar to SQL, but it was created specifically to work with large datasets in Azure. Since it’s read-only there are no update or delete clauses. It is based on relational management systems, which use schema entities, and is organized into a hierarchy like SQL’s databases tables and columns.
The result generated through querying can be exported to CSV/Excel, data can be visualized and shared through Excel, or Power BI, or directly from the ADX(Azure Data Explorer).
KQL (Kusto Query Language) was developed with certain key principals in mind, like —
- Easy to read and understand syntax
- Provide high-performance through scaling
- Transition smoothly from simple to complex query.
As said KQL is very similar to SQL with a sequence of statements, where the statements are modeled as a flow of tabular data output from the previous statement to the next statement. These statements are concatenated with a pipe (|) character.
In SQL, the queries start with the column names and we only get to know about the table name when we reach the “From” statement, whereas, in KQL, the query starts with the table name followed by the pipe character after which the conditions are defined. We will see how this works shortly.
KQL is the query language and the Kusto Engine is the engine that receives the queries in KQL to execute them, and specifically the large datasets from Azure, like –
- Azure Application Insights
- Azure Log Analytics
- Windows Defender Advanced Threat Protection
- Azure Security Center
Apart from these, the data can be ingested from external sources as well. It can be done using the custom code in any preferred language like Python, .Net SDK, R, etc. using the Azure Data Explorer API. Data can also be ingested using Event Hub’s and Event Grid’s, and from the CSV file as well.
KQL offers feature to run SQL commands as well. Here find the attachment for SQL to KQL Cheat sheet (https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/sqlcheatsheet)
Kusto Management Commands : https://docs.microsoft.com/en-us/azure/data-explorer/kusto/management/
Reference Links :
- https://github.com/rod-trent/MustLearnKQL
- Azure Kusto REST API > https://docs.microsoft.com/en-us/rest/api/azurerekusto/
- https://docs.microsoft.com/en-us/azure/data-explorer/kusto/api/rest/
- https://docs.microsoft.com/en-us/azure/data-explorer/kusto/api/rest/streaming-ingest
- https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/splunk-cheat-sheet
- https://techcommunity.microsoft.com/t5/testingspot-blog/learning-kusto-query-language-a-tool-for-performance-test/ba-p/2308480
- https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/
- https://docs.microsoft.com/en-us/azure/data-explorer/write-queries
- https://docs.microsoft.com/en-us/azure/data-explorer/kql-quick-reference
- https://azure-training.com/azure-data-science/the-kusto-query-language/
- https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/tutorial?pivots=azuredataexplorer # Tutorial: Use Kusto queries
- https://www.sqlservercentral.com/articles/an-introduction-to-kusto-query-language-kql
- https://docs.microsoft.com/en-us/azure/data-explorer/kql-quick-reference
- https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/tutorial?pivots=azuredataexplorer
- https://squaredup.com/blog/kusto-101-a-jumpstart-guide-to-kql/
- https://www.pluralsight.com/courses/kusto-query-language-kql-from-scratch
- https://www.sqlshack.com/azure-data-explorer-and-the-kusto-query-language/
- https://docs.microsoft.com/en-us/azure/data-explorer/kql-quick-reference
- https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/samples?pivots=azuredataexplorer
- https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/tutorial?pivots=azuredataexplorer
- https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/
- https://www.pluralsight.com/courses/kusto-query-language-kql-from-scratch
- https://www.pluralsight.com/courses/microsoft-azure-data-explorer-advanced-query-capabilities
