The No Hassle Guide to Event Query Language (EQL) for Threat Hunting

Did you ever have the urge to put together a few PowerShell scripts to parse and analyze the Sysmon event log in order to create your own threat analysis software? Nothing to be embarrassed about! But before you do anything rash, you should first read about the results of my own modest efforts in this area. If you’re still convinced you want to take on this project, you’ll quickly realize, as I did, how hard it is to develop real enterprise-level threat monitoring.

Part I: Overview of EQL
Part II: Finding Threats With EQL’s Join and Sequence
Part III: Advanced Threat Detection

Thankfully, there’s now open-source software, known as EQL, that takes care of the painful parts of this project involving parsing and organizing the Sysmon event data. With EQL, you can practically dive right in, avoiding the steep curve I went through herding PowerShell, Sysmon, JSON,  data structure, and more into something useful.

In Part I, we’ll quickly cover the basics of EQL, and then in Part II, we’ll show how you can do fairly sophisticated behavior-based analysis to discover threats. Part III is an even deeper dive as we start exploring the Mitre Att&ck threat model with EQL.

EQL is a first step to getting a handle on  cyber threats. But there’s only so much that you do on your own!  Watch the Varonis Incident Response team detect and manage real-world attacks. Register now for a live workshop.

Part I: Overview of EQL

EQL (pronounced equal) is a cybersecurity language designed by the folks at Endgame — love that name! — a cyber research and consulting company. While EQL was initially restricted for use within the company, it was released in 2018 as an open-source project on Github  to nurture collaboration among security practitioners worldwide. Great idea! EQL also has potential as a pen-testing tool, which we’ll explore in a future post.

The EQL core language is based on Python, there is an integration with Windows Sysmon, and there are extensive analytics. EQL benefits from its ability to match events, stack data, and perform analysis of aggregate data sets. In plain-speak, you can easily tap into a lot of process context that would usually require a complex query and coding for something like, “all the processes that performed network activity that are descended from “regsrvr32.exe”.  It’s also schema-independent and OS-agnostic, and so can be used with any data set or operating system (Linux, Windows).

The goal of EQL is to go beyond legacy reliance on Indicators of Compromise (IoCs)  by using familiar shell-type syntax to craft queries for spotting interesting behaviors. By the way, the security analytics capabilities match up with the Mitre ATT@CK model. There’s a lot more information on the EQL way of doing things in this very watchable video — skip to about the 6:50 mark.

EQL Ideas

Let’s get into the basis in this initial dive. To simplify search, EQL thankfully drops the over-abundance of keywords found in PowerShell scripts in favor of a simpler, more practical function syntax. These functions can be used to perform math, and create more sophisticated expressions without entering long keyword-heavy strings. Yay!

Booleans

As you’d expect, EQL has boolean operators (and, or, not),  the usual comparers (<,>,<=,>=,!=), and there’s a case-insensitive wildcard search available via the asterisk character. For example, if you wanted to look up a “svchost” service process that doesn’t have either -k in the command line, or services.exe as a parent process, you would write the query like this:

process where process_name == "svchost.exe" and (command_line != "* -k *" or parent_process_name != "services.exe")

Sequences

EQL sequences can be used to identify data points that share common attributes, such as a common process path and file path. These sequence queries can also be made time- and event-sensitive, for example, you can set a tracking point to end when a log-off event occurs, or after a set period of time has elapsed. This can be helpful to remove non-unique entries and reduce memory usage.

A generic sequence query looks like this:

sequence[event_type1(process, network, etc.) where condition1]
[event_type2 where condition2] ...
[event_typeN where conditionN]

Joins

Bless them for making database-like joins very simple. Joins can be used to match unordered events that share one, or several, user-defined properties. EQL’s join can be thought of as a form of the sequences syntax, but without accounting for time constraints.

join  by shared_field1, shared_field2, ...
[event_type1 where condition1] 
[event_type2 where condition2] ...
[event_typeN where conditionN]

Pipes

Pipes can be used to conveniently filter and reduce the number of results from a data set and add more specificity to queries in post-processing. You can remove duplicate entries using the ‘unique’ pipe, can request the most (or least) common entries in a data set with the ‘filter’ command, or sort with, you guessed it, the ‘sort’ command.

For example, the ‘count’ pipe returns the total number of entries found matching the search query. The basic pipe structure is as follows:

process where true | count

// results look like// "count": 100, "key": totals"

Process Lineage

Process lineage is automatically tracked to simplify the discovery of vital information about a process, such as its origin and how long it has been live. Using this lineage data, EQL can really isolate results you need.

You can limit results to just process identifiers (PIDs) with a specific parentage, or remove PIDs based on when they became active. This helps avoid searching the same results over and over since you can quickly ignore PIDs that were active before you last conducted the same search.  To search for PowerShell entries that weren’t launched by Windows Explorer, you’d enter this string:

process where process_name == "powershell.exe"and not descendant of [process where process_name == "explorer.exe"]

Hello World in EQL

This post is just a very quick introduction, and we’ll explore EQL more thoroughly next time. Can an infosec blogger install and run a very  trivial EQL query in about 30 minutes? The answer is yes.

I should have pointed out earlier that EQL works with JSON input. So if you want to analyze Sysmon output you need to convert it to JSON — which is easy to accomplish in PowerShell —and then it run through EQL. Fortunately, the Endgame gang has already published a few datasets on Github to work with.

Using one of the sets, I ran the EQL equivalent of “hello world” to count the number of Windows processes that start with “cmd”:

My point in this first section is to show that this is a very approachable language. In the next section, we’ll explore how to leverage EQL to accomplish useful threat exploration tasks.

Part II: Finding Threats With EQL’s Join and Sequence

Before we dive into EQL’s powerful features, let’s step back and examine what we’re really trying to do. The larger point about EQL is that it is most emphatically not meant to search for specific malware names or other obvious indicators — plain-text keywords buried in files.

Since you feed in low-level  Sysmon log files to EQL, you have very granular access to processes that are created, dlls loaded, as well as files read and written. With this information and real-world knowledge of threats — thank you, Mitre! — we can hunt for the underlying activities that won’t show up in a legacy virus or signature scan.

The EQL gang has even put together a mapping of the Mitre Att&ck matrix into corresponding EQL statements. If you want to get ahead of the game, you can take a peek at their threat scenarios, but we’ll return to it in Part III.

Working with the Sysmon Log

EQL is a query language, so what is it querying?  Answer: A JSON-formatted file based on the Sysmon log.

Fortunately, EQL has nice utility that will do the heavy lifting. You can go to my Github repository to download scrape-events.ps1, and then run the Get-LatestProcesses function to do the conversion, like so:

Get-LatestProcesses | ConvertTo-Json | Out-File -Encoding ASCII -FilePath my-log.json

Or if you’re a big fan of our Sysmon threat hunting post and already have installed my home-brewed module, you can run this as well

get-sysmonlogs| ConvertToJson|Out-file -Encoding Asci my-log.json.

If you don’t have Sysmon, you can borrow some pre-cooked customized logs that can be found in EQL’s repository.

Anyway, with the JSON log you’re ready to work with EQL. The installation for Python-based EQL is straightforward on a Linux platform, and then once the EQL command interpreter is up, import the JSON log with the “input” command:

At a more technical level, you have to make sure that core field names are available to EQL, and so you should study the EQL schema for various types of Sysmon events, which can be found here.

I’m focusing on “process” events for this guide, so you need a few fields (pid, ppid, timestamp) to accomplish some useful threat hunting. To get the full value of EQL, you’ll also need to tweak the timestamp field so it represents numeric ticks. Sorry, but you’ll have to add a PowerShell statement to do this as part of a pipeline (see the PS datetime object and the ticks property), and I’ll leave that as an exercise.

Basic Threat Hunting With Join

EQL lets you experiment by making it easy to test your (educated and well-researched) hunches. An ordinary user who  is working directly with PowerShell or the legacy cmd shell might be a tip off of that this account is engaging in unusual activities.

You can enter directly into the EQL command interpreter the following:

search process where process_name == “PowerShell.exe” or process_name == “cmd.exe”

Unfortunately, EQL will start spewing out JSON after you run this command, which may be tolerable for a small log file.  The way to tame  the rawl JSON is to use EQL’s  table statement, and format the output into columns:

Perhaps, there is some suspicious activity buried in here though it’s not unusual for Administrators to be mucking around with command shells.

You’d probably want to add criteria that’s based on more than just running a command shell: a command shell followed by “whoami.exe” and “findstr.exe” should trigger alarm bells. And then if EQL finds a match, you’d want to organize all the output under user name, rather than seeing it spewed out in random order.

This leads nicely to EQL’s “join” command, which I touched on in part I. It’s the familiar join from the database world, but instead applied to JSON output. I can craft the following command:

search join by User [process where process_name == "cmdl.exe"]
[process where process_name ==  “whoami.exe”]
[process where process_name ==  “findstr.exe”]

In effect, you’re  searching for any user who’s run all three of  these commands:

Let’s Sequence It!

The join commands lets me quickly see that employee Monty  is a possible suspect. There may be a benign explanation for his use of the cmd shell.  What would really make the case would be f this sequence of commands were run within a short time interval.

You can see what I’m getting at, right? Maybe Monty ran a cmd shell six months ago and then for whatever reason was searching his files using findstr, and then three months ago, he was bored, brought up a shell, and entered “whoami.exe”. Strange, but isn’t he a bit like you and me, as the song goes?

If the cmd shell, findstr, and whomai were all run within, say, 10 minutes of each other, then you can more confidently say that Monty’s behavior is far more consistent with an account that’s been hacked.

That’s where EQL’s “sequence” comes into play. It’s essentially a join with a time parameter. So I’d run it like this:

search sequence by User with maxspan=10m [process where process_name == "cmdl.exe"]
[process where process_name ==  “whoami.exe”]
[process where process_name ==  “findstr.exe”]

If I find that Monty’s activities fit this EQL sequence search, then I’m about ready to say “got ya”. Though I’d probably want to do more analysis.

This is a good point to end part II, and  you should try some of your own searches on JSON-ized Sysmon log data. Check back next week for part III, where we’ll go deeper into EQL  and also explore some of the Mitre Att&ck threat scripts.

Part III: Advanced Threat Detection With EQL

With the above as background, we’re now ready for more realistic (and complicated) queries. But before we delve into this, it’s worth the time to take up some practical matters with EQL.

It is a very flexible language, and in fact, you can try EQL on any JSON-formatted data. It’ll work fine. What makes process data special is the relationship between parent and child, which is captured in the pid and ppid fields in the Sysmon log.

In order to play along with EQL, there are few fields in the JSON-formatted file that should have the required names that you look at here in the EQL documentation under the schema definitions.

Let me bring to your attention  two particular fields since it’s not obvious what they map into from the  Sysmon record. They are unique_pid and unique_ppid. If you look at the Sysmon log (below) you’ll see the fields ProcessGuid and ParentProcessGuid.

You guessed it, they map into the aforementioned pair of fields. And you’ll have to keep in mind to use these names in your PowerShell code.

One more piece of business. As I mentioned in the previous section, for EQL’s sequence, you’ll need a “timestamp” field. And that is found in the “UtcTime” field in the Sysmon record. There’s a little bit of PowerShell to convert this universal time into ticks, which EQL then uses to quickly calculate seconds, minutes, or hours duration:

$t= [datetime] $_.UtcTime
$obj| add-Member -MemberType NoteProperty -Name timestamp -Value $t.Ticks

You’ll need something like the above buried in the iterator that’s going through the objects from the Sysmon log. Note: I wasn’t completely consistent with my field names in my own experiment, so please forgive my use of Cline instead of the standard “command_line” below.

Exploring Sysmon With EQL’s Descendant Search

In the previous section, we began using  EQL to look for possible threats. The idea was to search for the use of cmd.exe or  whoami.exe or hostname.exe in Sysmon  with the knowledge that ordinary employees would likely not be entering these command and therefore this could be a sign of a threat.

After making my field names more consistent with EQL’s schema, I can now leverage the “descendant of” and “child of” EQL keywords for a cooler kind of search.

What I really want to do is find all processes that are descended from a cmd.exe shell. Why do that? Because as we pointed out early, the use of a cmd shell is unusual for average workers, but very normal for malware or a hacker. With the “descendant of” EQL query, I can not only find the child processes but also anything these processes themselves launched. The whole shebang!

Playing the part of a defender. I used the following EQL to search though my Sysmon data:

Search process where descendant of [process where process_name == “Cmd.exe”] or process where process_name == “Cmd.exe”| sort ppid

And then organized my results into pretty columns with this:

table User,pid,ppid, Cline

Here’s a section of the output that is particularly revealing of threats:

The output redirections for user Monty are suspicious, as well as the cluster of whoami.exe, find.exe, and hostname.exe commands for the System user.

As the defender, I want to dig a little deeper and look at a process with pid of 3584, the parent process and then perhaps its parent, to learn more about the second group of incriminating commands:

You’ll notice in the above screenshot of my EQL session that a strangely named process in the C:\Windows started the whole chain. Those of you’ve been reading the blog  know that this is a sign of a remote psexec.

One more piece of confirming evidence is to see if the whoami.exe and find.exe processes have been run in a very short period of time, say 5 minutes. If so, that would really make the case for a threat. From the previous section we know about the EQL join’s maxspan option. You can see the results of my query below:

EQL’s Pre-Cooked Mitre Att&ack Queries

The far larger point in the above EQL exploration is that we’re looking for behaviors. I’m not searching for a specific malware name or scanning for some plain text keywords. When the attackers are living of the land  this kind of approach is not very successful. As I’ve been trying to show,  searching for the underlying activities, which can’t be hidden because they’re captured in the Sysmon log, is a far more productive method.

However, you or I can’t possibly know the underlying activities and behaviors that go along with all the threats out there! Thankfully, the Mitre folks have their Att&ck Matrix, and the EQL team has worked out queries that correspond with their classifications. And you can find all this delicious information here.

I tried their WMI Execution with Command Line Redirection, but suitably modified because I didn’t have all the Sysmon log information that’s required—file audit data ’cause we know that’s a CPU killer, and also spews out too may log entries for my modest experiments.

You can see the results of my modified WMI threat hunting query below:

In the next section, we’ll look at more of the Att&ck framework, take care of a few loose ends, and then conclude with a big picture view of threat hunting.  Check back again later next week!