Must Learn KQL Part 6: Interface Intimacy

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:

https://aka.ms/MustLearnKQL

The book version (pdf) of this series is located here:

https://cda.ms/3m1

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 Filter icon, then select values to adjust the results display.

The example query in the above and following images is located here: https://cda.ms/3mD

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 Group Columns option, then simply click and hold and drag the column header above the other columns.

Selecting columns to display

To add and remove a column that is displayed select the Columns button.

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 about 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.

The example query in the image above is available from here: https://cda.ms/3mF

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 four sharing options:

  1. 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.
  2. 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.)
  3. 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.
  4. Share to community: This option is super-fantastic! By utilizing this sharing feature, the query you’ve created is copied and placed into an email template that is addressed to the Azure Monitor team at Microsoft. By submitting this after entering the requested information in the email template fields, your creation will be vetted and published to the GitHub repository for the Azure Monitor community! Imagine your name in lights, idolized for your contributions that helped solve the latest global security threat!

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

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.

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.

Tabbing

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.

Keyboard shortcuts

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…

=========================

[Want to discuss this further? Hit me up on Twitter or LinkedIn]

[Subscribe to the RSS feed for this blog]

[Subscribe to the Weekly Microsoft Sentinel Newsletter]

[Subscribe to the Bi-Weekly Defender for Cloud Newsletter]

Author