Writing to Data Tables from Rules in Google SecOps

David-French
Staff

Welcome to part two of this series where we’re learning how to leverage data tables in Google Security Operations (SecOps). In part one, I explained how to create data tables and use them to filter events in rules. In today’s post, I’m going to demonstrate how to write values to a data table from a YARA-L rule.

Populating data tables with relevant information allows security operations teams to more readily implement a variety of detection and threat hunting scenarios. To provide some inspiration, below are a few use cases that will hopefully spark some creative ideas on how you can leverage data tables in your own environment.

  • Tracking cloud console login events over time. Relevant metadata about login events may include user ID, IP address, geolocation information, and whether or not Multi-Factor Authentication (MFA) was used for authentication.
  • Profiling notable or potentially suspicious file executions, network connections, or DNS queries over time. This metadata can be used in downstream rules to generate an alert based on a cumulative risk score.
  • Maintaining information about files shared and downloaded via Google Workspace or Slack.
  • Accumulating a historical record of notable API calls made to your Cloud Service Provider (CSP). For instance, API calls that are used to enumerate billing settings or creating/modifying IAM roles or policies.

Please note, at the time of this writing, it’s not possible to write to a data table from a search query. This functionality is expected to be added in a future release.

To put this concept into action, we’re going to create a rule that identifies when a user shares a file via Google Drive with an email address that’s associated with a free service such as Gmail or Hotmail. If the rule’s logic matches on any of our ingested events, it will write some metadata to a data table.

Creating the Data Table

Before we can write values to a data table from a rule, we need to create the data table and define the column names, data types, and entity field mappings (if required). A little planning is required when choosing these parameters, as it’s not possible to modify them after the data table is created.

As per the image below, I’ve named the data table, “google_drive_files_shared_with_free_email_domains” and entered names for the various columns. The data type for all of these columns is “string”. I’ll go ahead and click the save button to create the new data table.

image.pngCreating a new data table in Google SecOps

Writing to the Data Table from a Rule

Now that the data table exists with the required column names and data type mappings, let’s create a rule that matches on events that are relevant to our use case and writes the values from specific UDM fields to the data table. I’m going to step through the rule’s individual sections and explain what’s happening along the way. The complete rule can be found in the appendix section of this post.

In the events section of the rule, we’re filtering for Google Workspace events where a user shared a file via Google Drive with an email address that’s associated with a free service such as Gmail or Hotmail.

events:
  $ws.metadata.vendor_name = "Google Workspace"
  $ws.metadata.product_name = "drive"

  (
    $ws.metadata.product_event_type = "change_user_access" or
    $ws.metadata.product_event_type = "change_document_visibility" or
    $ws.metadata.product_event_type = "change_document_access_scope" or
    $ws.metadata.product_event_type = "change_acl_editors"
  )

  // File shared externally with free email domains
  $ws.target.resource.attribute.labels["visibility"] = "shared_externally"
  $ws.target.user.email_addresses = /.*@gmail\.com|.*@aol\.com|.*@ymail\.com|.*@ymail\.com|.*@hotmail\.com|.*@outlook\.com|.*@icloud\.com/

In the outcome section of the rule, various values are being stored in outcome variables that will be eventually written to the data table.

outcome:
  $event_time = timestamp.get_timestamp($ws.metadata.event_timestamp.seconds)
  $principal_user_email = window.last($ws.metadata.event_timestamp.seconds, $ws.principal.user.email_addresses)
  $principal_ip = window.last($ws.metadata.event_timestamp.seconds, $ws.principal.ip)
  $target_user_email = window.last($ws.metadata.event_timestamp.seconds, $ws.target.user.email_addresses)
  $resource_name = window.last($ws.metadata.event_timestamp.seconds, $ws.target.resource.name)
  $object_id = window.last($ws.metadata.event_timestamp.seconds, $ws.target.resource.product_object_id)

condition:
  $ws

You might be wondering why I’ve used the window.last function in the outcome section. Values written to a data table must align to the data type assigned to the column. If you recall from earlier, I specified the “string” data type for the columns in the data table, so using an outcome statement like $principal_user_email = array_distinct($ws.principal.user.email_addresses) will return an error like the one shown below.

To avoid this error, I’m using the window.last function to store a single string value in the outcome variables. The window.last function is capturing the values in the latest UDM events for the respective field names by using the metadata.event_timestamp.seconds value.

image.pngValues written to a data table must align to the data type assigned to the column

The final section in this rule is the “export” section. This is a new type of section for YARA-L rules, which is where we’re going to write values to a data table using the write_row function. In the export section of the rule, we’re using the write_row function to write the values stored in the placeholder variables (that we defined earlier) in a new row under the specific columns of the data table.

export:
  %google_drive_files_shared_with_free_email_domains.write_row(
    event_time: $event_time,
    principal_user_email: $principal_user_email,
    principal_ip: $principal_ip,
    target_user_email: $target_user_email,
    resource_name: $resource_name,
    object_id: $object_id
  )

There are a couple of things to be aware of relating to the export section. This must be the last section defined in your rule as the final action is to write data to the data table after certain things have occurred such as the definition of variables in the outcome section.

Data tables also have the concept of key columns where one or more columns make up a primary key. This topic will be covered in more detail in another post, but it’s something to keep in mind as you get your feet wet with data tables. When using the write_row function, you can overwrite a row in a data table with a matching key (a combination of column names and values that match an existing row in the data table). If a matching key is not found in the table, a new row is written instead. Currently, the key columns for a data table can only be specified upon the creation of a data table using Google SecOps’ API.

I don’t want this rule to generate detections or alerts when it matches on Google Workspace activity, so I’m going to leave the Live Rule and Alerting options disabled. Let’s populate the data table by creating a Retrohunt using events logged since the beginning of April this year.

image.pngCreating a new Retrohunt in Google SecOps’ rules editor

image.pngSelecting a start time and end time for the Retrohunt

After the Retrohunt has completed, we can see that a number of rows have been written to the data table by the rule. The values written to this data table can be leveraged in other detection use cases and threat hunting scenarios or serve as a data point during the security team’s investigations.

image.pngReviewing the contents of the data table

Wrap Up

That brings us to the end of this post where we learned how to write values to a data table from a YARA-L rule in Google SecOps. I hope that this example provides you with some inspiration on how you can leverage data tables for your team’s detection and threat hunting use cases.

Please feel free to reach out with any questions. Until next time!

Appendix

Rule: google_workspace_file_shared_from_google_drive_to_free_email_domain

rule google_workspace_file_shared_from_google_drive_to_free_email_domain {

    meta:
      author = "Google Cloud Security"
      description = "Identifies when a user shares a file on Google Drive with a free email domain and writes event metadata to the google_drive_files_shared_with_free_email_domains data table."

    events:
      $ws.metadata.vendor_name = "Google Workspace"
      $ws.metadata.product_name = "drive"

      (
        $ws.metadata.product_event_type = "change_user_access" or
        $ws.metadata.product_event_type = "change_document_visibility" or
        $ws.metadata.product_event_type = "change_document_access_scope" or
        $ws.metadata.product_event_type = "change_acl_editors"
      )

      // File shared externally with free email domains
      $ws.target.resource.attribute.labels["visibility"] = "shared_externally"
      $ws.target.user.email_addresses = /.*@gmail\.com|.*@aol\.com|.*@ymail\.com|.*@ymail\.com|.*@hotmail\.com|.*@outlook\.com|.*@icloud\.com/

    outcome:
      $event_time = timestamp.get_timestamp($ws.metadata.event_timestamp.seconds)
      $principal_user_email = window.last($ws.metadata.event_timestamp.seconds, $ws.principal.user.email_addresses)
      $principal_ip = window.last($ws.metadata.event_timestamp.seconds, $ws.principal.ip)
      $target_user_email = window.last($ws.metadata.event_timestamp.seconds, $ws.target.user.email_addresses)
      $resource_name = window.last($ws.metadata.event_timestamp.seconds, $ws.target.resource.name)
      $object_id = window.last($ws.metadata.event_timestamp.seconds, $ws.target.resource.product_object_id)

    condition:
      $ws

    export:
      %google_drive_files_shared_with_free_email_domains.write_row(
        event_time: $event_time,
        principal_user_email: $principal_user_email,
        principal_ip: $principal_ip,
        target_user_email: $target_user_email,
        resource_name: $resource_name,
        object_id: $object_id
      )  
}
2 1 44.5K
Authors