Today we are going to work with searches and dashboards in Google Security Operations (SecOps) and use a method that will display what matters to you in a result set. When I say display, I’m referring to the ability to view specific columns in results. You might be thinking, “yeah yeah, I’ve got a column selector in the UI, I’m good,” but I’d like to show you a few things that can be done with select and unselect and how this is yet another tool for your utility belt when it comes to building searches and dashboards.
Select/Unselect
The concept of select and unselect is pretty simple, we want to include columns in our result set, using select, and we want to exclude columns in our result set, using unselect.
When we say columns though, what does that mean? To me the best way to address this is to start with a good, old fashion UDM search. Here’s our starter search:
metadata.event_type = "PROCESS_LAUNCH"
metadata.vendor_name = "Microsoft"
principal.hostname = "win-server.lunarstiiiness.com"
Figure 1: Output of search query with four columns to get us started
The results of our search are what you would expect. In fact, I added four columns to the search results by using the columns button in the user interface. These columns are principal.hostname, principal.user.userid, principal.process.command_line and target.process.command_line. So far, so good.
Now, let’s say while we are happy with our search, we realize that we don’t need to call out the principal.hostname in the results, afterall, it’s in the query filtering statement. Additionally, I’ve decided I don’t want the principal.process.command_line. We could use the UI and remove these columns, which is perfectly fine, or we could modify our query. Let’s do the latter.
From a search layout perspective, the terms select and unselect make up their own sections in a search or dashboard query. Here is our refined query. Notice how we have added a select section to the query and we have specified the field names that should be included in the results set.
metadata.event_type = "PROCESS_LAUNCH"
metadata.vendor_name = "Microsoft"
principal.hostname = "win-server.lunarstiiiness.com"
select:
principal.user.userid, target.process.command_line
Figure 2: Output of search query with two columns in the select section of the query
In the result set, notice the principal.hostname and principal.process.command_line are no longer visible.
As I reviewed the results, I changed my mind about the principal.process.command_line and I decided to include it in my result set. However, I have no interest in the principal.hostname being displayed. I could add the command line field to the select section of the query or I could change the select to unselect and then specify principal.hostname to exclude that column from being displayed.
metadata.event_type = "PROCESS_LAUNCH"
metadata.vendor_name = "Microsoft"
principal.hostname = "win-server.lunarstiiiness.com"
unselect:
principal.hostname
Figure 3: Output of search query using the unselect statement on the column named principal.hostname
Now we can see three of the four columns we started with in the first query. This last example only works because I had selected a set of columns that were already being displayed in the resultset. If I executed a search and had no columns displayed in the UI and then specified unselect, the search would execute but no columns, beyond the timestamp and event columns which are always displayed, would be shown.
If we write a new query from scratch and add the select section, we will get the specified fields. In this case, we will get the principal.process.pid.
metadata.event_type = "PROCESS_LAUNCH"
metadata.vendor_name = "Microsoft"
principal.hostname = "win-server.lunarstiiiness.com"
select:
principal.process.pid
Figure 4: Output of an initial search query with a single select field provided
What this means is that we can also leverage the select section in our saved searches and build parameterized searches like this. We can specify the specific columns to output using the select section without having to add them in the UI using the column selector.
Figure 5: Example of a saved search using the select statement and parameters to streamline input and the result set
That said, if we use select or unselect and we want to add or remove columns via the UI, we can still do that, but this gives us an initial set of columns every time by building it into our query.
Unselect with Detections
There are numerous opportunities to use select and unselect, but here is one I really like because it helps me keep my dashboard charts tidy and succinct.
In the previous example, we added the sections select and unselect to a search where we only focused on including UDM fields in the columnar output. This time, we are going to extend this into statistical queries. This means that we can select or unselect on match and outcome variables too!
Here’s a scenario I imagine many of you have experienced. I want to display a table in my dashboard of recent detections with a couple of key values to accompany the rule name including when the detection last triggered, the number of detections that occurred within our time window as well as the severity of the detection.
detection.detection.rule_name = $rule_nam
detection.detection.rule_set = ""
match:
$rule_name
outcome:
$last_detection_time = timestamp.get_timestamp(max(detection.detection_time.seconds))
$severity = array_distinct(detection.detection.rule_labels["severity"])
$count = count($rule_name)
order:
$count desc, $last_detection_time desc, $rule_name asc
Figure 6: Output of the initial query for a dashboard chart
When I review the results, I’m feeling pretty good. However, when leadership takes a look, they want the table sorted by severity with Critical at the top of the list and Info at the bottom. This creates a challenge because adding severity to the order section of the query isn’t going to align the detections in the order leadership is asking for.
To overcome this problem, I could add another field to the result set. I’m using conditional logic with nested if statements to assign an integer to each of the severity values on a scale of 1-5 with Info being 1 and Critical being 5.
detection.detection.rule_name = $rule_name
detection.detection.rule_set = ""
$severity_rank = if(detection.detection.rule_labels["severity"] = "Info", 1, if(detection.detection.rule_labels["severity"] = "Low", 2, if(detection.detection.rule_labels["severity"] = "Medium", 3, if(detection.detection.rule_labels["severity"] = "High", 4, if(detection.detection.rule_labels["severity"] = "Critical", 5, 0)))))
match:
$rule_name
outcome:
$last_detection_time = timestamp.get_timestamp(max(detection.detection_time.seconds))
$severity_value = array_distinct(detection.detection.rule_labels["severity"])
$severity_ranking = max($severity_rank)
$count = count($rule_name)
order:
$severity_ranking desc, $last_detection_time desc, $rule_name asc
Now I can output this value in the outcome section and order the table by the severity_ranking so that the detections are listed with Critical first, followed by High, Medium, Low and then finally Info.
Figure 7: Query result when we add conditional logic that creates a custom sorted value, but also an extra column
At this point, I think we’re good to go, but leadership doesn’t like that I have this extra column being displayed. It doesn’t mean anything to them, severity_value has the severity in it that means something to them, so could we get rid of this column?
detection.detection.rule_name = $rule_name
detection.detection.rule_set = ""
$severity_rank = if(detection.detection.rule_labels["severity"] = "Info", 1, if(detection.detection.rule_labels["severity"] = "Low", 2, if(detection.detection.rule_labels["severity"] = "Medium", 3, if(detection.detection.rule_labels["severity"] = "High", 4, if(detection.detection.rule_labels["severity"] = "Critical", 5, 0)))))
match:
$rule_name
outcome:
$last_detection_time = timestamp.get_timestamp(max(detection.detection_time.seconds))
$severity_value = array_distinct(detection.detection.rule_labels["severity"])
$severity_ranking = max($severity_rank)
$count = count($rule_name)
order:
$severity_ranking desc, $last_detection_time desc, $rule_name asc
unselect:
$severity_ranking
By adding an unselect section to the end of the query, the answer to that would be, yes! This is a great example that demonstrates how we can perform a calculation in a search or dashboard but when it comes to display, we can use unselect to remove that value from our result view. Because the select and unselect reside as the last section in a query, think of it as the last thing that happens before the dataset is returned. In this example, I even sorted on the value before removing it.
Figure 8: Query result to add to our dashboard after using unselect to remove the sorted column from the view
When I show this to leadership, they find it more useful because it doesn’t contain extraneous data in the view.
Select and unselect on the surface are very straightforward but they make our life easier by including specific columns in search results or excluding extraneous columns for display. Here are a few things to keep in mind:
- Select and unselect are used in search and dashboards today
- Select and unselect are sections within a query so make sure they end with a colon (:)
- They are the last section of a query so they process after all other actions including ordering and limiting the results
- Field names, or in statistical queries, match and outcome variables, can leverage select/unselect
- They can be used in saved searches to provide specific columns to the user of the search
- If you decide you need more columns added to your result set, you can still choose them from the UI!
I hope you will consider leveraging select/unselect in search and dashboards as you continue to build your content. These are just a few examples but hopefully provide ideas that you can take and expand in your tenants! If you’d like to share how you are using it, we’d love to hear it!