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:

| 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…

The Where operator is the best way to accomplish this. You can see here in the example that we’re filtering first by when the occurrence happened (TimeGenerated) and then by Windows Event ID (4782 – the password hash of an account was accessed.).

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

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

What do we get now?

Being a data professional we should order our data so we will tell the query engine how we want to order the results.
| where TimeGenerated > ago (3h)
| where EventID == 4634
| summarize count() by Account
| order by count_ desc
| project Account, LogOffEvents = count_

Using the Order operator above, we’re telling the query engine that when the results are displayed, I want it shown in order by the LogOffEvents column.

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…



One thought on “KQL Series – creating KQL queries

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s