Get started with GoogleSQL

Supported in:

This document helps security analysts, threat hunters, and detection engineers get started with querying data using GoogleSQL in Google Security Operations, supporting both Standard SQL and Pipe SQL 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:

  • Access settings: You can toggle between YARA-L 2.0 and GoogleSQL using the language selector in the Query Editor user interface.
  • Telemetry tables (schema): You can query the following built-in tables:
  • 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 and Timeline 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 and Timeline 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.

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"

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.

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:

You can use SELECT * for WHERE ... IN subquery joins:

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

GoogleSQL supports selecting specific columns:

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.
  • 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:

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