Use data tables

Data tables are multicolumn data constructs that let you input your own data into Google Security Operations. They can act as lookup tables with defined columns and the data stored in rows. You can create or import a data table to your Google SecOps account using the Google SecOps web interface, the data tables API, or by using a YARA-L query.

Assign scopes to data tables using data RBAC

To use data tables, you need to assign scopes to data tables using data role-based access control (data RBAC). By assigning scopes to a data table, you can control which users and resources can access and utilize it. For more information, see Configure data RBAC for data tables.

Manage data tables using the Google SecOps web interface

The sections that follow describe how to manage data tables using the web interface, including how to access your data tables, add a new data table and edit its contents, import data to your table, add rows, separate data using either commas or tabs, and how to remove a data table from your account.

Access your data tables

To access the Data tables page, do the following:

• On the left sidebar, select Investigation > Data tables.

To find a specific data table, at the top of the sidebar, enter its name in the Search field.

Add a new data table

To add a new data table to Google SecOps, complete the following steps:

1. Click add Add at the top right of the sidebar.

2. In the Create new data table dialog, give the new table a name and, optionally, add a description.

3. Click Create. The new data table appears in the main window and is ready to accept data.

Import data to your data table

To add data to your data table, you can import it directly into Google SecOps as follows:

1. Click Import Data.

2. Select a standard CSV file (only CSV files can be imported to Google SecOps).

3. Click Open when you're ready to import the data to your data table.

Map data types to single data table columns

Map each data type to a single data table column. Any column containing values for multiple fields must be split before they can be used as data tables.

In the following example, the data table column Field_value contains values for multiple fields:

The preceding table is split into four columns with each column mapped to only one field type before it can be used for any of the data table use cases presented in this document.

Map column names to entity fields (optional)

When you create a data table, you can map the column names of the data table to entity fields.

In the following example data table, the Userid and Role columns are mapped to entity.user.userid and entity.user.attribute.role.name, respectively:

The Email column can't be mapped to entity.user.email_address because it's a repeated field. Data table columns must map to a single-value (non-repeated) fields only.

You can map a data table column to an entity proto field using the mapped_column_path field of the DataTable resource.

For columns that don't have an entity path defined, such as Email in this example table, you must define a data type. As with reference lists, the supported data types for data tables are string, regex, and cidr.

You can include both mapped and unmapped columns in a data table by specifying a join condition.

Unmapped columns are stored in the additional field of the entity the table joins to. These are added as key-value pairs, where the key is the column name and the value is the corresponding row value.

Add a new row of data to your data table

To manually add a new row of data to a data table, you can enter it directly, with the first row serving as the table header. To do this, as follows:

1. On the Details tab, hold the pointer over the end of an existing row and press Enter.

2. Enter a new row of data:

   1. Separate the data fields using either commas or tabs.
   2. Be sure to match each data item to the appropriate data column.
   3. As you enter a row data on the Details tab, it populates in the Table editor.

Specify whether to use commas or tabs to separate data

To separate data using either commas or tabs, do the following:

1. Click grid_view Edit separator type next to Import Data.

2. In the Edit separator type dialog, select either Comma or Tab from the Separator menu.

Remove a data table

To remove a data table:

1. Select a data table from the Data tables list to the left.

2. Click Delete.

Manage data tables using the Chronicle API

You can also use the REST resources available in the Chronicle API to manage data tables in Google SecOps. The API can replicate all of the features available in the web interface, and includes some additional features that let you manage data tables with more performance and greater scale.

Here are the data table REST resources:

• dataTables

• dataTableRows

• dataTableOperationErrors

Use data tables in Google SecOps

Once you've imported data tables to your Google SecOps instance, you can use them to filter, enhance, and enrich your data using YARA-L queries. This document includes numerous examples in YARA-L syntax, which you can incorporate into Google SecOps.

You can use data tables in conjunction with YARA-L queries in both Search and rules. Data tables can be used in the following ways:

• Filter UDM event or entity data using a data table. You can filter UDM telemetry events and entities by comparing them with entries in a data table.

• Use a data table as a multicolumn reference list. You can use a data table as a multicolumn reference list. While a reference list can access data in a single dimension, data tables let you access data in multiple dimensions, enabling data filtering.

• Join a data table with an event or entity. You can link UDM events to a data table using the equality (=) operator for row-based comparisons. This comparison lets you filter the data. A row-based comparison evaluates as true only if all conditions in the statement match a single row in the data table.

Filter UDM event and entity data using a data table

You can filter UDM events and entities by comparing them to entries in a data table.

Link UDM events to data tables in either of the following ways:

• Using an equality operator (=, !=, >, >=, <, <=) for row-based comparison. For example, $<udm_variable>.<field_path> = %<data_table_name>.<column_name>.

• Using the in keyword for column-based comparison. For example, $<udm_variable>.<field_path> in %<data_table_name>.<column_name>.`

As with reference lists, the supported data types for each data table column can be string, regex, or CIDR.

To use a data table column of type CIDR or regular expression for row-based comparison, use the following syntax:

net.ip_in_range_cidr($e.principal.ip, %<data_table_name>.<column_name>)

re.regex($e.principal.hostname, %<data_table_name>.<column_name>)

To use a data table column of type CIDR or regular expression for column-based comparison, use the following syntax:

$e.principal.ip in cidr %cidr_data_table.column_name

$e.principal.hostname in regex %regex_data_table.column_name

If you specified the data type of the column as either CIDR or regular expression, exclude the keyword cidr or regex.

You can also use the not operator with data tables. The following example query filters out entries where the IP addresses ($e.principal.ip) don't match any of the CIDR ranges listed in the benign_ip column in cidr_data_table:

not $e.principal.ip in %data_table.benign_ip

Use a data table as a multicolumn reference list

You can use a data table as a multicolumn reference list. While a reference list can access data in a single dimension (one column), data tables support multiple columns, letting you filter and access data across several dimensions.

You can link UDM events to a data table using the in keyword for column-based comparison, letting you match values in a specific UDM field against values in a single column of the data table.

In the following example, alerts are triggered for network connections that match with suspicious port and protocol combinations. The badApps data table contains two columns: port and protocol. The rule triggers when both values from a UDM event match a row in the data table.

events:
    $e.metadata.event_type = NETWORK_CONNECTION
    $e.security_result.action = ALLOW
    $e.target.asset.asset_id = $assetid
    
    // event port matches at least one value in table column port
    $e.target.port in %badApps.port
    
    // event IP matches at least 1 value in table column protocol
    $e.target.network.ip in %badApps.ip

match:
    $assetid over 1h

condition:
    $e

Join a data table with a UDM event or entity

You can link UDM events to a data table using equality operators (=, !=, >, >=, <, <=) to perform row-based comparisons. This approach lets you filter data by matching values from UDM events against rows in the data table. If you're using multiple comparisons statements, all of the fields must match on the same data table row.

The following example uses a data table column of string type. This YARA-L query checks whether a user login event matches a row in example_table by confirming that the user ID exists in the table. Both conditions must match on the same row in the data table for the rule to trigger.

// Check if a user exists in a data table and that the user is active for all user login events.

event:
  $e.metadata.event_type = USER_LOGIN
  $e.security_result.action = ALLOW
  $e.principal.user.userid = $userid

// Event must match at least one row in the table where the uid in the table
// row is the userid for the event and the active date in the same table
// row is before the event timestamp

  %example_table.uid = $userid
  $e.principal.hostname = %example_table.hostname

match:
  $userid over 1h

condition:
  $e

The following example illustrates how a data table and UDM event data work together. Based on the logic in the preceding YARA-L query, a user with user ID 32452 surfaces in the detection as the user's hostname from the system and matches the hostname in the data table.

Write results from YARA-L queries to data tables

You can write the results from YARA-L queries to a data table. Using this feature, you can create data tables from your Google SecOps data and use these tables to filter and enhance other data.

You can use the YARA-L query write syntax for the following:

• Define YARA-L syntax for writing query results to data tables.

• Use data tables for threat intelligence, incident response, and other security use cases.

• Ensure that the data is consistent with YARA-L conventions.

Write detections and alerts to data tables using YARA-L

You can use a YARA-L query to send detections and alerts to data tables.

Using the write_row function, you can overwrite a data table row with the matching key in the data table using the results from a rule. If the key is not found in the table, add a new row instead.

Specify the write_row function in the export section of a YARA-L query. Writing data to the data table must be the final action of the query. This ensures that the outcome variables are written to the data table.

export:
  %<data_table_name>.write_row(
  data_table_column_x_name: <value>,
  data_table_column_y_name: <value>,
  ...,
  ...,
  data_table_column_z_name: <value>
)
// depending on the key column(s), the rows will be updated for existing keys 
and appended for new keys

Modify a data table using YARA-L

The following shows how to modify a data table using YARA-L:

TableName: ip_user_domain_table (key columns for the primary key are defined at creation)

* indicates the primary key.

The following query captures unique combinations of principal.ip, principal.user.employee_id, and target.domain. It filters the results based on the prevalence of the target.domain:

events:
    $e.principal.ip = $principal_ip
    $e.principal.user.employee_id = $principal_user_employee_id
    $e.target.domain.name = $target_domain
    $e.target.domain.prevalence.day_count < 5

condition:
    $e

Query results:

Example: Use write_row to write query output to a data table

events:
  $e.principal.user.employee_id = $principal_user_employee_id
  $e.target.domain.name = $target_domain
  $e.target.domain.prevalence.day_count < 5

outcome:
  $hostname = $target_domain
  $principal_emp_id = $principal_user_employee_id
 
condition:
  $e

export:
  %ips_with_hostnames.write_row(
      employeeid:$principal_emp_id,
      hostname:$hostname,
  )

Example: Understanding write_row

In the following example, user and ip are used as primary keys. Each detection that persists in the detections table results in one evaluation of the function call in the export section of the query.

events:
    $e.metadata.event_type = "USER_LOGIN"
    all $e.security_result.action != "BLOCK"
    all $e.security_result.action != "UNKNOWN_ACTION"

    $user = $e.principal.user.userid
    $ip = $e.target.ip
    $ts = $e.metadata.event_timestamp.seconds

match:
    $user, $ip over 1h

outcome:
    $first_seen = min($ts)

condition:
    $e

export:
    %successful_logins.write_row(user:$user, ip:$ip)

Here is the event data:

metadata: {
  event_type: USER_LOGIN
  event_timestamp: { seconds: 1283299200 }
}
principal: {
  user: {
    userid: "charlie"
  }
}
target: {
  ip: ["192.0.2.135", "192.0.2.136"]
}
security_result: {
  action: ALLOW
}

The following detections are returned when this query is executed as a rule:

The data table contains the following:

The following search query illustrates the support offered in Search for writing scalar values to data tables.

events:
    $e.metadata.event_type = "NETWORK_CONNECTION"

export:
    %summary_table.write_row(col_name: &e.metadata.id)
        "Event_Count":$e.metadata.id, 
    )

Enrich entity graph with a data table

You can use data tables to add, remove, or replace the entities presented in entity graph from rules. Use functions in the rule setup section to indicate how the data table should be merged with, appended to, or used to remove entities from entity events referenced in the events section.

You can use the following rule template to modify an entity graph:

rule entity_graph_template {

  meta:
    ...

  setup:
    // import the data table into entity graph
    <enrichment_keyword> <join_condition>

  events:
    ...

  match:
    ...

  condition:
    ...
}

You can use the following YARA-L 2.0 functions to enhance entity graph with a data table:

• graph_override: Overwrite the rows in the entity graph that match the join condition with data from the data table.

  For example:

  [graph_override](?tab=t.0#heading=h.v0fps7eke1if)

• graph_append: Append the rows from the data table to the rows in the entity graph. The graph_append operation requires an array that includes a data table variable and an entity event variable rather than a join condition.

  In the following example, $g1 is the entity graph variable and example_table is the data table:

  graph_append [$g1, %example_table]

  For the append function, data tables should include the following columns to validate the entity:

  • start_time (mapped to metadata.interval.start_time.seconds)

  • end_time (mapped to metadata.interval.end_time.seconds)

  Data table columns cannot be mapped to metadata fields using the web interface. For append use cases, data tables need to be created using Chronicle API (https://cloud.google.com/chronicle/docs/reference/rest/v1alpha/projects.locations.instances.dataTables/create)

• graph_exclude: Remove the rows in the entity graph that match the join condition.

  For example:

  [graph_exclude](?tab=t.0#heading=h.o0qbb5paki6g)

The join condition must be an equality expression between the data table column and the entity graph field. For the graph_override and graph_exclude functions, the syntax to access a data table is as follows:

<data_table_name>.<column_name>

Any filter specified for the <entity_variable> in the event section is applied after its enhancement with the data table.

After the entity in the entity graph is enriched with the entity in the data table, the entity variable in the entity graph must be joined to the UDM entity.

Override entity graph with data from data table

With the graph_override function, fields present in both the entity graph and the data table are replaced with fields from the data table. Fields present in the entity graph and not in the data table remain the same. Fields not present in the entity graph but present in the data table are included.

Only those columns of the data table that are mapped override the columns of the entity graph. The columns that are unmapped are added to the additional field of the entity graph on which the data table is joined.

Example: Match on single join

In the following example, the rows in the entity graph that match the join condition between the data table column and the entity graph field ($g1.graph.entity.ip = %example_table.my_ip) are overridden by the data table.

rule rule_override {
  meta:
    description = "Override entity context with data table before joining with UDM event"

  setup:
    //Rows in the entity graph that match the join condition are overridden by the data table
    graph_override ($g1.graph.entity.ip = %example_table.my_ip)

  events:
    $e.metadata.event_type = "NETWORK_CONNECTION"
    $e.security_result.action = "ALLOW"

    // Filter will be applied after graph is overridden by data table
    $g1.graph.entity.hostname = "ftp01"

    // Accessing unmapped columns
    $g1.graph.additional.fields["Owner"] = "alice"

    // Joining the UDM event with the enriched entity graph
    $e.target.ip = $iocip
    $g1.graph.entity.ip = $iocip

  match:
    $iocip over 1h

  condition:
    $e and $g1
}

To use an unmapped column (say "Owner") of the data table, then an equivalent statement for $g1.graph.entity.owner = "alice" is $g1.graph.additional.fields["Owner"] = "alice". This is because all unmapped columns of the data table go into the additional field of the entity graph ($g1).

The following tables illustrate an override operation where rows in the entity graph are enriched when the IP field in the data table matches the IP field in the entity graph.

Example: Match on multiple joins

In the following example, the rows in the entity graph that match the multiple join conditions ($g1.graph.entity.ip = %example_table.my_ip and $g1.graph.entity.hostname = %example_table.my_hostname) are overridden by the data table.

rule rule_override {
meta:
    description = "Override Entity context with Data Table before joining with UDM event"
setup:
  // example with more than one condition
  graph_override ($g1.graph.entity.ip = %example_table.my_ip and
  $g1.graph.entity.hostname = %example_table.my_hostname) 
events:
  $e.metadata.event_type = "NETWORK_CONNECTION"
  $e.security_result.action = "ALLOW"

  // Filter will be applied after graph is overridden by data table
  $g1.graph.entity.hostname = "ftp01"

  // joining the UDM event with the enriched entity graph
  $e.target.ip = $iocip
  $g1.graph.entity.ip = $iocip

match:
  $iocip over 1h

condition:
  $e and $g1
}

The following tables illustrate an override operation in which the rows of the entity graph are enriched when both the IP field and the hostname field in the data table match the IP field and the hostname field in the entity graph.

Append data from the data table to entity graph

With the graph_append function, no join condition is required.

In the following example, all rows in the data table are appended to the rows in the entity graph.

rule rule_append {
meta:
  description = "Data table append entity"
   
setup:
  graph_append [$g1, %example_table]

events:
    // filter UDM events
  $e.metadata.event_type = "NETWORK_CONNECTION"
  $e.security_result.action = "ALLOW"

  // Join the filtered UDM events with the enriched graph
  $e.target.ip = $iocip
  $g1.graph.entity.ip = $iocip

match:
  $iocip over 1h

condition:
  $e and $g1
}

The following example table illustrates an append operation where the rows of the data table are appended to the rows in the entity graph:

Use graph_exclude to remove rows from entity graph

With the graph_exclude function, rows in the entity graph that match the join condition are removed from the entity graph.

In the following example, all rows in the entity graph that match the given join condition (between the data table column and the entity graph field) are removed. No rows from the data table are added to the entity graph.

rule rule_exclude {

    meta:
    setup:
      graph_exclude ($g1.graph.entity.ip = %example_table.ip)

    events:
        $e.metadata.event_type = "NETWORK_CONNECTION"
        $e.security_result.action = "ALLOW"
        $e.target.ip = $iocip
        $g1.graph.entity.ip = $iocip

    match:
        $iocip over 1h

    condition:
        $e and $g1
}

The following tables illustrate an exclude operation in which the rows of the entity graph that match the IP field of the data table are removed:

Limitations

• The limits on the number of in statements when referencing a reference list in a query also apply to in statements in a data table.

• Only CSV file type is supported for uploads.

• Maximum size of a data table is 10 GB.

• Maximum aggregate limit of data volume across data tables in a tenant is 1 TB.

• Maximum number of in statements in a query, with or without special operators: 7

• Maximum number of in statements with the regex operator: 4

• Maximum number of in statements with the cidr operator: 2

• Placeholders are not allowed in the new setup section.

• Unmapped columns of a data table with data type set to string can only be joined with string fields of UDM event or UDM entity.

• Use only unmapped columns in a data table with a data type set to cidr or regex for CIDR or regular expression.

• You cannot map a data table's column to a repeated field.

Joins

• Unlike entities and UDM, data tables don't support placeholders. This means you cannot apply one set of filters to a data table, join it with a UDM entity and then apply a different set of filters to the same data table and while joining it with another UDM placeholder variable.

• For example, a data table named dt with 3 columns: my_hostname, org, and my_email and with the following rule:

events:
$e1.principal.hostname =  %dt.my_hostname
%dt.org ="hr"

$e2.principal.email =  %dt.my_email
%dt.org !="hr"

All the filters on a data table are applied first and then the filtered rows from the data table are joined with UDM. In this case, an empty data table is joined with e1 and e2 because the two filters on data table dt contradict each other (%dt.org ="hr" and %dt.org !="hr").

Use data tables with rules

The following limitations apply to data tables when used with rules.

Run frequency

Real-time run frequency is not supported for rules with data tables.

Output to data tables

• You can only export outcome variables to a data table. You cannot export event path or data table columns directly.

• Column lists must include the primary key columns for data tables.

• You can have no more than 20 outcomes.

• Data table columns don't support repeated values, so all outcome variables written to a data table must be singular values.

• If a data table doesn't exist, a new table is created with the default string data type for all columns, following the order specified.

• Only one rule can write to a data table at a time. If a rule tries to write to a data table that another rule is already writing to, the rule compilation fails.

• There is no guarantee that a producer rule can add rows to a data table before a consumer rule for that data table starts.

• A rule has a limit on the number of outcomes rows. A 10,000-row limit applies over the result and persisted data. The same limit applies to data tables; a single rule execution can output a maximum of 10,000 rows to a data table.

• If a row with the same primary key already exists in the data table, non-primary key columns will be replaced with the new values.

Entity enrichment from data tables

• You can apply only one enrichment operation (either override, append, or exclude) to a single entity graph variable.

• Each enrichment operation can be performed using only one data table.

• You can define a maximum of two enrichment operations of any type in the setup section of a YARA-L rule.

In the following example, an override operation is applied to the entity graph variable $g1 and an append operation is applied to the entity graph variable $g2.

    setup:
    graph_override($g1.graph.entity.user.userid = %table1.myids)
    graph_append [$g2, %table1]

In the preceding example, the same data table (table1) is used to enhance different entity graphs. You can also use different data tables to enhance the different entity graphs, as follows:

    setup:
    graph_override($g1.graph.entity.user.userid = %table1.myids)
    graph_append [$g2, %table2]

Use data tables with Search

The following limitations apply to data tables when used with Search.

• You can't run search queries on data tables using the Chronicle API. You can only run search queries from web interface.

• A single query execution can output a maximum of 1 million rows to a data table or 1 GB, whichever comes first.

• Search output to a data table skips event rows if they exceed 5 MB.

• Entity enrichment is not supported with Search.

• Data tables are not supported for customer-managed encryption keys (CMEK) customers.

• Writes are limited to 6 per minute per customer.

• API Support is not available.

• Statistics queries aren't supported with data table joins.

• Data table and data table join are only supported with UDM events and not with entities.

  Supported: %datatable1.column1 = %datatable2.column1 Not supported: graph.entity.hostname = %sample.test

• You cannot include a match variable in statistics query in the export section.

  For example, the following is not supported: none match: principal.hostname export: %sample.write_row( row: principal.hostname )

Need more help? Get answers from Community members and Google SecOps professionals.