KQL Series – creating KQL queries

Basically when we are writing KQL we are asking the following questions:

Does it exist?

Where does it exist?

Why does it exist?

What shall we do with the information?

We will come back to these as we go along – but we use these questions to construct our KQL query.

Here is a simple enough search query:

SecurityEvent
| where TimeGenerated > ago (3h)
| where EventID == 4782
| summarize count() by Account
| project Account, PasswordsCracked = count_

(Note you can run this query here: https://aka.ms/LADemo )

Steps to create this query:

1. Identify the table we want to run our query against. So here we are running against the Security Event table. The SecurityEvent table contains security events collected from windows machines by Microsoft Defender for Cloud or Microsoft Sentinel.
2. We make use of the pipe | character which is above the enter/return key (return if you are on a Mac) and we use shift to get it. It is sued to separate commands issued to the query engine. In the example above each command is on its own line.
   A KQL query can be all one single line. As a recommendation, I prefer each command on its own line. It is neater and more organized which makes it easier to troubleshoot when a query fails (as mine typically do from time to time!!)
3. Just as we would with other SQL languages we want to filter things. If we just ran this – we would get all security events in the past 24 hours…

4. The next step in our workflow is to provide data aggregation. What do we want to do with this filtered data? In the example, we want to create a count of the Accounts (usernames) that produced event ID of 4782 in the time greater than 3 hours ago.

5. Lastly we want to present the data, we use Project for this. We tell the engine we only want to display 2 columns in our results Account and PasswordsCracked.

You’ll see that we go zero results – so let’s change our query and look for logoff events – EventID of 4634

SecurityEvent
| where TimeGenerated > ago (3h)
| where EventID == 4634
| summarize count() by Account
| project Account, LogOffEvents = count_

What do we get now?

The ‘desc’ in the query in the Order Data step is what produces this ordering. If we wanted ascending order we’d use ‘asc’.

So what did we do here?

It searched our stored security events in the SecurityEvent table for all Accounts that had a successful login in the last 3 hours and we chose to display only the Account and number of log off events per Account in numerical order with the highest at the top.

So far I’ve introduced some new operators and things – but what is a really quick way to learn KQL?

Let’s look at the best cheat sheet ever…

#Yip.