I hope you are enjoying this mini-series on SQL Pipe syntax and that you’ve tried a few SQL queries out in Google Security Operations (SecOps)! The pipe operators of WHERE, SELECT and AGGREGATE are a great springboard to get you started and after we cover EXTEND today, you will have a great foundation to build queries with!
Quite simply, the EXTEND pipe operator adds computed columns to a result set. That statement is a nice summary, but it only scrapes the surface of what EXTEND can do.
- Do you want to create a concatenated output of multiple values within various columns?
- How about creating a sum (or any other mathematical calculation) based on values within a row of data?
- Calculating the length of a command line?
- Classifying results from a column?
EXTEND can do all of those things…and more.
For those familiar with YARA-L, the EXTEND pipe doesn’t have an exact counterpart, but it can fill the role that placeholder variables perform in the filtering statement or in the outcome section in the form of conditional logic, for instance. Because EXTEND fills many different roles, it is a bit harder to provide a single example that summarizes how it compares to YARA-L, so here is a basic example to kick this off. Here we are generating an output of the time, IP pair, sent and received bytes and creating a new column with the total bytes for each event.
metadata.event_type = "NETWORK_CONNECTION"
network.sent_bytes > 0
network.received_bytes > 0
match:
metadata.id // Group by the unique event ID to guarantee row-by-row results
outcome: // Using a match section, fields must be aggregated
$time = timestamp.get_timestamp(max(metadata.event_timestamp.seconds))
$principal_ip = array_distinct(principal.ip)
$target_ip = array_distinct(target.ip)
$sent_bytes = max(network.sent_bytes)
$received_bytes = max(network.received_bytes)
$total_bytes = max(network.sent_bytes + network.received_bytes)
order:
$time desc
unselect:
metadata.id
Here is the equivalent query in SQL pipe syntax:
FROM events
|> WHERE metadata.event_type = "NETWORK_CONNECTION" and
network.sent_bytes > 0 and
network.received_bytes > 0
|> SELECT TIMESTAMP_SECONDS(metadata.event_timestamp.seconds) as time,
principal.ip as principal_ip,
target.ip as target_ip,
network.sent_bytes,
network.received_bytes
|> EXTEND sent_bytes + received_bytes as total_bytes
If you want to use traditional SQL rather than pipe SQL, it’s worth noting that like the AGGREGATE pipe, EXTEND is not a term you will find in traditional SQL. However, this query can be handled by performing the addition of the bytes in the SELECT statement like this.
SELECT
TIMESTAMP_SECONDS(metadata.event_timestamp.seconds) as time,
principal.ip as principal_ip,
target.ip as target_ip,
network.sent_bytes,
network.received_bytes,
network.sent_bytes + network.received_bytes as total_bytes
FROM events
WHERE metadata.event_type = "NETWORK_CONNECTION" and
network.sent_bytes > 0 and
network.received_bytes > 0
Either way, the results looks like this:
I’ve mentioned a number of capabilities that EXTEND can handle, so let’s dig into some additional capabilities.
Extracting Values into New Columns
In the previous example, we created a new column by taking the sum of two other columns. Let’s apply EXTEND to string values. SecOps stores the file name and full path in a single field, and perhaps we want to extract that file name to perform some additional analysis. We would apply the regular expression REGEXP_EXTRACT to the target.process.file.full_path, extract it and name the new column file_name using the EXTEND pipe. The result is two columns, the source column and the newly created one that just contains the file name.
FROM events
|> WHERE metadata.event_type = "PROCESS_LAUNCH" and target.process.file.full_path <> ""
|> SELECT target.process.file.full_path
|> EXTEND REGEXP_EXTRACT(full_path, r'\\([^\\]+)$') as file_name
Calculating Numeric Values from Strings
Now let’s take a string and use a SQL function to output a numeric. In this example, we are using the LENGTH function, one of nearly 200 SQL functions supported in Google SecOps, and outputting this numeric to a new column using EXTEND.
FROM events
|> WHERE metadata.event_type = "PROCESS_LAUNCH" and target.process.command_line <> ""
|> SELECT target.process.command_line as cmd_line
|> EXTEND length(cmd_line) as cmd_length
With all three of these examples, the total bytes, file name and command string length, we are probably not at the final step of the query, but it unlocks values that can then be further manipulated in the query. These next steps could be as simple as sorting or they could be used in additional statistical calculations or filtered to arrive at a final actionable result.
Classifying Events
In fact, let’s take this last example and take it a step further. Again a lot of this mini-series is around highlighting the building blocks that exist within SQL pipes that you can take to build your own queries for your use cases as well as ensuring you understand what is occurring in each of these steps. I’m not expecting everyone to be SQL experts but it is important to be able to understand the flow of these queries as we add more pipe operators to them!
With the command line length calculated in the previous query, perhaps we want to add a classification column to the results. The EXTEND pipe does this as well.
FROM events
|> WHERE metadata.event_type = "PROCESS_LAUNCH" and target.process.command_line <> ""
|> SELECT target.process.command_line as cmd_line
|> EXTEND length(cmd_line) as cmd_length
|> EXTEND cmd_length > 200 or cmd_length < 10 as interesting_rows
|> WHERE interesting_rows = TRUE
The first thing I want to call out is that we have two EXTEND pipes back to back in this query. In SQL pipes, we need two separate pipes in this case because the latter one is dependent on the results of the former. So, the first EXTEND calculates the length of the command line. With that calculated, the second EXTEND can use that calculated value and based on the logic provided compares the command length to the values provided and if it meets the criteria, it outputs true in the newly named column and if not, it returns false.
If we had wanted to perform additional classifications or create additional columns, we could append additional logic to the second EXTEND pipe and separate the new logic from what we already have with a a comma. The last step of this query is the addition of another WHERE pipe to further filter the result set to just the “interesting” columns.
In my estimation, the additional bonus that EXTEND brings is that because SQL pipes operate in a linear manner, we can create new columns during a query and then perform operations on those columns within the same query. In traditional SQL, this is often handled with a common table expression (CTE) or using the last example, adding more calculations to the SELECT statement as well as filtering in the WHERE statement as the query gets refined. In YARA-L, these additional calculations are often handled by building a multi-stage search which I’ve covered previously.
We’ve scratched the surface of EXTEND and will follow this blog with another one with more examples of EXTEND which go deeper into its capabilities. In the meantime, here are a few things to keep in mind as you get started:
- EXTEND creates additional columns in the result set
- Using SQL functions, it can be used to concatenate values, extract strings, perform mathematical functions and more
- These newly created columns can then be used by subsequent pipe operators
- Just remember that the EXTEND pipe can’t be used to calculate a new column and then immediately use that newly created column in that same EXTEND pipe; you need to add a second EXTEND pipe
Try some of these concepts out in your next search and let us know how it goes. I’ll be back with more tips, more pipes and the concepts we covered today will be leveraged as we go deeper into SQL pipes!
New to Google SecOps: Same as It Ever Was? Not When You Use |> EXTEND was originally published in Google Cloud – Community on Medium, where people are continuing the conversation by highlighting and responding to this story.
Source Credit: https://medium.com/google-cloud/new-to-google-secops-same-as-it-ever-was-not-when-you-use-extend-51998c82c476?source=rss—-e52cf94d98af—4
About the Author
