Must Learn KQL Part 6: Interface Intimacy
Chapter 6
This post is part of an ongoing series to educate about the simplicity and power of the Kusto Query Language (KQL). If you’d like the 90-second post-commercial recap that seems to be a standard part of every TV show these days…
The full series index (including code and queries) is located here:
The book version (pdf) of this series is located here:
https://github.com/rod-trent/MustLearnKQL/tree/main/Book_Version
The book will be updated when each new part in this series is released.
…
I preface this post by saying this: everything discussed in this post about the User Interface (UI) can be done (and should be done, eventually) in the KQL query itself.
When you’re just starting with KQL, the UI can be a blessing. As you get further in your learning and comfortability with the query language, it can be a crutch – particularly when you need to find something quickly because of a perceived security threat and view it in a way that’s most meaningful. Still, understanding the UI’s capabilities is important.
In this post, I’ll give you a whirlwind tour of the UI, but again with the assumption that, eventually, every action it provides I’ll cover on how to accomplish it using KQL as we get further and further along in this series.
The Logs blade exists in almost every Azure service, allowing you to query the activity logs for that service. For our purposes for Microsoft Sentinel, since all of those services’ (and more) logs are consolidated in the Log Analytics workspace for Microsoft Sentinel, we get to use the UI to query everything. It can be a bit of a power rush.
For those that already have deep-level experience with the Logs UI in Azure services, this may not be your favorite part of this series, but you also may learn something you missed or that’s been updated recently, so make sure not to overlook anything important. And, please, please, PLEASE – if you’re the expert in the UI and with KQL, pass this along to someone who needs it.
Like everything in Azure, there’s updates and enhancements constantly, so I’ll try to keep this part of the series up-to-date continually. My youngest son is the epitome of FOMO (fear of missing out) and I feel like him sometimes when I’ve been away from the Azure portal or the Microsoft Sentinel console for even a day. Every day can be a new adventure. As a customer, you might think, or even become frustrated that it’s hard to keep up with all the changes going on in the Azure services and other products. But, believe me, those of us that work at Microsoft are faced with the exact same scenario and the same difficulties in keeping up-to-date. So, we can help each other in this respect. See something in this part of the series that’s slightly off or maybe improved? Or, maybe I’ve chosen not to cover an area or feature that you need more knowledge about. Let me know and I’ll get it updated toot sweet.
HANDS-ON: If you’d like to follow along yourself with the UI areas and descriptions in this post (instead of just reading through them in the text), use the KQL Playground that is referenced as a Practice Environment in the resources list of Part 1.
I’ll start this part of the series talking about those areas in the UI that are most important to our efforts in learning how to manipulate the KQL query data, and then follow up with the rest of the interface in the Extras section below, so you get the full intimate affair. And don’t forget to come back for Part 7 for the Schema Talk (see the TOC) where I’ll finish up covering the UI with those areas of the UI that pertain to working with the tables.
OK…so let’s dig in…
Filtering through the table elements
To focus on a specific column, select the ellipses next to a column heading and choose the Filter icon, then select values to adjust the results display.
The example query in the above and following images is located here: https://aka.ms/MustLearnKQL
Sorting results
To sort the results by a specific column, such as timestamp, click the column title. One click sorts in ascending order while a second click will sort in descending. An arrow will display in the column next to the column title to show which direction the results are sorted.
Grouping results
To group the results, first toggle the ellipses as you did in Filtering the columns, then choose the hamburger menu icon to expose the column’s grouping option.
Selecting columns to display
To add and remove a column that is displayed select the Columns pane on the right-hand side of the results display.
You will notice when you work with this UI feature, there’s a number of columns that are omitted from the results display. There’s some intelligence built in that looks at the table data and only shows results that it deems pertinent to the operation – in our case, that operation is security monitoring. It also locates columns that contain no data and omits these from the display. All of these measures are intended functions to help build efficiency and eliminate unnecessary data, but also to improve query results performance. But, using this feature (and actual KQL operators like project we’ll talk about later on), you can use the UI to pick and choose what to review.
Select a time range
To add a custom time range, select the Time range option.
Incidentally, 24 hours is the default for Microsoft Sentinel. Each time you enter the console or attempt to work with KQL in the Logs blade, it will default to this time value. This is based on security principles that a SOC or security teams should be focused on the most current data. Responsibilities, tasks, policies, and procedures of a well-tuned security team should ensure that all current events are monitored and managed in some way at the end of each day so that they are ready for the next round of events. That’s not always the case, of course, but that’s one reason why Microsoft Sentinel always defaults to 24 hours.
Charts
To add a chart as a visual format you can select the CHART option just above the results window at the bottom of the UI. On the right-hand side you have many options for manipulating the visual aspect the data.
Note that charting is dependent on tabular data. I’ll talk about this when we get to the summarize, render, and bin operators in this series. (See the TOC)
EXTRA
In the previous section, I’ve discussed those areas in the UI that are going to help you manipulate the results. Again, while those are important areas, I’ll show how to accomplish each of those using actual KQL query operators, so you don’t have to rely on the UI.
You might notice I didn’t spend any time talking about the Tables, Queries, and Functions areas in the Logs blade. I’ll actually come back to those in Part 7 when I talk about the schema. (See the TOC)
But before closing out this part of the series, I do want to also highlight some other cool areas of the UI that you might enjoy and have fun with.
Save search queries
You can save your queries to Query Packs and then look them up and use them later. For more information on Query Packs, see: Query packs in Azure Monitor Logs and How to Save an Azure Sentinel Query to a Custom Query Pack
Share Queries!
Sharing your fabulous query creations is an important capability for a number of reasons and not just for an ego boost or pat on the back when bragging to friends and colleagues.
There are three sharing options:
Copy link to query: Since the Azure portal and Microsoft Sentinel console is web-based, you can share the direct URL to the query you created by pasting it somewhere (email, Teams chat or channel, etc.). When you share the link and someone with proper access clicks on it, they are taken directly to the Logs blade and the query is run, so they can review the same results. This is an awesome team activity where you can get an extra set of eyeballs on a potential situation.
Copy query text: This function just copies the query itself so you can send that somewhere (to a team member, to a GitHub repo, etc.)
Copy results: Right now, this function literally does the exact same thing as the Copy link to query option. So, we’ll put a pin here for when this changes in the future.
And, by the way, you can also submit your KQL creations to the official GitHub repository for Microsoft Sentinel. See Add in your new or updated contributions to GitHub for steps on how to accomplish that.
Format query
A super-cool, super-useful tool is the Format button in the UI. This button takes a badly formatted query and reformats it so it a) works, or b) is in a more uniform, more readable format.
As I noted in Part 3 about Workflow, because of the power of the pipe (|) command separator, a KQL query can be a single line of code. But that’s a bit useless if you want to be able to determine what the query’s intent is or need to debug it. This option turns it into a better format.
Queries Galore
In addition to all the awesome KQL query goodness available from all over the Internet, there’s a slew of example KQL queries available to access in the Logs blade itself. Just tap or click the Queries button to gain access.
Exporting Queries
The Export option in the UI gives you the ability to export the query results in a number of ways.
You can export all data to a csv, export only the data in the displayed results, generate an M query for use in creating a Power BI dashboard, and export and open immediately in Microsoft Excel.
New Alert Rule
You can create rules for either Azure Monitor or Microsoft Sentinel directly from the Logs UI. This is an awesome feature that allows you to create and tune your query until it’s perfect and then begin the steps to turn it into a rule to automatically analyze security for your environment. We’re not quite at that step in this series, so we’ll come back to this feature in Part 21. (See the TOC)
Pin to Dashboard or Workbook
Pin to Dashboard is an interesting feature in that you can take the query results that are formatted as a chart and pin the visualization directly to the standard Azure portal dashboard. This dashboard can be your own private collection of visualizations or a collection that is shared among your teammates or even supplied so your manager has purview into operations.
An additional option here is the ability to send the query to a Workbook. This is useful when you need to use the Logs blade to develop the query and instead of copying/pasting into an existing Workbook that you’re currently developing, you can just insert it into the Workbook. Of course, you can create Workbooks from here using the new queries, too.
Settings
I’m not going to dig into each option, but the Settings icon contains configuration adjustments including things like how double-clicking works, if you want to see tables that contain no data, how many rows per page should display by default in the results window, and other things.
In-UI Reference
Lastly, to round out this intimate review of the Logs UI, there’s a very good, very solid collection of references built into the UI. Some of those I’ve already supplied as references in Part 1, but, like everything in Azure, this is also updated continually. So, keep an eye out here for updates.
Tabs
To help keep you organized, much like a web browser the UI also supports tabs.
This is awesome functionality to allow you to work on different queries or different datasets in each tab. If you right-click on each tab, there’s a context menu pop-up that allows you manage the tabs in various ways including duplicating the current query in a new tab.
Keyboarding Shortcuts
If you’re a die-hard keyboarding fan like myself, rest easy knowing that you can help speed up your query development using a couple key combinations. It’s also for us lazy people who can’t suffer the time to lift our hands from the keyboard to locate the mouse and click on one of it’s buttons.
Shift + Enter causes the query to run. Ctrl + Enter starts a new command line, complete with the command (pipe (|)) character.
Intellisense for the Win
Much like how addressing an email works, the Logs UI will try everything it can to use autocomplete to try and figure out what it is you want to accomplish. Just start typing in the query area and the applicable options will display in a list.
…
But, wait…there’s more…
Next, in Part 7 (see the TOC), there’s a bit more of the UI to talk about. But that deserves its own part since we’ll be talking in relation to working with the tables and the schema.
Stay tuned…