|> SQL in Google SecOps

Google SecOps has launched into public preview SQL support for Search 🥳

In this post I’ll cover why Google SecOps is adding SQL support, what is GoogleSQL Pipe Syntax, aka PipedSQL, and then walk through side-by-side comparisons of YARA-L with SQL.

Pipe Syntax SQL in Google SecOps

⚠️ At the time of writing, September 2026, what is covered here is in Preview, and subject to change ahead of GA. These are my notes for my own learning, and if you do find any errors or have suggestions on better ways of doing things, please do leave feedback.

Below is the official documentation for SQL in SecOps, I highly recommend reading and bookmarking these sources.

Get started with GoogleSQL | Google Security Operations | Google Cloud Documentation

Translate YARA-L 2.0 Search queries to GoogleSQL | Google Security Operations | Google Cloud…

Query syntax | Google Security Operations | Google Cloud Documentation

The Backstory

In March 2019, Chronicle announced Backstory to the world. Originally built as a high-speed telemetry engine for EDR and NDR data, its initial search capabilities were limited to raw logs and atomic indicators like hostnames, users, IPs, and hashes. It had no structured search capability.

As Chronicle evolved into Security Operations (SecOps), structured querying finally arrived with UDM Search. Powered by YARA-L 2 (YL2), it allowed analysts to query the Unified Data Model (UDM) in a traditional SIEM-like experience.

Under the hood, however, UDM Search is powered by Google’s F1 database, a globally distributed relational database running GoogleSQL. UDM Search has always been a translation layer: you write YARA-L, but the engine compiles it down to SQL to run against the data.

Why SQL in Google SecOps?

Analyst Workflow

As Google’s own 2024 research paper bluntly stated:

“SQL’s syntactic structure… is terrible.”

SQL Has Problems. We Can Fix Them: Pipe Syntax In SQL

SQL has a fundamental challenge in that it is non-intuitive. It uses a non-linear sequence that forces your brain to jump around the page.

So while Standard GoogleSQL is now available in Google SecOps, the Pipe Syntax (PipedSQL) is perhaps the more interesting element for folks familiar with SPL or KQL as it provides the full power of SQL but with a sequencing that follows a chronological narrative.

Let’s take a standard SQL statement:

SELECT 
    c.country_code AS country,
    COUNT(o.order_id) AS total_orders,
    ROUND(AVG(o.order_value), 2) AS average_spend
FROM 
    customers c
INNER JOIN 
    orders o ON c.customer_id = o.customer_id
WHERE 
    o.order_date >= '2026-01-01'
    AND o.status = 'Completed'
GROUP BY 
    c.country_code
HAVING 
    COUNT(o.order_id) > 5;

If we think about the flow of SQL it reads as follows:

1. WHERE comes after FROM, but executes before SELECT.
2. HAVING comes after GROUP BY, but filters results you haven’t technically "seen" yet.
3. JOIN conditions reference tables declared ten lines ago.

Now compare this with PipedSQL and you write your query as you mean it:

-- 1. Declare the starting table/source first
FROM 
    customers AS c

-- 2. Join additional tables sequentially 
|> INNER JOIN 
    orders AS o ON c.customer_id = o.customer_id

-- 3. Filter raw rows immediately (WHERE executes early)
|> WHERE 
    o.order_date >= '2026-01-01'
    AND o.status = 'Completed'

-- 4. Group and Aggregate (combines GROUP BY & aggregate functions, e.g., COUNT, AVG)
--    The HAVING filter is directly integrated as the "WHERE" equivalent post-aggregation, 
--    or simply written sequentially.
|> AGGREGATE 
    COUNT(o.order_id) AS total_orders,
    ROUND(AVG(o.order_value), 2) AS average_spend
    GROUP BY c.country_code

-- 5. Filter the aggregated groups (HAVING is now just a standard WHERE after aggregation!)
|> WHERE 
    total_orders > 5

-- 6. Project and alias the final columns
|> SELECT 
    country_code AS country,
    total_orders,
    average_spend;

Comparing ANSI standard SQL and the new Google SQL Pipe syntax, and you can see the steps are in a more intuative manner:

| Step | What you Write (SQL) | What you Mean (Piped) |
|------|----------------------|-----------------------|
| 1    | SELECT (The Goal)    | FROM (The Source)     |
| 2    | FROM (The Source)    | WHERE (The Filter)    |
| 3    | WHERE (The Filter)   | AGGREGATE (The Logic) |
| 4    | GROUP BY (The Logic) | SELECT (The Goal)     |

Outlier Use Cases

Google SecOps has reached 95% (ish, maybe) search function parity with incumbent SIEMs like Splunk.

However, while YARA-L can now solve for the vast majority of search and detection use cases, supporting 71 functions, there are still scenarios where specific advanced functions are required, and may not be supported.

Note, there is a caveat that not all of the above functions may be usable in UDM Search.

But if we recall that UDM Search is a wrapper to SQL, if we look at Google SQL we’ll see it supports over 400 functions. And this is one major driver for exposing direct SQL access — there becomes a point of diminishing returns to keep adding wrappers over SQL into UDM, rather than just exposing the underlying functions themselves.

GenAI

LLMs have been trained on vast amounts of SQL over fifty years, compared to the opposite of vast exposure to proprietary languages like YARA-L. That training asymmetry translates directly into real-world agent reliability:

• Zero-shot accuracy: Models generate valid SQL out of the box with minimal few-shot prompting, avoiding the syntax hallucinations common with niche vendor query languages.
• Self-correction loops: When an agent hits a query error via the API, it can interpret GoogleSQL parser errors and self-correct its query on the next turn (YARA-L errors are often cryptic)
• Linear generation with Piped SQL: Piped syntax (|>) matches how LLMs reason. Instead of structuring queries inside-out with nested subqueries or CTEs, models can construct pipelines sequentially: filter, aggregate, extend, and sort.
• Unified tool calling: An agent investigating an incident can query events, correlate with graph or detections, and cross-reference enterprise BigQuery data using the exact same dialect.

YARA-L didn’t make the Stack Overflow’s cut this year

Technology | 2025 Stack Overflow Developer Survey

PipedSQL Example

If you’re a SPL or KQL user then PipedSQL is likely to feel more familiar than standard SQL. Let’s compare a YL2 query with SPL, KQL and then PipedSQL.

Google SecOps: UDM Search using YARA-L

YARA-L is strongest for single-event lookups or single stage aggregates. It reads logically from top-to-bottom, keeping the search criteria and the match conditions in a clean, concise block:

metadata.product_event_type = "4625"
$ip = principal.ip
$user = target.user.userid
match: $ip, $user
outcome: $login_attempts = count(metadata.id)
condition: $login_attempts > 10

Splunk SPL

index=windows EventCode=4625
| stats count by user, src_ip
| where count > 10

Microsoft KQL

SecurityEvent
| where EventID == 4625
| summarize count() by TargetUserName, IpAddress
| where count_ > 10

Google SecOps: UDM search using PipedSQL

Now let’s look at the same logic in PipedSQL syntax.

Notice first how the Pipe (|>) acts as a hand-off, moving the data through a chronological narrative from source to filter to aggregate.

FROM events
|> WHERE metadata.product_event_type = "4625"
|> JOIN UNNEST(principal.ip) AS ip
|> AGGREGATE COUNT(*) AS login_attempts GROUP BY target.user.userid, ip
|> WHERE login_attempts > 10

And as we’ll see later in the guide, unlike with standard SQL, when using PipedSQL you can simply omit any sequential line of the query and it will still work.

Choose your query language in UDM Search with the new language selector dropdown

Note, I am going to caveat here that depending on your keyboard layout and internationalization, the |> combination is not easy to type, but I’ll come back to that.

To get started make sure the Language filter is set to SQL in your UDM Search. You can see your saved or historical Searches from the UDM Search history filter, which supports SQL too.

Filter your historical UDM Searches for YL2 or SQL

Single Event UDM Search

Let’s take the simplest UDM row-level search and see how it translates into the world of GoogleSQL.

YL2 in UDM Search

metadata.event_type = "NETWORK_CONNECTION"

Standard GoogleSQL in UDM Search

SELECT *
FROM events
WHERE metadata.event_type = "NETWORK_CONNECTION"

The logic here is:

1. Declare the goal (SELECT)
2. Find the source (FROM)
3. Apply the filter (WHERE)

PipedSQL in UDM Search

And the equivalent statement in PipedSQL:

FROM events
|> WHERE metadata.event_type = "NETWORK_CONNECTION"
|> SELECT * --(Implicitly added if omitted)

The logic now flows chronologically:

1. Source (FROM)
2. Filter (WHERE)
3. Declare the goal (SELECT)

Limits and Field Selection

When we want to narrow our results to specific fields and a set number of rows in YARA-L you would do the following:

YARA-L

metadata.event_type = "NETWORK_CONNECTION"
limit: 10
select: principal.ip, principal.port, target.ip, target.port, network.ip_protocol

Standard Google SQL

And in Google SQL, we see the SELECT FROM WHERE pattern.

SELECT principal.ip, principal.port, target.ip, target.port, network.ip_protocol
FROM events
WHERE metadata.event_type = "NETWORK_CONNECTION"
LIMIT 10

PipedSQL

In PipedSQL we see the FROM WHERE SELECT pattern, but interestingly the LIMIT operator can be used in differing positions, with differing results, for example:

FROM events
|> WHERE metadata.event_type = "NETWORK_CONNECTION"
|> SELECT principal.ip, principal.port, target.ip, target.port, network.ip_protocol
|> LIMIT 10

will return different results to the following, which in effect returns 10 rows and then performs the filter, and this may return 0 results as your initial sample set of data does not include the subsequent filters.

FROM events
|> LIMIT 10
|> WHERE metadata.event_type = "NETWORK_CONNECTION"
|> SELECT principal.ip, principal.port, target.ip, target.port, network.ip_protocol

In YARA-L or Standard SQL, you have to remember to tack it onto the very end of a non-linear statement. With Piped SQL you have flexibility where you place the LIMIT.

Aggregate UDM Search

Aggregate Searches in YARA-L automatically handle the underlying complexity of SQL and UDM objects for you. Google SQL does not.

Let’s look at a YL2 aggregate search looking for network traffic on privileged ports:

YL2 in UDM Search

metadata.event_type = "NETWORK_CONNECTION"
target.port > 0 and target.port < 1024
match: principal.ip, target.ip, target.port, network.ip_protocol 
outcome: $total = count(metadata.id)
order: $total desc
limit: 10

Google SQL

If we port this directly to Standard SQL as follows, we hit an error:

-- This will throw an error!
SELECT principal.ip, target.ip, target.port, network.ip_protocol, COUNT(metadata.id) AS total
FROM events
WHERE metadata.event_type = "NETWORK_CONNECTION"
AND target.port > 0 and target.port < 1024
GROUP BY ALL

The Error: Column ip... has type ARRAY which cannot be used in a grouping key.

Notice the GROUP BY ALL? A newer feature of GoogleSQL that automatically groups every non-aggregated column in your SELECT list—no more copy-pasting 10 column names into your Group By clause

This shows the first major difference between YARA-L and SQL: Implicit vs. Explicit handling of Repeated Fields.

In UDM, an IP address is not a string but rather an array (list) because a host can have multiple IPs.

In SQL you cannot “Group By” a list.

Using the UDM Lookup feature in UDM Search to verify a UDM Object type

The solution here is to use the SQL UNNEST operator. If a UDM event has three IPs in the ip field UNNEST turns that one event into three separate rows so that SQL can treat each IP as a unique value:

| event_id | principal.ip (Array)              |
|----------|-----------------------------------|
| 101      | ["1.1.1.1", "2.2.2.2", "3.3.3.3"] |

After UNNEST, the data stream is flattened:

| event_id | principal_ip (Flattened) |
|----------|--------------------------|
| 101      | 1.1.1.1                  |
| 101      | 2.2.2.2                  |
| 101      | 3.3.3.3                  |

The working Google SQL query implementing the required UNNEST function looks like this:

SELECT
  principal_ip,
  target_ip,
  target.port,
  network.ip_protocol,
  COUNT(DISTINCT metadata.id) AS total
FROM events,
  UNNEST(principal.ip) AS principal_ip,
  UNNEST(target.ip) AS target_ip
WHERE metadata.event_type = "NETWORK_CONNECTION"
  AND target.port > 0 AND target.port < 1024
GROUP BY ALL
ORDER BY total DESC
LIMIT 10

Note: In YARA-L, you simply write outcome: $total = count(metadata.id), and UDM Search handles multi-value fields behind the scenes. In SQL, you have to be much more conscious of what UNNEST actually does to your result set.

When you UNNEST both principal.ip and target.ip in the same query, you create a Cartesian Product for each event's repeated fields. For example, if a single network connection event contains 2 principal IPs and 2 target IPs, UNNEST expands that single event into 4 distinct rows.

If you were to write COUNT(metadata.id) or COUNT(*), SQL would count every expanded row, meaning that single event would be counted 4 times, artificially inflating your metrics.

To count the actual number of original events, you can use COUNT(DISTINCT metadata.id) or APPROX_COUNT_DISTINCT(metadata.id) for faster approximate counts across larger timespans.

Piped SQL

And to complete the example, here is the equivalent statement in PipedSQL.

FROM events
|> WHERE metadata.event_type = "NETWORK_CONNECTION"
  AND target.port > 0
  AND target.port < 1024
|> CROSS JOIN UNNEST(principal.ip) AS principal_ip
|> CROSS JOIN UNNEST(target.ip) AS target_ip
|> AGGREGATE COUNT(DISTINCT metadata.id) AS total
  GROUP BY principal_ip, target_ip, target.port, network.ip_protocol
|> ORDER BY total DESC
|> LIMIT 10

In Standard SQL, you often use a comma-join (FROM events, UNNEST(...)) which implicitly creates a join. In PipedSQL, we make this explicit, which can be easier to read and debug.

Handling Extracted Fields

These concepts are important when working with extracted.fields (where SecOps stores "arbitrary" data that doesn’t fit into UDM). An easy mistake to make is trying to select the whole object.

Standard Google SQL

SELECT 
  f.value AS cvss_score
FROM 
  events,
  UNNEST(extracted.fields) AS f
WHERE 
  f.key = "about[0].asset.vulnerabilities[0].cvssBaseScore"
LIMIT 10

While this is valid in BigQuery, this will return an error in Google SecOps as follows:

Selecting a full proto column is not supported: cvss_score. Please select specific fields instead.

The correct way to access a map field is as follows:

Google SQL

SELECT f.key, f.value.string_value AS cvss_score
FROM events, UNNEST(extracted.fields) AS f
WHERE f.key = "about[0].asset.vulnerabilities[0].cvssBaseScore"

PipedSQL

FROM events
|> CROSS JOIN UNNEST(extracted.fields) AS f
|> WHERE f.key = "about[0].asset.vulnerabilities[0].cvssBaseScore"
|> SELECT f.key, f.value.string_value AS cvss_score
|> LIMIT 10

By explicitly calling f.value.string_value, you satisfy the SecOps requirement to only return flat data types (strings, ints, etc.) rather than complex database objects.

The Conditions Statement

In UDM Search condition provides a final, post-aggregation, filter. For example, to find any hosts with over 100 PROCESS_OPEN or FILE_MODIFICATION events, you would use a condition filter like this:

YL2 in UDM Search

metadata.event_type = "PROCESS_OPEN" or metadata.event_type = "FILE_MODIFICATION"
hostname != ""
match:  principal.asset.hostname, metadata.event_type
outcome: $event_count = count_distinct(metadata.id) 
condition: $event_count > 100
order: $event_count desc

Google SQL

In Google SQL the equivalent function is HAVING.

SELECT
  principal.asset.hostname AS hostname,
  metadata.event_type,
  APPROX_COUNT_DISTINCT(metadata.id) AS event_count
FROM
  events
WHERE
  metadata.event_type IN ('PROCESS_OPEN', 'FILE_MODIFICATION')
  AND TIMESTAMP_SECONDS(metadata.event_timestamp.seconds) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND principal.asset.hostname IS NOT NULL
GROUP BY
  1, 2 
HAVING
  event_count > 100
ORDER BY
  event_count DESC;

Piped SQL

When using PipedSQL however you no longer need useHAVING, instead you can use a standard WHERE statement:

FROM events
|> WHERE
  metadata.event_type IN ('PROCESS_OPEN', 'FILE_MODIFICATION')
  AND TIMESTAMP_SECONDS(metadata.event_timestamp.seconds) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND principal.asset.hostname != ""
  AND principal.asset.hostname IS NOT NULL
|> AGGREGATE APPROX_COUNT_DISTINCT(metadata.id) AS event_count
   GROUP BY principal.asset.hostname AS hostname, metadata.event_type
|> WHERE event_count > 100
|> ORDER BY event_count DESC

Why is this?

In PipedSQL, every pipe operator starts with a fresh table. In Standard SQL, WHERE and HAVING exist as two separate keywords because of a rigid, non-linear execution order:

1. FROM (Get the data)
2. WHERE (Filter the raw rows)
3. GROUP BY (Aggregate)
4. HAVING (Filter the aggregates)
5. SELECT (Pick the columns)

Because WHERE is processed before the aggregation happens, it physically cannot see the event_count. If you tried to put WHERE event_count > 100 in a standard query, the engine would throw an Unknown column error because, at that stage of execution the counts don't exist yet.

HAVING was essentially a syntax patch invented to solve this phase issue.

PipedSQL, however, is procedural. Each pipe (|>) represents a completed action. When you run |> AGGREGATE ... AS event_count, the data stream is transformed. The output of that pipe is a brand-new table where event_count is a real, physical column. Because the next pipe doesn't care about the history of the data, a standard |> WHERE can look at that new table and see the count just like any other field.

Multi-Stage Queries

A more recent addition to Google SecOps is the Multi-stage query — essentially the UDM equivalent of a SQL Sub-select or CTE. An implementation that blends the logic of YARA-L (declarative/event-based) with the logic of SQL Joins (relational/table-based).

YL2 Multi-stage in UDM Search

To find a login anomaly, you typically build a historical stage to calculate a baseline, and a current stage to compare against it. You then have to manually connect these stages together in a Root stage using variable assignments. While powerful, it is a lot of lines of syntax to have to type, and not immediately intuitive.

// STAGE 1: Calculates daily buckets
stage user_history {
  $e.metadata.event_type = "USER_LOGIN"
  $user = $e.principal.user.userid
  $user != ""

  // EXCLUDE TODAY: Only look at events older than 24 hours
  $e.metadata.event_timestamp.seconds < timestamp.current_seconds() - 86400

  match:
    $user by day
  outcome:
    $daily_total = count($e.metadata.id)
    $day_marker = min($e.metadata.event_timestamp.seconds)
}

// STAGE 2: Last 24 hours
stage user_today {
  $e.metadata.event_type = "USER_LOGIN"
  $e.metadata.event_timestamp.seconds >= timestamp.current_seconds() - 86400
  $user = $e.principal.user.userid

  match:
    $user
  outcome:
    $today_total = count($e.metadata.id)
}

// --- ROOT STAGE ---
$user = $user_history.user
$user = $user_today.user

match:
  $user

outcome:
  $avg_historical = avg($user_history.daily_total)
  $today = max($user_today.today_total)
  
  $baseline_days = count_distinct($user_history.day_marker)
  
  $ratio = max($user_today.today_total) / avg($user_history.daily_total)

condition:
  $avg_historical >= 1 and $ratio >= 3 and $baseline_days >= 5

order: $ratio desc

Google SQL

In Standard SQL, we move away from stages and into Common Table Expressions (CTEs). While this is more familiar to DBAs, it still requires a Self-Join to compare the two timeframes. This is where the SQL cognitive overload can occur — you have to manage multiple temporary tables just to calculate a simple ratio.

WITH daily_stats AS (
  SELECT
    principal.user.userid AS userid,
    -- We still need to group by day for the baseline
    TIMESTAMP_TRUNC(TIMESTAMP_SECONDS(metadata.event_timestamp.seconds), DAY) AS login_day,
    COUNT(*) AS daily_count
  FROM events
  WHERE metadata.event_type = "USER_LOGIN"
    AND principal.user.userid IS NOT NULL AND principal.user.userid  NOT IN ("", "-") 
    -- EXCLUDE the last 24 hours from the baseline
    AND metadata.event_timestamp.seconds < UNIX_SECONDS(CURRENT_TIMESTAMP()) - 86400
  GROUP BY 1, 2
),
current_activity AS (
  -- Isolate the last 24 hours specifically
  SELECT
    principal.user.userid AS userid,
    COUNT(*) AS today_count
  FROM events
  WHERE metadata.event_type = "USER_LOGIN"
    AND principal.user.userid IS NOT NULL AND principal.user.userid  NOT IN ("", "-") 
    AND metadata.event_timestamp.seconds >= UNIX_SECONDS(CURRENT_TIMESTAMP()) - 86400
  GROUP BY 1
)
SELECT 
  c.userid,
  c.today_count,
  stats.avg_historical,
  stats.baseline_days,
  (c.today_count / NULLIF(stats.avg_historical, 0)) AS ratio
FROM current_activity c
JOIN (
  -- Calculate historical stats from the CTE
  SELECT 
    userid, 
    AVG(daily_count) AS avg_historical,
    COUNT(login_day) AS baseline_days
  FROM daily_stats
  GROUP BY 1
) stats ON c.userid = stats.userid
WHERE stats.baseline_days >= 5
  AND stats.avg_historical > 1
  AND (c.today_count / NULLIF(stats.avg_historical, 0)) >= 3
ORDER BY ratio DESC;

Piped SQL

This is where PipedSQL shines. By using Window Functions and the EXTEND operator, we eliminate the need for Joins and reduce the query complexity.

Instead of building two tables and joining them together, we use a Global Window (MAX(login_day) OVER()). We then Extend our rows with historical averages and filter the results in a single, chronological flow.

FROM events
|> WHERE metadata.event_type = "USER_LOGIN"
   AND principal.user.userid NOT IN ("-", "")
|> AGGREGATE COUNT(*) AS daily_count
   GROUP BY LOWER(principal.user.userid) AS userid, 
            TIMESTAMP_TRUNC(TIMESTAMP_SECONDS(metadata.event_timestamp.seconds), DAY) AS login_day

-- 1. Calculate History
|> EXTEND 
     AVG(daily_count) OVER(PARTITION BY userid ORDER BY UNIX_SECONDS(login_day) RANGE BETWEEN 2592000 PRECEDING AND 86400 PRECEDING) AS avg_historical,
     COUNT(*) OVER(PARTITION BY userid ORDER BY UNIX_SECONDS(login_day) RANGE BETWEEN 2592000 PRECEDING AND 86400 PRECEDING) AS baseline_days,
     -- Find the max date in the WHOLE dataset to use as our "Today"
     MAX(login_day) OVER() AS latest_data_day

-- 2. Calculate Ratio
|> EXTEND SAFE_DIVIDE(daily_count, avg_historical) AS login_ratio

-- 3. Filter
|> WHERE login_day = latest_data_day  -- Dynamically matches the end of your data
   AND avg_historical > 1
   AND login_ratio >= 3
   AND baseline_days >= 5

|> ORDER BY login_ratio DESC

Normally, to find the latest date to use as a baseline, you’d have to run a separate sub-query or a join. By using OVER(), you are telling the engine: "Don't group these rows yet. Just scan the whole dataset, find the max value, and append that value to every single row in the stream."

| User    | Login Date | Global Max (Dataset-Aware) | Status       |
|---------|------------|----------------------------|--------------|
| Alice   | March 28   | March 29                   | Baseline Row |
| Bob     | March 29   | March 29                   | "Today" Row  |
| Charlie | March 27   | March 29                   | Baseline Row |

JOINS

Google SecOps supports Joins in UDM Search with YL2, enabling complex relational queries. Joins as a topic is broad and nuanced, but here we will focus on the join types available in YL2 and how they translate into GoogleSQL and PipedSQL.

INNER JOIN

An inner join only returns results where the join key exists in both data sources. If an event has no corresponding counterpart, it is omitted.

In YARA-L, you create an inner join either implicitly via a shared placeholder variable or explicitly via equality:

// Explicit inner join with a match window
$e1.metadata.event_type = "USER_LOGIN"
$e1.principal.ip = $ip

$e2.metadata.event_type = "NETWORK_CONNECTION"
$e2.target.ip = $ip

match:
  $ip over 5m
condition:
  $e1 and $e2

// Matchless (Raw Data Join)
$e1.metadata.event_type = "USER_LOGIN"
$e2.metadata.event_type = "NETWORK_CONNECTION"
$e1.principal.hostname = $e2.principal.hostname

• SQL Equivalent: FROM events e1 INNER JOIN events e2 ON e1.principal.ip = e2.target.ip

LEFT OUTER JOIN

Preserves all records from the left source ($e1). If the right source ($e2 or an entity/datatable) has no match, its fields return as null.

$e1.metadata.event_type = "NETWORK_CONNECTION"
$g1.graph.metadata.entity_type = "ASSET"

left join $e1.principal.asset.hostname = $g1.graph.entity.asset.hostname
$host = $e1.principal.asset.hostname

match:
  $host over 5m

• SQL Equivalent: FROM events e1 LEFT OUTER JOIN graph g1 ON e1.principal.hostname = g1.entity.hostname

RIGHT OUTER JOIN

Preserves all records from the right source ($e2). If the left source has no match, its fields return as null.

In traditional SQL, RIGHT JOIN is semantically equivalent to flipping the tables and writing a LEFT JOIN. However, in SecOps, the explicit keyword is provided to support flexible query ordering.

$e1.metadata.event_type = "USER_LOGIN"
$e2.metadata.event_type = "NETWORK_CONNECTION"

right join $e1.principal.hostname = $e2.principal.hostname
$host = $e2.principal.hostname

match:
  $host over 5m

• SQL Equivalent: FROM events e1 RIGHT OUTER JOIN events e2 ON e1.hostname = e2.hostname

CROSS JOIN

In single-stage search an unrestricted Cartesian product (𝑀 × 𝑁) is prohibited because pairing millions of events with millions of other events would crash the backend.

However, in Multi-Stage Search, Google SecOps explicitly added the cross join keyword:

cross join $stage_a, $stage_b

FULL OUTER JOIN

Not currently supported with YL2.

Apply joins in search and dashboards | Google Security Operations | Google Cloud Documentation

Correlate data using outer joins | Google Security Operations | Google Cloud Documentation

Joining Events to the Entity Context Graph (ECG)

Let’s look at a practical, real-world example of a LEFT JOIN: finding outbound network connections to Port 22 (SSH), deduplicating them by host, and enriching them with Asset metadata.

We want a LEFT JOIN: if a host connects to SSH but has not been enrolled or observed in the Asset Graph, we still want to see the network event, and we just expect the asset metadata to return as null.

YL2 Outer Join in UDM Search

To demonstrate that the LEFT JOIN is genuinely preserving events when no context exists, we can deliberately attempt to join observed hostnames against an entity type of USER, which should never match:

$e.metadata.event_type = "NETWORK_CONNECTION"
$e.target.port = 22
$e.principal.asset.hostname != ""
$g.graph.metadata.entity_type = "USER"
// Explicit LEFT JOIN preserves all network events even if missing from ECG
left join $e.principal.asset.hostname = $g.graph.entity.asset.hostname
// Anchor the match variable to the preserved (left) event
$host = $e.principal.asset.hostname
match:
  $host
outcome:
  $target_port = max($e.target.port)
  $source_type = array_distinct($g.graph.metadata.source_type)

Reviewing the results confirms this works as expected: we retrieve all SSH connection events, and the SOURCE_TYPE column is cleanly returned as empty (null).

Now, swapping the entity type back to ASSET gives us our production query:

$e.metadata.event_type = "NETWORK_CONNECTION"
$e.target.port = 22
$e.principal.asset.hostname != ""
$g.graph.metadata.entity_type = "ASSET"
// Explicit LEFT JOIN preserves all network events even if missing from ECG
left join $e.principal.asset.hostname = $g.graph.entity.asset.hostname
// Anchor the match variable to the preserved (left) event
$host = $e.principal.asset.hostname
match:
  $host
outcome:
  $target_port = max($e.target.port)
  $source_type = array_distinct($g.graph.metadata.source_type)

The results now display the connection events successfully enriched with their Entity Graph context (DERIVED_CONTEXT and/or ENTITY_CONTEXT).

LEFT JOIN using YL2

Google SQL using a CTE

Get Chris Martin (@thatsiemguy)’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Joining the high-volume event table directly to the entity graph table can result in slow queries if you don’t reduce the event stream first. To do this we pre-filter and deduplicate our telemetry and asset sets using Common Table Expressions (CTEs) before executing the LEFT JOIN.

WITH filtered_network AS (
  SELECT 
    DISTINCT principal.asset.hostname AS host,
    target.port AS target_port
  FROM events
  WHERE metadata.event_type = "NETWORK_CONNECTION"
    AND target.port = 22
    AND principal.asset.hostname != ""
),
target_assets AS (
  SELECT 
    entity.asset.hostname AS host,
    ARRAY_AGG(DISTINCT metadata.source_type) AS source_type
  FROM graph
  WHERE metadata.entity_type = "ASSET"
  GROUP BY 1
)
SELECT 
  n.host, 
  n.target_port, 
  a.source_type
FROM filtered_network n
LEFT JOIN target_assets a 
  ON n.host = a.host;

Notice that we use ARRAY_AGG(DISTINCT …) in the graph CTE so that multiple graph records for a host are collapsed into a single row, mirroring YL2’s array_distinct():

LEFT JOIN using Google SQL

PipedSQL

PipedSQL maintains a chronological narrative. We start with the event we care about, filter it, deduplicate it, and then bring in the pre-aggregated enrichment context exactly when it’s needed:

-- Stage 1: Filter and Deduplicate the Network Stream
FROM events
|> WHERE metadata.event_type = "NETWORK_CONNECTION"
     AND target.port = 22
     AND principal.asset.hostname != ""
|> AGGREGATE
   GROUP BY principal.asset.hostname AS host, target.port AS target_port

-- Stage 2: Pre-Aggregate the Asset Graph (1 row per asset)
|> LEFT JOIN (
     FROM graph
     |> WHERE metadata.entity_type = "ASSET"
     |> AGGREGATE ARRAY_AGG(DISTINCT metadata.source_type) AS source_type
        GROUP BY entity.asset.hostname AS graph_host
   ) AS a ON host = a.graph_host

-- Stage 3: Final Output Projection
|> SELECT host, target_port, a.source_type

LEFT JOIN using Google Piped SQL

Comparing the three, we can see the differences in how you interact with the data:

• YARA-L (Declarative): “Here are my variables. Now, find where they match.”
• Standard SQL (Set-Based): ”Here are my definitions. Now, join them into a result.”
• PipedSQL (Procedural): ”Take this data. Now do this. Now do this. Now show me.”

FILTERING Examples

Let’s explore common examples of how you would apply filtering in Google SQL and Piped SQL as compared to YL2.

String Matches

In UDM Search, you can match a partial string as a regular expression by using forward slashes:

YL2 in UDM Search

principal.application = /application\\msedge.exe/
limit: 10
select: principal.hostname, principal.application

Google SQL

In Standard SQL, you use the % wildcard with the LIKE function to represent any characters, as follows:

SELECT principal.hostname, principal.application
FROM events
WHERE principal.application LIKE '%application\\\\msedge.exe%'
LIMIT 10

• The first % means: “Match any text (of any length) before \application…”.
• The last % means: “Match any text (of any length) after …msedge.exe”.

Notice how we have to use four backslashes to represent a literal backslash in the file path — this is a SQL escaping requirement.

PipedSQL

In PipedSQL this is syntactically the same as GoogleSQL, with just the ordering of statements moved around

FROM events
|> WHERE principal.application LIKE '%application\\\\msedge.exe%'
|> SELECT principal.hostname, principal.application
|> LIMIT 10

Escape Sequences

Handling Windows file paths is one of the quickest ways to run into escaping headaches (you could remove the words file paths and escaping and that sentence is also true!).

If the target value on disk is: \device\harddiskvolume3\program files (x86)\microsoft\edge\application\msedge.exe then here is how you handle it in YARA-L vs. GoogleSQL:

YL2 in UDM Search

Using backticks (raw strings — no escaping required):

principal.application = `\device\harddiskvolume3\program files (x86)\microsoft\edge\application\msedge.exe`

Using double quoted strings:

principal.application = "\\device\\harddiskvolume3\\program files (x86)\\microsoft\\edge\\application\\msedge.exe"

Note: When using double-quoted strings in YARA-L, the backslash is an escape character (used for \” or \\). An unescaped sequence like \d is not a recognized escape sequence, causing the lexer to throw an error: compilation error parsing query: lexer error: token recognition error at: ‘“\d’ line: 1 column: 25–26 : invalid argument This is why you must either double every backslash (“\\device…”) or use backticks.

compilation error parsing query: lexer error: token recognition error at: '"\d'
line: 1 
column: 25-26 : invalid argument

Google SQL

If you only need an exact path match in GoogleSQL, you can use the = operator with a raw string (r"…”), which requires only single backslashes and no regex escaping:

SELECT principal.hostname, principal.application
FROM events
WHERE principal.application = r"\device\harddiskvolume3\program files (x86)\microsoft\edge\application\msedge.exe"
LIMIT 10

However, when performing pattern matching, the GoogleSQL equivalent of re.regex is REGEXP_CONTAINS. Because REGEXP_CONTAINS evaluates both the SQL string parser and the RE2 regex engine, standard quoted strings require quadruple escaping for backslashes and double escaping for parentheses:

SELECT principal.hostname, principal.application
FROM events
WHERE REGEXP_CONTAINS(principal.application, "\\\\device\\\\harddiskvolume3\\\\program files \\(x86\\)\\\\microsoft\\\\edge\\\\application\\\\msedge.exe")
LIMIT 10

Alternatively, using the Raw String (r) prefix tells the SQL parser to treat backslashes as literal characters, bypassing the first parsing layer and cutting your escaping work in half (down to 2 backslashes for path separators, and 1 backslash for regex parentheses):

SELECT principal.hostname, principal.application
FROM events
WHERE REGEXP_CONTAINS(principal.application, r"\\device\\harddiskvolume3\\program files \(x86\)\\microsoft\\edge\\application\\msedge.exe")
LIMIT 10

PipedSQL

With standard string escapes:

FROM events
|> WHERE REGEXP_CONTAINS(principal.application, "\\\\device\\\\harddiskvolume3\\\\program files \\(x86\\)\\\\microsoft\\\\edge\\\\application\\\\msedge.exe")
|> SELECT principal.hostname, principal.application
|> LIMIT 10

With Raw String (r) escapes:

FROM events
|> WHERE REGEXP_CONTAINS(principal.application, r"\\device\\harddiskvolume3\\program files \(x86\)\\microsoft\\edge\\application\\msedge.exe")
|> SELECT principal.hostname, principal.application
|> LIMIT 10

Regular Expressions

In UDM Search, regular expressions can be written either by enclosing the pattern in forward slashes:

YL2 in UDM Search

udm.principal.hostname = /win/ nocase

Or by calling the re.regex function:

re.regex(udm.principal.hostname, "win")

Google SQL

To perform equivalent matching in Google SQL you can use the REGEXP_CONTAINS function.

SELECT DISTINCT principal.hostname
FROM events
WHERE REGEXP_CONTAINS(principal.hostname,'win')

Piped SQL

FROM events
|> WHERE REGEXP_CONTAINS(principal.hostname, 'win')
|> SELECT principal.hostname
|> DISTINCT

CASE Statements

In YARA-L, you have the if() function which provides basic binary (IF/THEN/ELSE) logic. However, it does not support multiple conditions unless you nest it. While functional, nested if() statements are error-prone, hard to read, and difficult to maintain.

Furthermore, nested logic often introduces subtle classification bugs. In the example below, if network traffic is external-to-external (neither IP starts with 10.), the nested logic erroneously falls through to "INBOUND". Fixing that in YARA-L would require yet another level of nesting.

$e.metadata.event_type = "NETWORK_CONNECTION"
// YARA-L handles repeated IP arrays automatically
$src = $e.principal.ip
$dst = $e.target.ip
match:
  // Group by IPs over a 1-hour window
  $src, $dst over 1h
outcome:
  // Nested if: if(condition, then, else)
  $direction = array_distinct(
    if(re.regex($src, `^10\.`),
      if(re.regex($dst, `^10\.`), "LATERAL", "OUTBOUND"),
      "INBOUND"
    )
  )
  $total_bytes = sum($e.network.sent_bytes)
condition:
  $e

Use nested if statements for more complex logic | Google Security Operations | Google Cloud…

Google SQL

With Google SQL, you can use the CASE expression, which provides clean multi-branch conditional logic.

However, because standard SQL does not allow you to reuse column aliases within the same SELECT statement, you have to repeat the verbose principal.ip[SAFE_OFFSET(0)] and target.ip[SAFE_OFFSET(0)] expressions across every single WHEN branch:

SELECT
  principal.ip[SAFE_OFFSET(0)] AS src_ip,
  target.ip[SAFE_OFFSET(0)] AS dst_ip,
  CASE
    -- Logic: If both start with 10., it's internal lateral
    WHEN principal.ip[SAFE_OFFSET(0)] LIKE '10.%' AND target.ip[SAFE_OFFSET(0)] LIKE '10.%' THEN 'INTERNAL_LATERAL'
    WHEN principal.ip[SAFE_OFFSET(0)] LIKE '10.%' AND NOT target.ip[SAFE_OFFSET(0)] LIKE '10.%' THEN 'EGRESS_OUTBOUND'
    WHEN NOT principal.ip[SAFE_OFFSET(0)] LIKE '10.%' AND target.ip[SAFE_OFFSET(0)] LIKE '10.%' THEN 'INGRESS_INBOUND'
    ELSE 'EXTERNAL_OTHER'
  END AS traffic_direction,
  SUM(network.sent_bytes) AS total_bytes
FROM events
WHERE metadata.event_type = "NETWORK_CONNECTION"
  -- Only scan the last day to keep it snappy
  AND metadata.event_timestamp.seconds > UNIX_SECONDS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY))
GROUP BY 1, 2, 3
ORDER BY total_bytes DESC

Piped SQL

This is where PipedSQL’s procedural flow truly shines.

By chaining two |> EXTEND operators, we define src_ip and dst_ip once in the first pipe. The subsequent pipe can then immediately reference those clean alias names inside the CASE statement:

FROM events
|> WHERE metadata.event_type = "NETWORK_CONNECTION"
  -- Only scan the last day to keep it snappy
  AND metadata.event_timestamp.seconds > UNIX_SECONDS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY))

-- Step 1: Flatten primary IPs
|> EXTEND
    principal.ip[SAFE_OFFSET(0)] AS src_ip,
    target.ip[SAFE_OFFSET(0)] AS dst_ip

-- Step 2: Reference the new aliases directly in the CASE statement
|> EXTEND
    CASE
      WHEN src_ip LIKE '10.%' AND dst_ip LIKE '10.%' THEN 'INTERNAL_LATERAL'
      WHEN src_ip LIKE '10.%' THEN 'EGRESS_OUTBOUND'
      WHEN dst_ip LIKE '10.%' THEN 'INGRESS_INBOUND'
      ELSE 'EXTERNAL_OTHER'
    END AS traffic_direction

-- Step 3: Aggregate and order
|> AGGREGATE SUM(network.sent_bytes) AS total_bytes
  GROUP BY src_ip, dst_ip, traffic_direction
|> ORDER BY total_bytes DESC

Repeated Fields

How repeated fields are queried is one of the biggest shifts when moving between YARA-L and SQL:

• YARA-L: Uses an object/document mental model. Arrays are first-class citizens that are handled natively and transparently. You don’t have to flatten or normalize records to query them.
• SQL (GoogleSQL & PipedSQL): Uses a relational mental model. Tables are two-dimensional (rows and columns). To inspect individual array elements in SQL, you have to unnest (flatten) the array into pseudo-rows using UNNEST().

Take the following example:

SELECT metadata.id, target_ip
FROM events, UNNEST(target.ip) AS target_ip

Under the hood, a comma in SQL is an implicit CROSS JOIN (a Cartesian product).

• If an event has 2 target IPs, the event is multiplied by 2 (1 x 2 = 2 rows).
• If an event has no target IPs (the repeated field is empty []), UNNEST([]) returns 0 rows.
• In a CROSS JOIN, $1 x 0 = 0.

⚠️ A comma-join silently discards the entire event record if the repeated field is empty. If an authentication log, e.g., a cloud IAM login or API key usage, doesn’t record a target.ip, your query will drop that authentication event entirely from the results without any warning or error.

To preserve events where the array might be empty or unpopulated, you must use LEFT JOIN UNNEST(…). This guarantees that if the array is empty, the original event is retained, and the unnested column returns NULL.

In YARA-L, you do not need to unnest. If you don’t filter on target.ip, all events are preserved automatically:

$e.metadata.event_type = "USER_LOGIN"
// Matches if ANY element in the array is 10.0.0.1; does not require unnesting
$e.target.ip = "10.0.0.1"

In Standard SQL, we must explicitly choose LEFT JOIN UNNEST over the comma join:

-- Standard GoogleSQL
SELECT
  metadata.event_timestamp.seconds AS event_time,
  principal.user.userid AS user_id,
  target_ip
FROM events
-- Safe: Preserves the event with target_ip = NULL if target.ip is empty []
LEFT JOIN UNNEST(target.ip) AS target_ip
WHERE metadata.event_type = "USER_LOGIN"

If we used FROM events, UNNEST(target.ip) AS target_ip, every login lacking an IP would be silently dropped.

In PipedSQL because operations run top-to-bottom, we can filter the events first and then safely unnest the repeated field:

-- PipedSQL: Safe Unnesting with Linear Flow
FROM events
-- Stage 1: Filter the event stream first (avoids unnesting irrelevant events)
|> WHERE metadata.event_type = "USER_LOGIN"

-- Stage 2: Safely unnest target.ip while preserving events with empty arrays
|> LEFT JOIN UNNEST(target.ip) AS target_ip

-- Stage 3: Project the desired columns
|> SELECT
     metadata.event_timestamp.seconds AS event_time,
     principal.user.userid AS user_id,
     target_ip

If you only want to know if an array contains a specific value (without exploding the event into multiple rows), both GoogleSQL and PipedSQL allow checking array membership directly without a join:

-- Check membership without exploding rows
FROM events
|> WHERE metadata.event_type = "USER_LOGIN"
   AND "10.0.0.1" IN UNNEST(target.ip)
|> SELECT
     metadata.event_timestamp.seconds AS event_time,
     principal.user.userid AS user_id

Windowing

Another area where YL2 in UDM Search automatically handles a concept is Windowing.

Tumbling

• Keyword: by <duration>
• Fixed, non-overlapping, contiguous time buckets (e.g., 10:00–10:05, 10:05–10:10). Each event falls into exactly one slot

In SQL, tumbling windows are implemented by mathematically flooring each event’s timestamp to the start of its fixed interval, and then performing a standard GROUP BY.

For arbitrary time slices (e.g., 5 minutes, 10 minutes, 15 minutes), the canonical GoogleSQL pattern uses integer division (DIV) on epoch seconds:

• 5-minute window (300 seconds):

TIMESTAMP_SECONDS(DIV(metadata.event_timestamp.seconds, 300) * 300) AS window_start

• 10-minute window (600 seconds):

TIMESTAMP_SECONDS(DIV(metadata.event_timestamp.seconds, 600) * 600) AS window_start

• 1-hour window (3600 seconds):

TIMESTAMP_SECONDS(DIV(metadata.event_timestamp.seconds, 3600) * 3600) AS window_start

When grouping by standard calendar boundaries like whole hours, days, or weeks in UTC, GoogleSQL’s built-in TIMESTAMP_TRUNC is preferred:

• Tumbling Daily window (match: $var by 1d):

TIMESTAMP_TRUNC(TIMESTAMP_SECONDS(metadata.event_timestamp.seconds), DAY, 'UTC') AS log_date

• Tumbling Hourly window (match: $var by 1h):

TIMESTAMP_TRUNC(TIMESTAMP_SECONDS(metadata.event_timestamp.seconds), HOUR, 'UTC') AS window_start

Counting Failed Logins per 5-Minute Bucket

YL2

events:
  $e.metadata.event_type = "USER_LOGIN"
  $e.security_result.action = "BLOCK"
  $user = $e.principal.user.userid

match:
  // Non-overlapping 5-minute tumbling windows
  $user by 5m

outcome:
  $fail_count = count($e.metadata.id)

Google SQL

SELECT
  principal.user.userid AS user_id,
  TIMESTAMP_SECONDS(DIV(metadata.event_timestamp.seconds, 300) * 300) AS window_start,
  COUNT(DISTINCT metadata.id) AS fail_count
FROM events
JOIN UNNEST(security_result) AS sr
WHERE metadata.event_type = "USER_LOGIN"
  -- Check nested array for the BLOCK enum/string
  AND "BLOCK" IN UNNEST(sr.action)
  -- Replicate YARA-L's automatic zero-value filtering
  AND principal.user.userid != ""
GROUP BY user_id, window_start

Piped SQL

FROM events
|> WHERE metadata.event_type = "USER_LOGIN"
   -- Filter empty users upfront before doing work
   AND principal.user.userid != ""
   AND principal.user.userid IS NOT NULL
-- Unnest the repeated security_result record
|> JOIN UNNEST(security_result) AS sr
-- Check the nested action array
|> WHERE "BLOCK" IN UNNEST(sr.action)
-- Compute the 5-minute tumbling bucket
|> EXTEND
     TIMESTAMP_SECONDS(DIV(metadata.event_timestamp.seconds, 300) * 300) AS window_start
-- Aggregate unique logins per user and time window
|> AGGREGATE COUNT(DISTINCT metadata.id) AS fail_count
   GROUP BY principal.user.userid AS user_id, window_start

Sliding Windows

• Keyword: over <duration>
• Moving time intervals evaluated relative to events or rolling continuously. Captures bursts across boundaries.

Because sliding windows move continuously, simple GROUP BY window_start will not work. In SQL, you choose between two design patterns based on what you are trying to detect.

When you want to evaluate a rolling threshold across an event stream — such as “alert if a user experiences more than 5 failed logins within any 5-minute period” — use analytic window functions with RANGE BETWEEN.

In GoogleSQL, RANGE BETWEEN <offset> PRECEDING AND CURRENT ROW computes values over numeric ranges (like integer epoch seconds) without collapsing individual rows:

Google SQL

WITH login_bursts AS (
  SELECT
    metadata.event_timestamp.seconds AS event_time,
    target.user.userid AS user_id,
    -- Count events in the rolling 300 seconds up to and including this event
    COUNT(1) OVER (
      PARTITION BY target.user.userid
      ORDER BY metadata.event_timestamp.seconds
      RANGE BETWEEN 300 PRECEDING AND CURRENT ROW
    ) AS rolling_fails_5m
  FROM events
  WHERE metadata.event_type = "USER_LOGIN"
    -- Filter out empty usernames
    AND target.user.userid != ""
    AND target.user.userid IS NOT NULL
    -- Safely check double-nested array without duplicating event rows
    AND EXISTS (
      SELECT 1 
      FROM UNNEST(security_result) AS sr 
      WHERE "BLOCK" IN UNNEST(sr.action)
    )
)
SELECT
  TIMESTAMP_SECONDS(event_time) AS alert_time,
  user_id,
  rolling_fails_5m
FROM login_bursts
WHERE rolling_fails_5m >= 5

Piped SQL

FROM events
-- Stage 1: Filter event type, suppress zero-value users, and check nested array
|> WHERE metadata.event_type = "USER_LOGIN"
   AND target.user.userid != ""
   AND target.user.userid IS NOT NULL
   AND EXISTS (
     SELECT 1 
     FROM UNNEST(security_result) AS sr 
     WHERE "BLOCK" IN UNNEST(sr.action)
   )

-- Stage 2: Calculate the rolling 5-minute count per user inline
|> EXTEND
     COUNT(1) OVER (
       PARTITION BY target.user.userid
       ORDER BY metadata.event_timestamp.seconds
       RANGE BETWEEN 300 PRECEDING AND CURRENT ROW
     ) AS rolling_fails_5m

-- Stage 3: Filter directly on the computed window metric
|> WHERE rolling_fails_5m >= 5

-- Stage 4: Output clean alert fields
|> SELECT
     TIMESTAMP_SECONDS(metadata.event_timestamp.seconds) AS alert_time,
     target.user.userid AS user_id,
     rolling_fails_5m

When your detection requires correlating distinct events — such as “A failed login ($e1) followed by a successful login ($e2) for the same user within 5 minutes” — a self-join with an explicit timestamp delta check is required.

events:
  $e1.metadata.event_type = "USER_LOGIN"
  $e1.security_result.action = "BLOCK"
  $e1.principal.user.userid = $user

  $e2.metadata.event_type = "USER_LOGIN"
  $e2.security_result.action = "ALLOW"
  $e2.principal.user.userid = $user

match:
  // Sliding 5-minute correlation window
  $user over 5m

condition:
  $e1 and $e2

Google SQL

SELECT
  e1.principal.user.userid AS user_id,
  TIMESTAMP_SECONDS(e1.metadata.event_timestamp.seconds) AS fail_time,
  TIMESTAMP_SECONDS(e2.metadata.event_timestamp.seconds) AS success_time,
  (e2.metadata.event_timestamp.seconds - e1.metadata.event_timestamp.seconds) AS delta_seconds
FROM events AS e1
INNER JOIN events AS e2
  ON e1.principal.user.userid = e2.principal.user.userid
WHERE e1.metadata.event_type = "USER_LOGIN"
  AND e2.metadata.event_type = "USER_LOGIN"
  -- Crucial: prevent cross-joining thousands of empty user IDs
  AND e1.principal.user.userid != ""
  AND e1.principal.user.userid IS NOT NULL
  -- e1: check for blocked/failed login
  AND EXISTS (
    SELECT 1 
    FROM UNNEST(e1.security_result) AS sr 
    WHERE "BLOCK" IN UNNEST(sr.action)
  )
  -- e2: check for successful login
  AND EXISTS (
    SELECT 1 
    FROM UNNEST(e2.security_result) AS sr 
    WHERE "ALLOW" IN UNNEST(sr.action)
  )
  -- Sliding window condition: e2 occurs within 0 to 300 seconds after e1
  AND e2.metadata.event_timestamp.seconds >= e1.metadata.event_timestamp.seconds
  AND e2.metadata.event_timestamp.seconds <= e1.metadata.event_timestamp.seconds + 300

Piped SQL

-- Stream 1: Filter failed/blocked logins first
FROM events AS e1
|> WHERE metadata.event_type = "USER_LOGIN"
   AND principal.user.userid != ""
   AND principal.user.userid IS NOT NULL 
   AND EXISTS (
     SELECT 1 
     FROM UNNEST(security_result) AS sr 
     WHERE "BLOCK" IN UNNEST(sr.action)
   )

-- Stream 2: Join only with successful logins for the same user
|> INNER JOIN (
     FROM events
     |> WHERE metadata.event_type = "USER_LOGIN"
        AND principal.user.userid != ""
        AND EXISTS (
          SELECT 1 
          FROM UNNEST(security_result) AS sr 
          WHERE "ALLOW" IN UNNEST(sr.action)
        )
   ) AS e2
   ON e1.principal.user.userid = e2.principal.user.userid
   -- Sliding window: e2 occurs between 0 and 300 seconds after e1
   AND e2.metadata.event_timestamp.seconds >= e1.metadata.event_timestamp.seconds
   AND e2.metadata.event_timestamp.seconds <= e1.metadata.event_timestamp.seconds + 300

-- Stream 3: Project the result
|> SELECT
     e1.principal.user.userid AS user_id,
     TIMESTAMP_SECONDS(e1.metadata.event_timestamp.seconds) AS fail_time,
     TIMESTAMP_SECONDS(e2.metadata.event_timestamp.seconds) AS success_time,
     (e2.metadata.event_timestamp.seconds - e1.metadata.event_timestamp.seconds) AS delta_seconds

Why IS NOT NULL AND != ‘’ is Required in GROUP BY

The official documentation explicitly notes a subtle difference between YARA-L and SQL:

“In YARA-L, match variables in the match: section implicitly exclude events where the matched field is unset or empty. In GoogleSQL, GROUP BY treats NULL and empty strings as a distinct grouping key. To ensure exact parity with YARA-L, filter them out in the WHERE clause using AND field IS NOT NULL AND field != ‘’.”

In the UDM an unpopulated string field can exist in two distinct states:

1. NULL: The field was never set in the protobuf/JSON payload.
2. "" (Empty String): The field was explicitly set as an empty string (common in parser normalizations or default protobuf string values).

In SQL:

• user_id = '' matches empty strings.
• user_id IS NULL matches unset fields.

When you write WHERE user_id != '' in a WHERE clause:

• If user_id is "", "" != "" evaluates to FALSE → dropped.
• If user_id is NULL, NULL != '' evaluates to UNKNOWN → dropped.

In a simple WHERE clause user_id != "" drops both.

In YARA-L, both NULL (unset) and "" are considered zero-values and are discarded by the match: section. Explicitly checking both in SQL is how you guarantee 100% behavioral parity.

As a best practice with Google SQL use the following pattern:

WHERE principal.user.userid IS NOT NULL 
  AND principal.user.userid != ""

Limitations

As of September 2026, the SQL feature in Google SecOps is in Public Preview.

Supported Tables:

• events: Unified Data Model (UDM) event telemetry (principal.hostname, etc.)
• graph: Entity Context Graph context (assets, users, threat intelligence)
• detections: Historical alerts and detections (from both custom user-authored and curated Google rules)
• cases: Case and incident management data
• case_history: Audit history logs for cases
• <data_table_name>: Custom User-Defined Reference Tables (queried directly by table name, e.g. threat_intel_list)

Note, not all tables support SELECT * FROM at the time of writing. Further tables will likely be added as time goes on.

Interactivity:

• The ability to Drill Down from an aggregate count directly into the raw underlying rows is not available in Google SQL.

Function Coverage

• While the majority of GoogleSQL functions are implemented, not all functions are necessarily exposed. This is a combination of requirement, security, or performance considerations.

Other:

• No SELECT * with UNNEST: Because UDM records and arrays can be massive, you cannot run SELECT * FROM events, UNNEST(…). You must project specific columns when unnesting.
• No SELECT * in Multi-Table Joins: You cannot run SELECT * FROM events JOIN graph … or mix table wildcards with specific columns (e.g., SELECT events.*, graph.entity.hostname). You must explicitly select the projection columns from joined tables.
• Single Statement Only: No multiple queries separated by semicolons (no scripting/transactions).
• Hard Row Limit: All searches have a system-enforced cap of 1,000,000 rows.
• Raw Log Search: RLS is only available when the query languge is set to YL2. If you try raw in SQL language mode you’ll get an error Syntax error: Unexpected identifier “raw” [at 1:1] raw = /.*/ ^
• API Support: From a quick test, I couldn’t get this working with the official SecOps MCP, which isn’t to say it doesn’t work, but my Gemini started writing Python to query BigQuery directly. I added it into my own SDK and it works well, so API access is available.

Using Google SQL in SecOps via the API

Conclusion

In this post we covered an introduction to SQL in Google SecOps, and the more modern Piped SQL syntax.

For power users of SPL and KQL, the addition of SQL to Google SecOps should provide a more familiar query language.

For users building Agents, this is likely the more interesting area as Agentic capabilities with SQL are extremely strong, and token efficient. A topic for another blog.

And about the keyboard combination of |>. This is fine for a US keyboard, but on QWERTZ, AZERTY, and other layouts it is a pain, and you will need to work on creating a macro in your browser.

What does this mean for the future of YL2? I can’t authoritatively answer, but I understand the plan is that YL2 and SQL will both be supported. Personally, I am using SQL for my Agents in combination with some custom protos and skills. Ultimately, use which language you prefer.

It does open up challenging questions for SOC teams though, such as do we decide to build and write in YL2 or SQL going forward? I also can’t give an answer on that in a sentence (another good blog idea), but the good news is genAI helps here, it can seamlessly translate between YL2, GoogleSQL and PipedSQL.

Example SQL Queries

Below are some example SQL and Piped SQL queries for non event and graph tables that may help to get started.

Case

Google SQL

SELECT DISTINCT
  response_platform_info.response_platform_id AS Case_ID,
  display_name AS Case_Name,
  FORMAT_TIMESTAMP('%F %T ', TIMESTAMP_SECONDS(create_time.seconds)) AS Case_Creation_Time,
  stage AS Last_Stage,
  status AS Case_Status,
  CONCAT(last_modifying_user.given_name, ' ', last_modifying_user.family_name) AS Last_Handled_Analyst,
  closure_details.reason AS Case_Closed,
  closure_details.root_cause AS Root_Cause
FROM cases
ORDER BY Case_Creation_Time DESC;

PipedSQL

FROM cases
|> EXTEND
    FORMAT_TIMESTAMP('%F %T ', TIMESTAMP_SECONDS(create_time.seconds)) AS Case_Creation_Time,
    CONCAT(last_modifying_user.given_name, ' ', last_modifying_user.family_name) AS Last_Handled_Analyst
|> SELECT
    response_platform_info.response_platform_id AS Case_ID,
    display_name AS Case_Name,
    Case_Creation_Time,
    stage AS Last_Stage,
    status AS Case_Status,
    Last_Handled_Analyst,
    closure_details.reason AS Case_Closed,
    closure_details.root_cause AS Root_Cause
|> DISTINCT
|> ORDER BY Case_Creation_Time DESC;

Case History

Google SQL

SELECT
  name AS history_name,
  case_response_platform_info.case_id AS case_id,
  case_activity,
  TIMESTAMP_SECONDS(event_time.seconds) AS event_time,
  stage, 
  assignee.name AS assignee_name,
  priority,
  status,
  important,
  incident,
  sla_type
FROM case_history
ORDER BY event_time DESC
LIMIT 10;

Piped SQL

FROM case_history
|> EXTEND TIMESTAMP_SECONDS(event_time.seconds) AS event_time_sec
|> SELECT
    name AS history_name,
    case_response_platform_info.case_id AS case_id,
    case_activity,
    event_time_sec,
    stage,  -- Natively supported in SQL!
    assignee.name AS assignee_name,
    priority,
    status,
    important,
    incident,
    sla_type
|> ORDER BY event_time_sec DESC
|> LIMIT 10;

Detections

Google SQL

SELECT
  detection[SAFE_OFFSET(0)].rule_name AS rule_name,
  type,
  detection[SAFE_OFFSET(0)].rule_type AS rule_type,
  COUNT(DISTINCT id) AS count
FROM detections
GROUP BY 1, 2, 3
ORDER BY count DESC
LIMIT 10;

Piped SQL

FROM detections
|> AGGREGATE COUNT(DISTINCT id) AS count
   GROUP BY
     detection[SAFE_OFFSET(0)].rule_name AS rule_name,
     type,
     detection[SAFE_OFFSET(0)].rule_type AS rule_type
|> ORDER BY count DESC
|> LIMIT 10;

Data Tables

Replace with your Data Table name accordingly.

Google SQL

SELECT * FROM average_log_size

Piped SQL

FROM average_log_size
|> SELECT *