Must Learn KQL Part 10: The Count Operator
Chapter 10
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.
…
If you remember in the last part of this series (Part 9 on limit and take operators) I noted that in the query tool the query results are limited to 30,000 rows. Depending on how far back the data the is being stored, i.e., your Log Analytics workspace retention settings, there might possibly be hundreds of thousands of rows of data in the tables. Now, going back to what I said before (also in Part 9), if you need more than 1,000 rows of data to determine if something exists or is impactful to the environment, you might want to change your strategy. In my opinion, just knowing that a potential security situation exists is important enough to circle the wagons.
But a count of something is a good measure to get a better understanding of overall impact of a situation.
For example, if there’s one or two occurrences of a single person locking themselves out of their account in the last 30 days, that’s not usually a big deal. It’s most likely someone who forgot their password. As remediation, we can suggest to their manager that they might need to invest in training. We’ve all worked with those types of people. And as many of those types we know professionally, we probably know many more personally. My mom, my dad, my wife – yes, I’m also afflicted by those that believe passwords are just a nuisance and not something worth remembering.
But if we have record of that single person locking themselves out of their account 100 times in the last 30 days, that’s a more immediate concern.
This is where the count operator really shines.
Count operator syntax:
Tablename
| countOn its own, just using the operator syntax listed above will show the exact number of rows in a given table. For example, the following query shows how many rows exist in the SecurityEvent table.
SecurityEvent
| countTyping out this query in the KQL Playground (https://aka.ms/LADemo) will show something similar to the following screenshot…
Now, let’s take the same query we’ve been using for all of our query building exercises so far and add the count operator to it. Type this query in the KQL Playground (https://aka.ms/LADemo):
SecurityEvent // The table
| where TimeGenerated > ago(1h) // Activity in the last hour
| where EventID == 4624 // Successful logon
| where AccountType =~ "user" // case insensitive
| count // Number of successful logons
As before, the query results show us the number of successful logons in the last hour by all standard (non-admin) users. But, now with the count operator, the results tell us the total number of times this occurred.
I think we can agree that this is much more impactful data than just showing row after row of data and then having to manually sift through it. It’s important to grasp that adding a simple line to our original query changed everything. It made it even more powerful and even more relevant for our purposes.
Hopefully, you see as we are building our query toward Analytics Rule creation (see the TOC), that only simple steps are required to get us there. Each part of this series is intended as just one more simple step in the learning process.
The count operator will be a key to Analytic Rule development. In the next part of this series (see the TOC), I’ll talk about the summarize operator where the count operator will come into play again. In fact, we’ll be working with count quite a bit throughout the series. As important as the where operator is for filtering data (Part 8), the count operator is equally significant for its myriad of uses including helping create graphs and charts when we get to the render operator (see the TOC).
EXTRA CREDIT: The number of successful logons (Event ID 4624) is not necessarily something we look for when searching for security events. Instead, Event ID 4625 (unsuccessful logon) is the one most used to expose issues. For extra work and fun, in the KQL Playground (https://aka.ms/LADemo) simply change 4624 in the query to 4625 and run it again.
SecurityEvent // The table
| where TimeGenerated > ago(1h) // Activity in the last hour
| where EventID == 4625 // Unsuccessful logon
| where AccountType =~ "user" // case insensitive
| count // Number of successful logonsIn the KQL Playground (https://aka.ms/LADemo) you should get 0 (zero) results for your effort, but this is also an impactful number. If there are no unsuccessful logons in your environment – ever – you have been gifted with the unicorn of end-user populations and you should never leave your post.
