# Get started with GoogleSQL

Supported in:

> [!NOTE]
> **Note:** This feature is covered by [Pre-GA Offerings Terms](https://chronicle.security/legal/service-terms/) of the Google Security Operations Service Specific Terms. Pre-GA features might have limited support, and changes to pre-GA features might not be compatible with other pre-GA versions. For more information, see the [Google SecOps Technical Support Service guidelines](https://chronicle.security/legal/technical-support-services-guidelines/) and the [Google SecOps Service Specific Terms](https://chronicle.security/legal/service-terms/).  
> If this feature isn't visible in your environment, see [Manage preview features](https://docs.cloud.google.com/chronicle/docs/secops/preview-features-manage) or contact your system administrator.

This document helps security analysts, threat hunters, and detection engineers get started with querying data using GoogleSQL in Google Security Operations, which supports both Standard SQL and [Pipe SQL syntax](https://docs.cloud.google.com/chronicle/docs/reference/standard-sql/pipe-syntax).

GoogleSQL provides an alternative to YARA-L 2.0. You can use GoogleSQL to perform ad hoc investigations, statistical aggregations, and complex data analysis on your security telemetry.

## GoogleSQL capabilities

GoogleSQL follows a declarative query structure, which is composable and highly adaptable for security analytics. Unlike YARA-L 2.0, which is optimized for streaming detection rules and multi-event correlations, GoogleSQL excels at broad, complex data exploration and deep-dive analysis.

### Structural differences

GoogleSQL uses standard ANSI-compliant clauses to define *what* data to retrieve rather than specifying a step-by-step sequence of execution.

The following table compares the structural differences between YARA-L 2.0 and GoogleSQL:

| Feature | YARA-L 2.0 | GoogleSQL |
|---|---|---|
| **Query structure** | Section-bound: requires a specific logical flow (`events`, `match`, `outcome`, `condition`). | Declarative: uses clauses to define *what* data to retrieve, not the sequence of execution. |
| **Core components** | Sections: `events`, `match`, `outcome`, `condition`. | Clauses: `FROM`, `WHERE`, `GROUP BY`, `HAVING`, `SELECT`, `ORDER BY`, `LIMIT`. |

## Before you begin

Verify the following prerequisites and settings to start querying with GoogleSQL in Google SecOps:

- **Required permissions** : You must have the standard Google SecOps Search [permissions and roles](https://docs.cloud.google.com/chronicle/docs/reference/feature-rbac-permissions-roles#search) assigned to your user account. If you use custom Identity and Access Management roles, make sure your custom role includes the `chronicle.queries.suggestSql` permission to support SQL search query auto-suggestions.
- **Access settings** : You can toggle between YARA-L 2.0 and GoogleSQL using the language selector in the [Query Editor](https://docs.cloud.google.com/chronicle/docs/investigation/udm-search#CollapsequeryEditor) user interface.
- **Telemetry tables (schema)** : You can query the following built-in tables:
  - **`events`** : Contains Unified Data Model (UDM) event data (for example, `principal.hostname`). See [UDM field dictionary reference](https://docs.cloud.google.com/chronicle/docs/reference/udm-field-list).
  - **`graph`** : Contains Entity Context Graph metadata (for example, `entity.hostname`). See [Entity Context Graph overview](https://docs.cloud.google.com/chronicle/docs/event-processing/entity-graph).
  - **`detections`** : Contains alerts and detections generated by threat detection rules (includes alerts and detections from both custom user-authored rules and curated Google-authored rules). See [Detections schema reference](https://docs.cloud.google.com/chronicle/docs/reference/sample-yaral-for-native-dashboard#detections-fields).
  - **`cases`** : Contains case and incident management data. See [Cases and alerts schema](https://docs.cloud.google.com/chronicle/docs/reference/soar-data-dashboard#cases-and-alerts).
  - **`case_history`** : Contains audit history logs for cases. See [Case history schema](https://docs.cloud.google.com/chronicle/docs/reference/soar-data-dashboard#case-history).
- **Data tables** : You can query custom, user-defined reference tables directly by table name (for example, `threat_intel_list`).
- **API integration** : You can run SQL queries programmatically by setting the `queryDialect` parameter to `SQL` in the `udmSearch` API call:

    queryDialect=SQL

## Query types and user interface behavior

The fields you select in your query affect the behavior of the query and the interactivity of the user interface as described in the following sections.

### Statistical queries for tabular results

When you project specific columns (for example, `principal.hostname` and `metadata.event_type`) or perform aggregations (such as `COUNT(*)`), the query returns a flat table optimized for summary data or counting. Although this lets you perform rapid statistical analysis, it disables interactive user interface features like the [Event Viewer](https://docs.cloud.google.com/chronicle/docs/investigation/udm-search#event-viewer) and [Timeline](https://docs.cloud.google.com/chronicle/docs/investigation/udm-search#timelines_chart) widgets.

Example of a statistical query follows:

    -- Generates a flat table with two specific fields
    SELECT 
        MyEvent.principal.hostname AS MyHost, 
        MyEvent.metadata.event_type AS MyAction 
    FROM events AS MyEvent

### Event queries for interactive investigation

Retrieving full structured UDM objects using `SELECT *` provides the complete context required to populate the interactive [Event Viewer](https://docs.cloud.google.com/chronicle/docs/investigation/udm-search#event-viewer) and [Timeline](https://docs.cloud.google.com/chronicle/docs/investigation/udm-search#timelines_chart) widgets in the Google SecOps interface.

Example of an event query follows:

    -- Enables interactive timeline and event viewer widgets
    SELECT * FROM events

## Recommended search strategies

Depending on your investigation goal, choose a query projection strategy that balances query performance with the level of detail required:

- **Statistical analysis**: choose specific field selection when your goal is to quickly aggregate information, summarize data points, or build tables for a dashboard.
- **Active investigations** : use `SELECT *` when you are hunting threats or analyzing a particular incident and need to review the raw logs or follow a chronology in the interactive timeline.

## Architecture and syntax options

GoogleSQL in Google SecOps supports two syntax styles for composing queries: Standard SQL and Pipe SQL.

### Standard SQL syntax

In declarative SQL, you begin your query with the `SELECT` clause:

    SELECT principal.hostname, target.hostname
    FROM events
    WHERE principal.hostname = "my-host"

### Pipe SQL syntax

In this alternative syntax, you begin with the data source and sequentially pipe data into operators using the `|>` operator:

    FROM events
    |> WHERE principal.hostname = "my-host"
    |> SELECT principal.hostname, target.hostname

### Choosing the syntax

Pipe SQL offers several benefits for your security investigations. It uses a linear mental model that flows sequentially. You start with your data source and apply transformations in a specific order: *Source ➔ Filter ➔ Aggregate ➔ Refine* . This progression helps you write, read, and debug complex queries. For more information, see [Pipe query syntax](https://docs.cloud.google.com/chronicle/docs/reference/standard-sql/pipe-syntax).

## Key workflows and best practices

This section describes key workflows and best practices to follow when writing queries.

### Repeated fields and `UNNEST`

Security and UDM datasets often store multiple values as an array (for example, `security_result` and `target.ip`). To filter, aggregate, or display elements from an array, flatten the array using the `UNNEST` operator.

When unnesting arrays, use `LEFT JOIN UNNEST()` to preserve rows where the array is empty or unpopulated. Using a comma join (`,`) or `INNER JOIN` with `UNNEST()` acts as a CROSS JOIN; if the array is empty, the entire event record is dropped from your results.

For example, consider a UDM event record for Workstation-01 where the `target.ip` array is empty:

| UDM field path | Value |
|---|---|
| `principal.hostname` | `"Workstation-01"` |
| `target.ip` | `[]` (Empty) |

The following query preserves the row. Workstation-01 appears in your results, and the `target_ip` column shows as `NULL`:

    -- ✅ "Workstation-01" appears in the results with a NULL target_ip
    SELECT principal.hostname, target.hostname, metadata.event_type, target_ip
    FROM events
    LEFT JOIN UNNEST(target.ip) AS target_ip;

In contrast, the following query uses an implicit CROSS JOIN. Because `target.ip` is empty for this event, the entire record for Workstation-01 is omitted from the results:

    -- ❌ "Workstation-01" will NOT appear in the results at all
    SELECT principal.hostname, target.hostname, metadata.event_type, target_ip
    FROM
      events,
      UNNEST(target.ip) AS target_ip;

#### Nested arrays

If an array contains nested arrays (for example, the `action` array inside the `security_result` array), you can directly unnest the nested array path without needing to chain multiple `UNNEST` operators:

    SELECT principal.hostname, target.hostname, metadata.event_type, action
    FROM events
    LEFT JOIN UNNEST(events.security_result.action) AS action
    WHERE action = "BLOCK"

> [!NOTE]
> **Note:** Unnesting repeated fields can output duplicate rows for a single event if that event contains multiple array elements. If your goal is only to check whether any instance of a repeated field satisfies a condition (without outputting duplicate rows), use the `ANY` pattern instead of an `UNNEST` join. For more information, see [Quantified comparison predicates](https://docs.cloud.google.com/chronicle/docs/reference/standard-sql/getting-started-googlesql#any-all-predicates).

### Quantified comparison predicates (`ANY` and `ALL`)

You can use quantified comparison predicates to compare a scalar value against elements in an array. This is an alternative to `IN` and `EXISTS` subqueries.

#### The `ANY` quantifier

This quantifier returns `TRUE` if the comparison is true for at least one element in the array:

    SELECT DISTINCT principal.hostname, target.hostname
    FROM events
    WHERE "BLOCK" = ANY UNNEST(security_result.action);

#### The `ALL` quantifier

This quantifier returns `TRUE` if the comparison is true for every element in the array.

> [!CAUTION]
> **Caution:** If the array is empty, the `ALL` predicate automatically returns `TRUE`. To avoid unexpected matches on empty arrays, always include a check to verify that the array length is greater than 0.

The following example shows the correct pattern for `ALL`:

    SELECT DISTINCT principal.hostname
    FROM events
    WHERE ARRAY_LENGTH(target.ip) > 0
      AND "127.0.0.1" = ALL UNNEST(target.ip);

### Join guidelines

You can join telemetry tables and user-defined data tables based on the following rules.

#### Filter with `WHERE EXISTS` and `WHERE ... IN` subqueries

You can use `SELECT *` with `WHERE EXISTS` or `WHERE ... IN` subqueries to filter rows in one table by matching records in another table. Each matching row from the outer table is returned once.

For example, the following query returns `events` rows whose target hostname appears in `graph`:

    SELECT *
    FROM events
    WHERE target.hostname IN (SELECT entity.hostname FROM graph)

Similarly, to return distinct `cases` that have matching `case_history` records (for example, cases with a priority change), use `WHERE EXISTS` or `WHERE ... IN`.

The following example uses `WHERE EXISTS` in Standard SQL:

    SELECT *
    FROM cases AS c
    WHERE EXISTS (
      SELECT 1
      FROM case_history AS h
      WHERE c.response_platform_info.response_platform_id = h.case_response_platform_info.case_id
        AND h.case_activity = "PRIORITY_CHANGE"
    )

The following example uses `WHERE EXISTS` in Pipe SQL:

    FROM cases AS c
    |> WHERE EXISTS (
      SELECT 1
      FROM case_history AS h
      WHERE c.response_platform_info.response_platform_id = h.case_response_platform_info.case_id
        AND h.case_activity = "PRIORITY_CHANGE"
    )

The following example uses `WHERE ... IN` in Standard SQL:

    SELECT *
    FROM cases AS c
    WHERE c.response_platform_info.response_platform_id IN (
      SELECT h.case_response_platform_info.case_id
      FROM case_history AS h
      WHERE h.case_activity = "PRIORITY_CHANGE"
    )

#### Select specific columns from joined tables

GoogleSQL supports selecting specific columns from `JOIN` queries:

    SELECT A.metadata.product_name, B.entity.hostname
    FROM events A
    JOIN graph B ON A.principal.hostname = B.entity.hostname

### Disallowed queries

You cannot combine wildcard selections (such as `A.*` or `*`) with explicit column projections in the same `SELECT` statement.

Example of a disallowed query is shown:

      -- ❌ ERROR: attempting to select all fields from table A alongside a specific field from table B
      SELECT
          MyEvents.*,
          MyGraph.entity.hostname
      FROM events AS MyEvents
      JOIN graph AS MyGraph
      ON MyEvents.principal.hostname = MyGraph.entity.hostname

- **`SELECT *` with UNNEST** : because UDM records and arrays can be wide, you cannot use `SELECT *` when flattening an array using `UNNEST`.

  Example of a disallowed query is shown:

      -- ❌ ERROR: attempting to select all columns while flattening the security_result array
      SELECT *
      FROM events,
      UNNEST(events.security_result) AS MySecurityResult
      WHERE MySecurityResult.action = 'BLOCK'

- **Wildcard selections in standard table joins** : you cannot use `SELECT *` for standard table joins. You must explicitly specify the columns you want to return from the joined tables.

  Example of a disallowed query is shown:

      -- ❌ ERROR: attempting a standard table join without specifying specific column projections
      SELECT *
      FROM events AS MyEvents
      JOIN graph AS MyGraph
      ON MyEvents.principal.user.userid = MyGraph.entity.user.userid

## Limitations and quotas

When writing queries in GoogleSQL, take note of these limitations:

- **Single statement only**: you can use only a single query statement. You can use CTEs (Common Table Expressions) and subqueries, but you cannot use multiple statements separated by semicolons.
- **Read-only** : you cannot use data modification commands (such as `INSERT`, `UPDATE`, `DELETE`) or data definition commands (such as `CREATE TABLE`...).
- **System-appended limit** : all searches have a limit of 1,000,000 rows. Any limit that you specify that exceeds this value is overridden. For more information, see [Search large result sets](https://docs.cloud.google.com/chronicle/docs/investigation/udm-search#search_large_result_sets).
- **`SELECT *` projections in joins and UNNEST** : you cannot use `SELECT *` for standard join queries or when you combine it with `UNNEST`. Explicitly specify the required projection columns instead.

## What's next

For more information about querying and analyzing data in Google SecOps, see the following:

- To look up Unified Data Model columns, see the [UDM field dictionary reference](https://docs.cloud.google.com/chronicle/docs/reference/udm-field-list).
- To learn about advanced search capabilities, see [Understand search](https://docs.cloud.google.com/chronicle/docs/investigation/udm-search).
- To learn more about pipe syntax in GoogleSQL, see [Pipe query syntax](https://docs.cloud.google.com/chronicle/docs/reference/standard-sql/pipe-syntax).

**Need more help?** [Get answers from Community members and Google SecOps professionals.](https://security.googlecloudcommunity.com/)