New to Google SecOps: |> WHERE Did Our Logs Go?

To gain familiarity with using SQL in Google Security Operations (SecOps), it seems that a good approach is to learn a bit about the different pipe operators that are available. To address more advanced use cases and build more elaborate queries, we need to ensure the fundamentals are in place. I’m going to take a crawl, walk, run approach to this and start with the pipeline operator of WHERE.

The WHERE pipe operator reduces the number of rows in a dataset. Using the time range selector and WHERE are the two primary methods to constrain the amount of data that an analyst is going to work with in their query.

For users who have written searches in SecOps before, you may be thinking there isn’t anything different about a filtering statement in SQL pipes than what you are already doing today. That’s not entirely the case. My goal today is not to enumerate all of the different ways to filter data using SQL pipes but to highlight a few methods that SQL unlocks for us.

A Basic Search

In YARA-L, a basic hostname search in Google SecOps looks like this:

principal.hostname = "win-server.lunarstiiiness.com"

In SQL pipes, the syntax is very similar:

FROM events
|> WHERE principal.hostname = "win-server.lunarstiiiness.com"

The first line, the FROM statement, defines the dataset that is being searched. The UDM parsed data repository is named events. There are other datasets that can be searched as well including cases and case history, detections, the entity graph and data tables. Once we’ve defined where the data resides, we can use pipe operators to refine the data being searched. Because WHERE filters rows of data, this pipe operator is one of the two, along with SELECT, that will be used immediately in most queries.

Case Sensitivity

It’s important to understand that while SQL terms, pipe operators and functions like WHERE are not case sensitive, fields and values are. This means that if you want to find all the permutations of this hostname, upper case, lower case, and mixed case, you will need to add a SQL function to the WHERE pipe. Here are a pair of functions that you can use, I prefer LOWER but if you like to type in all caps, the function UPPER works too.

FROM events
|> WHERE LOWER(principal.hostname) = "win-server.lunarstiiiness.com"

Here we are using the SQL function lower which converts the values in the principal.hostname field to lowercase and then compares it to the value in the statement, which is in all lowercase.

Numeric Ranges

When working with numeric fields, we can use greater than and less than amongst other operators like this:

|> WHERE network.sent_bytes >= 1000 and network.sent_bytes <= 10000

We could also use the BETWEEN operator that generates the same result as the WHERE statement above.

FROM events
|> WHERE network.sent_bytes BETWEEN 1000 and 10000

If we wanted events where the sent bytes are outside of that range, we could add the operator NOT before the word BETWEEN. 

FROM events
|> WHERE network.sent_bytes NOT BETWEEN 1000 and 10000

In UDM, numeric fields that do not have a value are represented with a default value of zero (0). Because of this, it isn’t enough to negate the range and call it a day or we may get additional events.

Instead, we can add a piece of logic to the WHERE pipe.

FROM events
|> WHERE network.sent_bytes NOT BETWEEN 1000 and 10000 and 
         network.sent_bytes <> 0

Now we are returning events that are not in the range of 1000 to 10000 and only return sent_bytes that are not 0. Notice the operator we are using for the term not equal. We can use either <> or != to represent this inequality.

Using IN as a Comparison Operator

If we want to search for multiple terms within a single field, we could build a search using the OR operator between each term. Speaking as someone who has built a number of queries in SecOps, I feel like I am wearing out the Ctrl, C and V keys as I copy and paste the term metadata.event_type multiple times.

FROM events
|> WHERE metadata.event_type = "PROCESS_LAUNCH" or 
         metadata.event_type = "NETWORK_CONNECTION"

SQL provides the IN operator to search for multiple values within a field without needing to specify the field separated with the OR operator multiple times. Instead of the previous search, we can build the WHERE pipe like this:

FROM events
|> WHERE metadata.event_type in ("PROCESS_LAUNCH", "NETWORK_CONNECTION")

The results include both process launch and network connection events in the result set.

Wildcards

Sometimes we need to search events that contain a field with some commonality in the value, but it isn’t a complete string match so using the equal sign won’t return the data we want. One option that SQL provides to assist with this conundrum is the operator LIKE along with wildcards. 

If I want to find all the events associated with servers that start with the string win-, I could use a wildcard search. Rather than an equal sign, use the term LIKE and then use the percent sign (%) as the wildcard. If we are searching for hostnames that start with win-, the  expression looks like this:

FROM events
|> WHERE principal.hostname like "win-%"

We can also use the underscore ( _ ) as a wildcard to match a single character.

Pro tip: If you need to represent a backslash, underscore or percent sign in a wildcard statement using LIKE, make sure you escape it with a backslash first. Much like regular expressions, we need to make sure that if we want to find a percent sign, we can’t have it being considered a wildcard.

Alternatively, we could use the SQL function STARTS_WITH followed by the field and string we are looking for. Don’t forget we can nest functions, so we can convert the principal.hostname to lowercase and then specify the string to match against in a single step like this: 

FROM events
|> WHERE STARTS_WITH(LOWER(principal.hostname), "win-")

Regular Expressions

Here’s another method that we can use to filter using the WHERE pipe operator. If you really want to use regular expressions instead of a wildcard or a function like STARTS_WITH, you can use the function REGEXP_CONTAINS. Google’s implementation of regular expressions, re2, is supported by SQL. 

In this example, we are applying the regular expression to the principal.hostname field to identify events which match the expression. Notice that the regular expression is encapsulated in single quotes. Those are not backticks, for anyone who has used re.regex in YARA-L!

FROM events
|> WHERE REGEXP_CONTAINS(principal.hostname, '^(wrk|win)-')

The carrot indicates that we are matching from the beginning of the string and searching for wrk OR win followed by a dash.

Pro Tip: The following is more computationally efficient and equivalent so consider leading with the function instead of the regular expression, but know that the regular expression is available if needed.

FROM events
|> WHERE principal.hostname != "" 
      AND ( 
            starts_with(principal.hostname, "wrk-") OR 
            starts_with(principal.hostname, "win-") 
           )

Raw Strings

When we start filtering data, we often find ourselves working with file paths and command lines that contain those pesky backslashes. Remember that backslashes need to be escaped with another backslash, so at a minimum, a logic statement in the WHERE pipe looks like this:

FROM events
|> WHERE metadata.event_type = "PROCESS_LAUNCH" and
         LOWER(target.process.command_line) = "c:\\windows\\system32\\sppsvc.exe"

This is pretty straightforward for direct string matches like those using an equal sign or even a function like STARTS_WITH, but when we start mixing in the use of wildcards or regular expressions, an additional stage of parsing of the query by SQL is needed.

Remember that both wildcards and regular expressions use escape characters for values beyond the basic parsing of collapsing the two backslashes to a single literal backslash. SQL will perform this parsing automatically during the query but we need to have the correct number of backslashes to account for both of these stages occurring so that we can accurately compare the value in the query to the value in SecOps.

In the absence of raw strings, the query we would need to build using the LIKE wildcard operator looks like this: 

FROM events
|> WHERE metadata.event_type = "PROCESS_LAUNCH" and 
         LOWER(target.process.command_line) LIKE "c:\\\\windows\\\\system32\\\\sppsvc.%"

Personally, I’m not a fan of so many escape characters in my query if I can avoid it. Using raw strings, called by placing the letter r (or R) before the quotation, bypasses the initial string compiler (not the one for regular expressions and wildcards). This notation tells SQL that what comes after it is a raw string where backslashes are treated as regular text and not as code.

Direct String

I’m using uppercase R here but lowercase works the same way.

FROM events
|> WHERE metadata.event_type = "PROCESS_LAUNCH" and
         LOWER(target.process.command_line) = R"c:\windows\system32\sppsvc.exe"

Regular Expression and Wildcards

While we have to add escape characters to handle the backslash in this example, we aren’t overwhelmed by them. I’m using lowercase r here just to highlight that both work.

FROM events
|> WHERE metadata.event_type = "PROCESS_LAUNCH" and 
         LOWER(target.process.command_line) LIKE r"c:\\windows\\system32\\sppsvc.%"

Below are additional examples of how this parsing of the backslashes occurs using equality, regular expressions, wildcards and the SQL function STARTS_WITH to help illustrate how SQL parses the data in two passes and if results would be found in the command line based on everything else in my instance being handled by default.

We’ve scratched the surface of the WHERE pipe operator by highlighting some of the capabilities it brings when filtering rows from a data set in SQL. When you use the WHERE pipe operator, here are a few things to keep in mind:

• Comparisons are case sensitive so consider using the LOWER or UPPER SQL functions
• The IN operator can be used with a list of values rather than having to write field = value1 OR field = value2 OR field = valueN
• BETWEEN provides an easy way to specify a range to search
• Numeric fields that appear as nulls in Google SecOps have a default value of zero, so be prepared to filter them out as well

I hope you try these capabilities out as you start building SQL pipe statements in Google SecOps. I will have more tips on other SQL pipeline operators in the coming weeks!