Must Learn KQL Part 12: The Render Operator
Must Learn KQL Part 12: The Render Operator
Chapter 12
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.
…
Before jumping into this chapter’s topic on generating graphics using the Render operator, I want to first thank everyone for making this series such a success. I recognized that something like this was needed, but I had no clue how much of an immediate impact it would be. The positive notes I’ve received about this series from customers, partners, and my own colleagues has been overwhelming, so that lends me to believe we’re on the right track and that this needs even wider distribution. We only get smarter by sharing what we know or what we learn. This series needs to reach far and wide. If you love this series, please share it with someone. And, again, thanks so much for being here!
…
This chapter may seem like somewhat of a detour on our path to using KQL queries to create Analytics Rules for Microsoft Sentinel, but there’s some very real value in turning rows and columns of data into visualizations. Sure enough, across Microsoft Sentinel, you’ll use KQL for almost everything – that includes the Workbooks feature that allows organizations to develop their own views of the security data. Workbooks can be used to create dashboards of consolidated data. I’ve worked with several customers in 911-emergency-type settings where they erect massive screens at the front of the room. KQL allows them to create the dashboard views that display on the screens so the entire security team can be privy to potentially nefarious operations in near real time. So, having an understanding of how KQL can produce visualizations is important.
There’s another great reason to put some effort into learning how to transform static data into graphs and charts – and it’s not just because I said in Part 6 when I gave the tour of the User Interface…
I like to think of myself as reader. I remember growing up reading book after book and loving it. One of my favorite series was the Hardy Boys. I read the entire series and some of the books more than once. I seriously believe that mystery books like those have a lot to do with my fascination with cybersecurity. But, somewhere along the way I was introduced to comic books and telling stories with pictures and words was fantastic to me. I’m a visual, hands-on type of learner so comic books really filled a void.
Fast forward to today. Every day I read tomes of emails, Teams messages, social media posts, etc., etc. And frankly this has soured me to just general reading. Friends and family are always recommending books and I just look at them and shake my head. Once they see my response to their recommendations, they quickly switch to “well, just get the audiobook version, you’ll love it” as if that’s somehow a better alternative. I truly wish I could go back and be that early reader and get excited about it all. But I read so much as part of my job, sitting down with a book in a quiet room now seems like torture to me. But, as a visual learner, that comic book style still appeals to me. If you can show me in a meaningful way the storyline of a threat, I’m all in.
P.S. I still read comics books to this day.
And that, for me, is where the KQL Render operator comes in. Render tells the query engine that you want to take the data you’ve supplied, and show it in any of the following ways (visualizations):
areachart – Area graph. First column is the x-axis and should be a numeric column. Other numeric columns are y-axes.
barchart – First column is the x-axis and can be text, datetime or numeric. Other columns are numeric, displayed as horizontal strips.
columnchart – same as barchart.
piechart – First column is color-axis, second column is numeric.
scatterchart – Points graph. First column is the x-axis and should be a numeric column. Other numeric columns are y-axes.
table – this is the default view.
timechart – Line graph. First column is x-axis, and should be datetime. Other (numeric) columns are y-axes.
Something important to know is that each visualization requires a certain data type before it will display. I’ve boldened those requirements in the list above. As you see, many of the requirements are numeric, hence why I covered the Summarize operator in the previous part/chapter 11 (see the TOC). What I didn’t cover in Summarize was how to take numeric and datetime values and group them into smaller specific values for use in visual displays using the Render operator. In the examples below, I’m including bin and time for your hands-on exercises in the KQL Playground (https://aka.ms/LADemo) but understand that Bin rounds values down to an integer multiple of a given bin size. It’s used frequently in combination with summarize by. If you have a scattered set of values, they will be grouped into a smaller set of specific values. For the first examples below, recognize that bin is being used to split out a week’s worth of data into daily chunks.
Render Operator Syntax
Tablename
| render visualizationJust the Data
SecurityEvent //The table
| where TimeGenerated > ago(7d) //Looking at data in the last 7 days
| summarize count() by bin(TimeGenerated, 1d) //Using Bin to group the data by each day
The Data as a Barchart
SecurityEvent //The table
| where TimeGenerated > ago(7d) //Looking at data in the last 7 days
| summarize count() by bin(TimeGenerated, 1d) //Using Bin to group the data by each day
| render barchart //Looking at the data in a Barchart
Here’s a couple additional examples for you to work with. The first one shows IP addresses with the most activity and the second displays disk space available for virtual machines.
And, lastly…if you want something with a really spectacular view, I give you a burst of exciting color.
IP Address Activity
VMConnection //Tablename
| summarize count() by SourceIp //Summarizing found IP addresses
| sort by count_ desc //Sorting the list in descending order
| render barchart //Showing the data in a barchart to show activity
Drive Space
Perf //Tablename
| where CounterName == "Free Megabytes" //Looking for free megabytes
| where InstanceName matches regex "^[A-Z]:$" //Looking for regular expressions for drive letters
| summarize min(CounterValue) by bin(TimeGenerated, 1d) //Grouping the data by each drive letter found
| render columnchart
Ooooo…pretty!
Usage //Tablename
| summarize count_per_type=count() by DataType //Creating the numeric value (summary) for types of data
| sort by count_per_type desc //Sorted by descending order
| render piechart //Display the data in a piechart
In the next two parts/chapters, we’ll get back on track as I start discussing how to manipulate the results that are display using the Extend and Project operators.