Welcome to our third and final article in our series on improving Azure security. Today’s article will explore the business architecture of Log Analysis Logs / Azure Data Explorer, which are some of the products that rely on Kusto Query Language (KQL) to retrieve data. quickly and efficiently from collected logs / telemetry. At this stage, we know how to configure our log analytics, create our first query using the query editor, send the diagnostic parameters to our log analytics. It’s time to dig deeper and understand the log analytics architecture and the two key operators that will follow us throughout the series.
Overview of Log Analysis Architecture
In Part 1 and 2 of this series, we covered the basics to create our first query, but there’s more to the architecture side of log parsing and the Kusto query language. We’re going to explore the basic concepts to get our queries off the ground, but before we get there, we need to explore the entities that make up the KQL universe and some Azure tools and services that can be used as part of our process.
For starters, Kusto Query Language isn’t just for log analysis. It’s the language most Microsoft Azure products use to query data (Microsoft takes the big picture – PowerShell was never just for Windows, you see?).
One of those services is Azure Data Explorer, a fast and highly scalable data mining service for logs and telemetry. It allows multiple sources, such as IoT, websites, apps, etc. We also have other products like Azure Data Studio, Power BI and many more to come. The goal is to analyze the data using Kusto Query Language.
Azure Data Explorer is important because it implements the concept of entities, which is native to Kusto query languages. When building using cmdlets, we’ll see New-AzKustoCluster and we’ll see the Kusto string in the cmdlet to create the Azure service.
When using Azure Data Explorer we have the concept of a cluster where the databases are stored. The cluster can have multiple databases.
A database is where all of the Kusto query languages run, and a database can contain one or more tables. Tables can be viewed using log analysis and they are highlighted in the image shown below.
A table can contain one or more columns. Each column is associated with a data type, which we will use to perform searches and use operators corresponding to the data type. The actual data is stored in the rows and it is arranged according to the columns defined in a given table. The icon in front of each column is a graphical representation of the data type.
There are a lot of details around entities, but you should be aware that entities are case sensitive, including tables and columns. When you start writing your first few queries, you’ll find that looking up data in the “azureActivity” table won’t work, but the same query when using “AzureActivity” will work fine.
House Rules to be on Track with Log Analysis and Query Editor
This section isn’t a complete list of all the pitfalls, but the idea is to help you get up to speed. Here are some rules:
- Be specific as much as possible, and general queries will take longer. Cleaning is required on the results.
- Comments in KQL are “//“in front of the string. Add this and this line will not be executed.
- When troubleshooting your queries, you will need to run a subset of your query. There are a number of ways to tackle this. You can select the desired query and click on Shift + enterr. A second option is to create a new tab, place your code there and use the To run button.
- Use indentation – it helps a lot!
- KQL is not SQL. You can stack multiple lines of “where” statements, and the next statement will be based on the input provided by the previous statement (similar to PowerShell using a pipeline). For example, if in your first where statement you limit yourself to the last five minutes, the second where statement will work with those values. So play smart with the directions where to go from the most comprehensive data to the very specific information you are looking for.
- In theory, when using multiple where statements, try to refine the results of the first one using the TimeGenerated column.
Getting Started with Queries: Finding Items
When we run KQL we are in the context of the database, so we can use a simple statement to search the entire database to find any information we need, which means search all tables.
To note: Of course, the performance here won’t be good, and unless you get it on time, you’ll always try to be more specific when looking for information about your data. The following command finds the name of my resource group in the current log analysis workspace.
Let’s go to our playground to begin our queries. You will first see that the time range is set outside of the query (item 1). We have two separate queries in the editor (item 2). The first is the simplest operator and string. In the second query, we change the behavior to be case sensitive.
In the same screen we have the time and number of records taken from item 3) of our query (we are running row 2). The results of the query are visible at the bottom of the blade. Since we have the exact string in multiple tables (item 5), we can see which tables it is. If we expand a given record, we will have a column with the table name (item 4), which helps when looking for something.
If we want to find something in a specific database, we can restrict it by the name of the table. There are several ways to do this. We will show three formats that will provide similar results.
|search for “ResourceGroupName”||Terrible. It will search the entire database for the string.|
| search for “ResourceGroupName”
|Bad. It will examine the entire table for the string.|
|search in (AzureActivity) “ResourceGroupName”||Bad. It will examine the entire table for the string.|
| search for ResourceGroup == ”ResourceGroupName”
|Good. It will look in a specific column for the string.|
While the search instruction does a good job, my tool of choice is the or operator, which gives more flexibility and an appropriate structure to start more advanced queries.
AzureActivity | where ResourceGroup== "RG-CAC-AP6"
You can combine conditions in the same query to refine your results.
AzureActivity | where ( ResourceGroup== "RG-CAC-AP6" and ActivityStatusValue=="Start" )
Your log analysis architecture is up and running
In our process of exploring the Kusto Query Language and Log Analysis Workspace to increase our level of security, we looked at the entities available when we created our queries, and this helps define the limits. We’ve also added some essential tools to our toolkit: the “find” and “where” operators, how we can improve performance, and some best practices for building the habit of writing our queries.
Featured Image: Shutterstock
Following Azure Kusto query language articles