Translate YARA-L 2.0 Search queries to GoogleSQL

Supported in:

Google secops SIEM

Note: This feature is covered by Pre-GA Offerings 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 and the Google SecOps Service Specific Terms.
If this feature isn't visible in your environment, see Manage preview features or contact your system administrator.

This document helps security analysts translate Google Security Operations Unified Data Model (UDM) Search (YARA-L Search syntax) into GoogleSQL, supporting both standard SQL and pipe syntax variations.

Google SecOps Search is used for threat hunting, data exploration, and statistical analysis. Use the examples and mappings in this document to translate YARA-L search queries into GoogleSQL queries in the Google SecOps search interface.

Architectural concepts: YARA-L Search versus GoogleSQL

The following sections describe how YARA-L Search concepts map to GoogleSQL and help you write efficient GoogleSQL queries.

Schema: Events, Entity Graph, and detections

For Google SecOps, GoogleSQL uses simplified, logical table names that map to your security data lake:

• events: Contains Unified Data Model (UDM) event telemetry (for example, logins and process launches).
• graph: Contains Entity Graph context (assets, users, and threat intelligence).
• detections: Contains the historical record of alerts and detections generated by rules (used when searching for prior alerts).
• Data tables: User-defined lookup tables (for example, admin_users and threat_intel_list) queried directly by name.
• cases (or case): Contains case and incident management data.
• case_history: Contains audit history logs for cases.

YARA-L Search versus GoogleSQL Search comparison

When searching data in Google Security Operations using YARA-L 2.0, searches are classified along two main dimensions: data processing method and event scope.

• Data processing method:
  • Filter searches: Retrieve raw, granular Unified Data Model (UDM) events for point-in-time forensic lookups.
  • Statistical searches: Apply aggregations (such as counts and averages) that highlight trends, metrics, and anomalies.
• Event scope:
  • Single-event search: Monitors specific actions and frequencies within a single log source.
  • Multi-event search: Correlates disparate log streams across a defined time window to detect complex patterns.

Single-event search

• YARA-L Search: Written as a simple list of UDM filter expressions (for example, metadata.event_type = "USER_LOGIN"). No event variables (such as $e) are needed.
• GoogleSQL Search: Queries the events table using standard WHERE filter clauses. Like YARA-L single-event search, no event variables are used; fields are referenced directly using UDM path names (for example, SELECT * FROM events WHERE metadata.event_type = 'USER_LOGIN').

Multi-event search

• YARA-L Search: Event variables (such as $failed) are used to distinguish and correlate event types. Uses a match: block for grouping and time windowing (for example, $user over 5m) and a condition: block for thresholding (for example, #city > 1).
• GoogleSQL Search: Replaces YARA-L match: and condition: blocks with standard SQL grouping, timestamp bucketization, and aggregate functions.

Time windows: Tumbling versus sliding

YARA-L Search and GoogleSQL support tumbling (non-overlapping) and sliding (overlapping) time windows:

• YARA-L Search match::
  • Tumbling windows: Use the by keyword (for example, match: $user by 5m) for periodic statistical aggregation.
  • Sliding windows: Use the over keyword (for example, match: $user over 5m) for multi-event correlation across continuous time spans.
• GoogleSQL time windowing:
  • Tumbling windows: Achieved by bucketizing or truncating timestamps using integer division or TIMESTAMP_TRUNC:
    • 5-minute tumbling window: TIMESTAMP_SECONDS(DIV(metadata.event_timestamp.seconds, 300) * 300)
    • 10-minute tumbling window: TIMESTAMP_SECONDS(DIV(metadata.event_timestamp.seconds, 600) * 600)
    • Daily tumbling window: TIMESTAMP_TRUNC(TIMESTAMP_SECONDS(metadata.event_timestamp.seconds), DAY, 'UTC')
  • Sliding windows: In GoogleSQL Search queries, sliding windows are typically implemented by joining events on common identifiers and filtering the timestamp difference between events (for example, (L2.metadata.event_timestamp.seconds - L1.metadata.event_timestamp.seconds) <= 300), or by using analytic window functions (OVER (PARTITION BY ... ORDER BY ...)).

Repeated fields: UNNEST

UDM events contain many repeated fields (arrays). In GoogleSQL, you query repeated fields by using the UNNEST operator to flatten the array, or by checking membership with IN UNNEST or NOT IN UNNEST:

• Match any element in array:
  • YARA-L Search: principal.ip = "100.97.16.0"
  • GoogleSQL: "100.97.16.0" IN UNNEST(principal.ip)
• Match all elements in array:
  • YARA-L Search: all principal.ip != "100.97.16.0"
  • GoogleSQL: "100.97.16.0" NOT IN UNNEST(principal.ip)

Event variables and placeholder variables

In YARA-L, event and placeholder variables act as follows:

• Event variables: Represent the log stream or record itself (for example, $e1).
• Placeholder variables: Represent temporary aliases assigned to a specific field (for example, $user = $e1.principal.user.userid) and used to link multiple events together in the match: section.

In GoogleSQL, event and placeholder variables act as follows:

• Event variables: Translate to a table alias (for example, FROM MyEvents AS e1), establishing the overall namespace for the data you are querying.
• Placeholder variables: Translate to a column alias (for example, AS user_id) or a join key. For the best performance, apply your filters to the main event variable in the WHERE clause before you try to group or join your placeholder variables. This shrinks the dataset early and substantially reduces compute resources.

Single-event and multi-event queries

In YARA-L, single-event queries evaluate one log at a time (for example, finding a single failed login) and don't require a match section. Multi-event queries correlate multiple logs or group actions over time (for example, finding five failed logins by the same user in 10 minutes) using a match window such as over 10m or by 1h. Multi-event queries require both event variables and a match section.

In GoogleSQL, this distinction is handled by using standard filtering versus aggregations or joins:

• Single-event queries: Are easier to write and perform substantially better. They equate to a SELECT statement with a WHERE clause filtering individual rows. The query engine evaluates rows instantly and discards non-matching data without keeping intermediate state in memory.
• Multi-event queries: Are more complex and require more resources. In GoogleSQL, multi-event logic requires GROUP BY clauses, aggregations (such as COUNT() or SUM()), or JOIN operations to correlate events. This is computationally expensive because the query engine must scan, shuffle, and aggregate large volumes of data across memory buckets. If your time buckets are too large or your join keys are not specific enough, multi-event queries can overconsume resource quotas and cause timeouts.

Standard GoogleSQL versus Pipe GoogleSQL

Security analysis is like a water filtration system. You start with a large source of raw data, pass it through various filters in a specific order, and get your final results at the end.

• Pipe GoogleSQL (linear and natural): Follows the filtration model. You write the query in the exact order the data is processed:

  1. Start with your data.
  2. Filter raw events.
  3. Group and count.
  4. Filter the counts.

• Standard GoogleSQL (inside-out and reversed): You describe the final output at the top of the query (in the SELECT clause), even though it's the last step in the process. You need to review the full query to understand the flow.

Translation library

This section provides direct translations for YARA-L 2.0 UDM Search queries into standard and pipe GoogleSQL.

Single-event searches

In YARA-L 2.0, a single-event query finds individual events matching specific criteria or occurrences directly from ingested telemetry. You can use it to find specific, individual activities that match your criteria, or to run calculations—such as counting occurrences of an activity—without needing to correlate disparate event streams.

Example: Basic event filter

Goal and logic: Find all successful user logins. This is a simple point-in-time filter returning raw event details.

YARA-L Search:

metadata.event_type = "USER_LOGIN"

Standard GoogleSQL:

SELECT *
FROM events
WHERE metadata.event_type = 'USER_LOGIN';

Pipe GoogleSQL:

FROM events
|> WHERE metadata.event_type = 'USER_LOGIN';

In YARA-L Search, a single-event query consists of the raw filter expression. In GoogleSQL, this maps to a standard SELECT query on the events table with a WHERE filter. Pipe GoogleSQL begins directly with the data source (FROM events).

Example: Daily tumbling window aggregation by log type

Goal and logic: Group successful logins by log type into 1-day non-overlapping (tumbling) windows to identify daily login volumes per log source.

YARA-L Search:

metadata.event_type = "USER_LOGIN"
$log_type = metadata.log_type

match:
  $log_type by 1d

outcome:
  $login_count = count(metadata.id)

order:
  $log_type asc

Standard GoogleSQL:

SELECT
  TIMESTAMP_TRUNC(TIMESTAMP_SECONDS(metadata.event_timestamp.seconds), DAY, 'UTC') AS log_date,
  metadata.log_type AS log_type,
  COUNT(*) AS login_count
FROM events 
WHERE metadata.event_type = 'USER_LOGIN' 
GROUP BY log_date, log_type 
ORDER BY log_date ASC, log_type ASC;

Pipe GoogleSQL:

FROM events
|> WHERE metadata.event_type = 'USER_LOGIN'
|> EXTEND TIMESTAMP_TRUNC(TIMESTAMP_SECONDS(metadata.event_timestamp.seconds), DAY, 'UTC') AS log_date
|> AGGREGATE COUNT(*) AS login_count
   GROUP BY log_date, metadata.log_type AS log_type
|> ORDER BY log_date ASC, log_type ASC;

The YARA-L Search match: $log_type by 1d specifies a 1-day (24-hour) tumbling window. In GoogleSQL, you replicate this by truncating the timestamp to the selected interval (DAY). Pipe GoogleSQL's EXTEND operator lets you define this log_date column cleanly and inline before performing the aggregation and grouping.

Querying and tuning

These examples show how to use regular expressions and negation to filter search results.

Example: Exclusion-based process hunt

Goal and logic: Hunt for process masquerading by finding process launches for svchost.exe that didn't originate from the standard `\Windows\System32` directory.

YARA-L Search:

metadata.event_type = "PROCESS_LAUNCH"
re.regex(principal.process.command_line, `\bsvchost(\.exe)?\b`) nocase
not re.regex(principal.process.command_line, `\\Windows\\System32\\`) nocase

Standard GoogleSQL:

SELECT *
FROM events
WHERE metadata.event_type = 'PROCESS_LAUNCH'
  -- Case-insensitive regex match (?i)
  AND REGEXP_CONTAINS(principal.process.command_line, r'(?i)\bsvchost(\.exe)?\b')
  -- Negation of the exclusion path
  AND NOT REGEXP_CONTAINS(principal.process.command_line, r'(?i)\\Windows\\System32\\');

Pipe GoogleSQL:

FROM events
|> WHERE metadata.event_type = 'PROCESS_LAUNCH'
|> WHERE REGEXP_CONTAINS(principal.process.command_line, r'(?i)\bsvchost(\.exe)?\b')
|> WHERE NOT REGEXP_CONTAINS(principal.process.command_line, r'(?i)\\Windows\\System32\\');

YARA-L's re.regex(...) nocase translates to REGEXP_CONTAINS with a (?i) prefix in GoogleSQL. Using raw strings (prefixed with r, such as r'(?i)...') eliminates redundant backslash escaping.

Repeated fields

The following queries demonstrate how to hunt across lists of data (arrays) within events.

Example: Suspicious login IP hunt

Goal and logic: Identify logins where every single IP in the source IP list (repeated field) is suspicious (not matching 100.97.16.0). The query flattens the repeated IP array to return a row for each suspicious IP, grouped by 5-minute windows.

YARA-L Search:

metadata.event_type = "USER_LOGIN"
all principal.ip != "100.97.16.0"
principal.ip = $ip

match:
  $ip by 5m

outcome:
  $event_count = count(metadata.id)

Standard GoogleSQL:

SELECT
  -- 5-minute tumbling window (300 seconds)
  TIMESTAMP_SECONDS(DIV(metadata.event_timestamp.seconds, 300) * 300) AS MyWindowStart,
  MyIp,
  COUNT(*) AS MyEventCount
FROM events,
  UNNEST(principal.ip) AS MyIp
WHERE metadata.event_type = 'USER_LOGIN'
  -- Quantified Predicate: Every IP in the array must satisfy != '100.97.16.0'
  AND '100.97.16.0' NOT IN UNNEST(principal.ip)
GROUP BY MyWindowStart, MyIp;

Pipe GoogleSQL:

FROM events AS MyEvent, UNNEST(MyEvent.principal.ip) AS MyIp
|> WHERE MyEvent.metadata.event_type = 'USER_LOGIN'
-- Quantified Predicate: Every IP in the array must satisfy != '100.97.16.0'
|> WHERE '100.97.16.0' NOT IN UNNEST(MyEvent.principal.ip)
-- Calculate the 5-minute tumbling window (300 seconds)
|> EXTEND TIMESTAMP_SECONDS(DIV(MyEvent.metadata.event_timestamp.seconds, 300) * 300) AS MyWindowStart
|> AGGREGATE COUNT(*) AS MyEventCount
   GROUP BY MyWindowStart, MyIp;

In GoogleSQL, YARA-L's all principal.ip != "X" translates directly to "X" NOT IN UNNEST(principal.ip). This is more efficient than standard SQL NOT EXISTS subqueries. Using UNNEST(principal.ip) AS MyIp in the FROM clause flattens the array so you can output and group by each individual IP address.

Example: User login and authentication failure aggregation

Goal and logic: Audit user authentication behavior and identify potential brute-force attacks, credential stuffing, or misconfigured service accounts by highlighting high-volume login activity and tracking authentication failures per user. This query ranks all users by their total login attempts from highest to lowest while tracking how many of those attempts failed.

YARA-L Search:

metadata.event_type = "USER_LOGIN"

match:
  target.user.userid

outcome:
  $total_logins = count(metadata.id)
  $failed_logins = sum(if(any security_result.action = "FAIL", 1, 0))

order:
  $total_logins desc

Standard GoogleSQL:

SELECT
  target.user.userid AS User_ID,
  COUNT(*) AS Total_Logins,
  COUNTIF('FAIL' IN UNNEST(security_result.action)) AS Failed_Logins
FROM events
WHERE metadata.event_type = 'USER_LOGIN'
  AND target.user.userid IS NOT NULL AND target.user.userid != ''
GROUP BY User_ID
ORDER BY Total_Logins DESC;

Pipe GoogleSQL:

FROM events
|> WHERE metadata.event_type = 'USER_LOGIN'
     AND target.user.userid IS NOT NULL AND target.user.userid != ''
|> AGGREGATE
     COUNT(*) AS Total_Logins,
     COUNTIF('FAIL' IN UNNEST(security_result.action)) AS Failed_Logins
   GROUP BY target.user.userid AS User_ID
|> ORDER BY Total_Logins DESC;

This query evaluates array membership inline within an aggregate calculation using COUNTIF('FAIL' IN UNNEST(security_result.action)), avoiding the need to join against an unnested array.

Note: 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 and exclude unassigned or empty user IDs, filter them out in the WHERE clause using AND target.user.userid IS NOT NULL AND target.user.userid != ''.

Multi-event correlation

Correlating different events or states over a time window.

Example: Geographically impossible travel or multi-city login detection

Goal and logic: Identify potential account compromise or credential sharing by detecting instances where the same user logs in from two different cities within a physically impossible or highly improbable timeframe (in this case, under 5 minutes).

YARA-L Search:

In YARA-L, multi-event correlation is supported by defining distinct event variables and correlating them using a shared placeholder variable in the events section, bound by a time window in the match section:

$login1.metadata.event_type = "USER_LOGIN"
$login1.principal.user.userid = $user
$login1.principal.location.city = $city1
$city1 != ""

$login2.metadata.event_type = "USER_LOGIN"
$login2.principal.user.userid = $user
$login2.principal.location.city = $city2
$city2 != ""

$city1 != $city2
$user != ""

$login2.metadata.event_timestamp.seconds > $login1.metadata.event_timestamp.seconds
$login2.metadata.event_timestamp.seconds - $login1.metadata.event_timestamp.seconds <= 300

Standard GoogleSQL:

SELECT
  L1.principal.user.userid AS user_id,
  L1.principal.location.city AS city1,
  L2.principal.location.city AS city2,
  TIMESTAMP_SECONDS(L1.metadata.event_timestamp.seconds) AS login_time1,
  TIMESTAMP_SECONDS(L2.metadata.event_timestamp.seconds) AS login_time2,
  (L2.metadata.event_timestamp.seconds - L1.metadata.event_timestamp.seconds) AS time_diff_seconds
FROM events AS L1
INNER JOIN events AS L2
  ON L1.principal.user.userid = L2.principal.user.userid
WHERE L1.metadata.event_type = 'USER_LOGIN'
  AND L2.metadata.event_type = 'USER_LOGIN'
  AND L1.principal.user.userid IS NOT NULL AND L1.principal.user.userid != ''
  AND L1.principal.location.city IS NOT NULL AND L1.principal.location.city != ''
  AND L2.principal.location.city IS NOT NULL AND L2.principal.location.city != ''
  AND L1.principal.location.city != L2.principal.location.city
  AND L2.metadata.event_timestamp.seconds > L1.metadata.event_timestamp.seconds
  AND (L2.metadata.event_timestamp.seconds - L1.metadata.event_timestamp.seconds) <= 300;

Pipe GoogleSQL:

FROM events AS L1
|> WHERE L1.metadata.event_type = 'USER_LOGIN'
     AND L1.principal.user.userid IS NOT NULL AND L1.principal.user.userid != ''
     AND L1.principal.location.city IS NOT NULL AND L1.principal.location.city != ''
|> INNER JOIN events AS L2
    ON L1.principal.user.userid = L2.principal.user.userid
    AND L2.metadata.event_type = 'USER_LOGIN'
    AND L2.principal.location.city IS NOT NULL AND L2.principal.location.city != ''
    AND L1.principal.location.city != L2.principal.location.city
    AND L2.metadata.event_timestamp.seconds > L1.metadata.event_timestamp.seconds
    AND (L2.metadata.event_timestamp.seconds - L1.metadata.event_timestamp.seconds) <= 300
|> SELECT
    L1.principal.user.userid AS user_id,
    L1.principal.location.city AS city1,
    L2.principal.location.city AS city2,
    TIMESTAMP_SECONDS(L1.metadata.event_timestamp.seconds) AS login_time1,
    TIMESTAMP_SECONDS(L2.metadata.event_timestamp.seconds) AS login_time2,
    (L2.metadata.event_timestamp.seconds - L1.metadata.event_timestamp.seconds) AS time_diff_seconds;

Logically, the YARA-L and SQL queries are equivalent. The SQL query cannot be a non-stats query because SELECT * over multiple tables is not permitted in Google SecOps SQL search.

Example: Rapid user creation and deletion hunt

Goal and logic: Hunt for "burner" account activity by correlating a user creation event with a user deletion event for the same username, occurring within 4 hours.

YARA-L Search:

$create.target.user.userid = $user
$create.metadata.event_type = "USER_CREATION"
$delete.target.user.userid = $user
$delete.metadata.event_type = "USER_DELETION"

match:
  $user over 4h before $delete

outcome:
  $earliest_create_time_seconds = min($create.metadata.event_timestamp.seconds)
  $latest_delete_time_seconds = max($delete.metadata.event_timestamp.seconds)

condition:
  $create and $delete

Standard GoogleSQL:

SELECT
  CreateEvent.target.user.userid AS MyUser,
  MIN(CreateEvent.metadata.event_timestamp.seconds) AS EarliestCreateTimeSeconds,
  MAX(DeleteEvent.metadata.event_timestamp.seconds) AS LatestDeleteTimeSeconds
FROM events AS CreateEvent
INNER JOIN events AS DeleteEvent
  ON CreateEvent.target.user.userid = DeleteEvent.target.user.userid
WHERE CreateEvent.metadata.event_type = 'USER_CREATION'
  AND DeleteEvent.metadata.event_type = 'USER_DELETION'
  AND CreateEvent.target.user.userid IS NOT NULL AND CreateEvent.target.user.userid != ''
  AND CreateEvent.metadata.event_timestamp.seconds <= DeleteEvent.metadata.event_timestamp.seconds
  AND (DeleteEvent.metadata.event_timestamp.seconds - CreateEvent.metadata.event_timestamp.seconds) <= 14400
GROUP BY MyUser;

Pipe GoogleSQL:

FROM events AS CreateEvent
|> WHERE metadata.event_type = 'USER_CREATION'
    AND target.user.userid IS NOT NULL AND target.user.userid != ''
|> INNER JOIN events AS DeleteEvent
    ON CreateEvent.target.user.userid = DeleteEvent.target.user.userid
    AND DeleteEvent.metadata.event_type = 'USER_DELETION'
    AND CreateEvent.metadata.event_timestamp.seconds <= DeleteEvent.metadata.event_timestamp.seconds
    AND (DeleteEvent.metadata.event_timestamp.seconds - CreateEvent.metadata.event_timestamp.seconds) <= 14400
|> AGGREGATE
    MIN(CreateEvent.metadata.event_timestamp.seconds) AS EarliestCreateTimeSeconds,
    MAX(DeleteEvent.metadata.event_timestamp.seconds) AS LatestDeleteTimeSeconds
   GROUP BY CreateEvent.target.user.userid AS MyUser;

Because this query correlates two distinct events (USER_CREATION and USER_DELETION), YARA-L Search uses event variables ($create and $delete) paired with a match ... before sliding window. In GoogleSQL, this requires a self-join of the events table on the user ID restricted to a 4-hour window. Always filter out empty user IDs (IS NOT NULL AND != '') prior to joining to prevent Cartesian join explosions on unassigned telemetry.

Example: Missing sequential events hunt

Goal and logic: Identify hosts that generated a log on firewall_1 but failed to generate a corresponding log on firewall_2 within the next 10 minutes, indicating a potential logging failure or network telemetry gap.

YARA-L Search:

$e1.metadata.product_name = "firewall_1"
$e1.principal.hostname = $host

$e2.metadata.product_name = "firewall_2"
$e2.principal.hostname = $host

match:
  $host over 10m after $e1

condition:
  $e1 and !$e2

Standard GoogleSQL:

SELECT
  E1.principal.hostname AS MyHost
FROM events AS E1
LEFT JOIN events AS E2
  ON E1.principal.hostname = E2.principal.hostname
  AND E2.metadata.product_name = 'firewall_2'
  AND E2.metadata.event_timestamp.seconds >= E1.metadata.event_timestamp.seconds
  AND (E2.metadata.event_timestamp.seconds - E1.metadata.event_timestamp.seconds) <= 600
WHERE E1.metadata.product_name = 'firewall_1'
  AND E1.principal.hostname IS NOT NULL AND E1.principal.hostname != ''
  AND E2.principal.hostname IS NULL
GROUP BY E1.principal.hostname;

Pipe GoogleSQL:

FROM events AS E1
|> WHERE metadata.product_name = 'firewall_1'
    AND principal.hostname IS NOT NULL AND principal.hostname != ''
|> LEFT JOIN events AS E2
  ON E1.principal.hostname = E2.principal.hostname
  AND E2.metadata.product_name = 'firewall_2'
  AND E2.metadata.event_timestamp.seconds >= E1.metadata.event_timestamp.seconds
  AND (E2.metadata.event_timestamp.seconds - E1.metadata.event_timestamp.seconds) <= 600
|> WHERE E2.principal.hostname IS NULL
|> AGGREGATE GROUP BY E1.principal.hostname AS MyHost;

Converting the non-existence correlation pattern (Event A followed by the absence of Event B) into SQL is best achieved using an anti-join pattern: a LEFT JOIN paired with a WHERE ... IS NULL filter.

Tip: While non-existence in SQL can also be expressed using a correlated NOT EXISTS subquery, correlated subqueries can incur substantial performance overhead in distributed query engines. Using a LEFT JOIN with a WHERE right_table.key IS NULL filter enables distributed hash joins to execute significantly faster while maintaining a clean, linear pipeline in Pipe GoogleSQL.

Complex multi-event and outcomes

The following query calculates aggregations across multiple events and applies threshold filtering.

Example: Brute force followed by successful login hunt

Goal and logic: Hunt for successful brute-force attacks by grouping login attempts by user and host into 10-minute windows and returning groups with 5 or more failures and 1 or more successes.

YARA-L Search:

$failed.metadata.event_type = "USER_LOGIN"
$failed.security_result.action = "FAIL"
$failed.target.user.userid = $user
$failed.principal.hostname = $hostname

$success.metadata.event_type = "USER_LOGIN"
$success.security_result.action = "ALLOW"
$success.target.user.userid = $user
$success.principal.hostname = $hostname

match:
  $user, $hostname by 10m

outcome:
  $failed_count = count_distinct($failed.metadata.id)

condition:
  #failed >= 5 and #success >= 1

Standard GoogleSQL:

SELECT
  target.user.userid AS MyUser,
  principal.hostname AS MyHost,
  -- 10-minute bucket (use 300 for 5-minute bucketization)
  TIMESTAMP_SECONDS(DIV(metadata.event_timestamp.seconds, 600) * 600) AS MyTimeBucket,
  COUNTIF('FAIL' IN UNNEST(security_result.action)) AS MyFailedCount,
  COUNTIF('ALLOW' IN UNNEST(security_result.action)) AS MySuccessCount
FROM events
WHERE metadata.event_type = 'USER_LOGIN'
  AND target.user.userid IS NOT NULL AND target.user.userid != ''
  AND principal.hostname IS NOT NULL AND principal.hostname != ''
GROUP BY MyUser, MyHost, MyTimeBucket
HAVING MyFailedCount >= 5 AND MySuccessCount >= 1;

Pipe GoogleSQL:

FROM events
|> WHERE metadata.event_type = 'USER_LOGIN'
|> WHERE target.user.userid IS NOT NULL AND target.user.userid != ''
|> WHERE principal.hostname IS NOT NULL AND principal.hostname != ''
|> AGGREGATE
    COUNTIF('FAIL' IN UNNEST(security_result.action)) AS MyFailedCount,
    COUNTIF('ALLOW' IN UNNEST(security_result.action)) AS MySuccessCount
  GROUP BY
    target.user.userid AS MyUser,
    principal.hostname AS MyHost,
    TIMESTAMP_SECONDS(DIV(metadata.event_timestamp.seconds, 600) * 600) AS MyTimeBucket
|> WHERE MyFailedCount >= 5 AND MySuccessCount >= 1;

For high-volume statistical hunts, avoid resource-intensive self-joins by grouping logins together and using conditional aggregation (COUNTIF). In GoogleSQL, COUNTIF is the most performant way to count specific states (FAIL versus ALLOW) in a single pass over tumbling time buckets. In Pipe GoogleSQL, the |> AGGREGATE operator delivers a fresh table directly into the downstream |> WHERE filter (replacing Standard SQL's HAVING clause). If exact sliding-window parity with YARA-L is required, an event-anchored self-join can be used instead at higher compute cost.

Multistage queries

A multistage query (or multi-phase hunt) is used when you need to chain multiple analysis phases. Instead of correlating events occurring at the same time, you feed the output of one query stage directly into the input of a subsequent stage. This is crucial for:

• Sequential detection: Finding Step A, then looking for Step B only if Step A occurred.
• Statistical anomaly detection (baselining): Calculating a historical baseline in Stage 1, and comparing recent activity against it in Stage 2.

YARA-L multistage concepts versus GoogleSQL

• Named stages: In YARA-L Search, stages are defined using stage <name> { ... } and must be declared before the root stage. In GoogleSQL:
  • Standard SQL: Maps to Common Table Expressions (CTEs) using the WITH clause.
  • Pipe SQL: Maps to a linear sequence of pipes, or a WITH clause for helper baselines.
• Referencing stage fields: YARA-L uses $<stage_name>.<variable>. GoogleSQL uses standard table or CTE aliases (alias.column).
• Window timestamps: YARA-L provides reserved fields such as $<stage_name>.window_start. In GoogleSQL, you explicitly project and group by your truncated timestamp alias (for example, window_start).
• Matchless joins: YARA-L correlates stages by assigning variables (for example, $join_host = $median.host). GoogleSQL uses standard JOIN ... ON ... syntax.

Example: Unusual network activity hunt

What the query achieves: Establishes a daily baseline of network traffic volume (bytes exchanged) between host and target pairs, and compares today's volume against that baseline. It calculates a Z-score (number of standard deviations away from the mean) to flag unusually high activity.

YARA-L Multistage Search:

// Stage 1: Calculate the total bytes exchanged per day by source and target
stage daily_stats {
  metadata.event_type = "NETWORK_CONNECTION"
  $source = principal.hostname
  $target = target.ip
  $source != ""
  $target != ""

  match:
    $source, $target by day

  outcome:
    $exchanged_bytes = sum(network.sent_bytes + network.received_bytes)
}

// Root Stage: Calculate the average per day and compare with today's bytes
$source = $daily_stats.source
$target = $daily_stats.target
$date = timestamp.get_date($daily_stats.window_start)

match:
  $source, $target

outcome:
  $today_bytes = sum(if($date = timestamp.get_date(timestamp.current_seconds()), cast.as_int($daily_stats.exchanged_bytes), 0))
  $average_bytes = window.avg($daily_stats.exchanged_bytes)
  $stddev_bytes = window.stddev($daily_stats.exchanged_bytes)
  $zscore = ($today_bytes - $average_bytes) / $stddev_bytes

order:
  $zscore desc

Standard GoogleSQL (CTEs):

WITH daily_stats AS (
  -- Stage 1: Aggregate bytes by Source, Target IP, and Day
  SELECT 
    principal.hostname AS source,
    target_ip AS target,
    -- Grouping by tumbling day bucket
    TIMESTAMP_TRUNC(TIMESTAMP_SECONDS(metadata.event_timestamp.seconds), DAY, 'UTC') AS day_bucket,
    SUM(network.sent_bytes + network.received_bytes) AS exchanged_bytes
  FROM events
  -- Unnesting target.ip to group by a string rather than an array
  , UNNEST(target.ip) AS target_ip
  WHERE metadata.event_type = 'NETWORK_CONNECTION'
    AND principal.hostname IS NOT NULL AND principal.hostname != ''
    AND target_ip IS NOT NULL AND target_ip != ''
  GROUP BY 1, 2, 3
),
root_calculations AS (
  -- Root Stage Step A: Collapse daily stats per source/target pair
  SELECT
    source,
    target,
    SUM(
      IF(
        day_bucket = TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, 'UTC'),
        exchanged_bytes,
        0
      )
    ) AS today_bytes,
    AVG(exchanged_bytes) AS average_bytes,
    STDDEV_SAMP(exchanged_bytes) AS stddev_bytes
  FROM daily_stats
  GROUP BY 1, 2
)
-- Root Stage Step B: Calculate Z-Score and Order
SELECT 
  source, 
  target, 
  today_bytes, 
  average_bytes, 
  stddev_bytes,
  IF(
    stddev_bytes = 0, 
    0, 
    (today_bytes - average_bytes) / stddev_bytes
  ) AS zscore
FROM root_calculations
ORDER BY zscore DESC;

Pipe GoogleSQL:

FROM events
|> WHERE metadata.event_type = 'NETWORK_CONNECTION'
   AND principal.hostname IS NOT NULL AND principal.hostname != ''
-- Unnest target.ip to transform array elements into independent rows
|> JOIN UNNEST(target.ip) AS target_ip
|> WHERE target_ip IS NOT NULL AND target_ip != ''
-- Stage 1: Aggregate by Day
|> EXTEND TIMESTAMP_TRUNC(TIMESTAMP_SECONDS(metadata.event_timestamp.seconds), DAY, 'UTC') AS day_bucket
|> AGGREGATE 
     SUM(network.sent_bytes + network.received_bytes) AS exchanged_bytes
   GROUP BY principal.hostname AS source, target_ip AS target, day_bucket
-- Root Stage: Collapse per pair and evaluate statistics
|> AGGREGATE 
     SUM(IF(day_bucket = TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, 'UTC'), exchanged_bytes, 0)) AS today_bytes,
     AVG(exchanged_bytes) AS average_bytes,
     STDDEV_SAMP(exchanged_bytes) AS stddev_bytes
   GROUP BY source, target
|> EXTEND IF(stddev_bytes = 0, 0, (today_bytes - average_bytes) / stddev_bytes) AS zscore
|> ORDER BY zscore DESC;

In this Z-score query, YARA-L uses a named stage to group events by day and a root stage to collapse daily rows into host-target pairs, using window.avg to calculate statistics across days. In GoogleSQL, the initial aggregation groups by day_bucket, and the second aggregation groups only by source, target, calculating standard SQL AVG and STDDEV_SAMP metrics over daily sums. Pipe GoogleSQL simplifies this transition without requiring nested CTEs.

Example: Matchless join hunt

What the query achieves: Calculates the median bytes sent for every host and target pair to establish a baseline. It then joins this baseline back to the raw events to calculate how much each individual event deviates from that median, and finally aggregates these to find the Mean Absolute Deviation (MAD).

YARA-L Multistage Search:

// Stage 1: Calculate median bytes per host/target pair
stage median {
  metadata.event_type = "NETWORK_CONNECTION"
  $host = principal.hostname
  $target = target.ip[0]

  match:
    $host, $target

  outcome:
    $median = window.median(network.sent_bytes, true)
}

// Stage 2: Join raw events with the median baseline to calculate deviation
stage absolute_deviations {
  metadata.event_type = "NETWORK_CONNECTION"
  $join_host = principal.hostname
  $join_host = $median.host
  $join_target = target.ip[0]
  $join_target = $median.target

  outcome:
    $host = $join_host
    $target = $join_target
    $absolute_deviation = math.abs(network.sent_bytes - $median.median)
}

// Root Stage: Calculate the Mean Absolute Deviation (MAD)
$host = $absolute_deviations.host
$target = $absolute_deviations.target

match:
  $host, $target

outcome:
  $mean_absolute_deviation = avg($absolute_deviations.absolute_deviation)

Standard GoogleSQL (CTEs):

WITH PreparedEvents AS (
  -- Step 1: Filter events and extract first IP safely
  SELECT
    principal.hostname AS host,
    target.ip[SAFE_OFFSET(0)] AS target,
    network.sent_bytes
  FROM events
  WHERE metadata.event_type = 'NETWORK_CONNECTION'
),
RankedEvents AS (
  -- Step 2: Calculate Row Rank and Total Count per partition
  SELECT
    host,
    target,
    sent_bytes,
    ROW_NUMBER() OVER (PARTITION BY host, target ORDER BY sent_bytes ASC) as rn,
    COUNT(*) OVER (PARTITION BY host, target) as total_count
  FROM PreparedEvents
),
median_stage AS (
  -- Step 3: Isolate middle rows to evaluate median
  SELECT
    host,
    target,
    AVG(sent_bytes) AS median
  FROM RankedEvents
  WHERE rn IN (FLOOR((total_count + 1) / 2.0), CEIL((total_count + 1) / 2.0))
  GROUP BY 1, 2
),
absolute_deviations_stage AS (
  -- Step 4: Calculate Deviation
  SELECT
    u.host,
    u.target,
    ABS(u.sent_bytes - m.median) AS absolute_deviation
  FROM PreparedEvents AS u
  INNER JOIN median_stage AS m
    ON u.host = m.host AND u.target = m.target
)
-- Root Stage: Calculate MAD
SELECT
  host,
  target,
  AVG(absolute_deviation) AS mean_absolute_deviation
FROM absolute_deviations_stage
GROUP BY 1, 2;

Pipe GoogleSQL:

FROM events
|> WHERE metadata.event_type = 'NETWORK_CONNECTION'
|> EXTEND 
     principal.hostname AS host,
     target.ip[SAFE_OFFSET(0)] AS target_ip
-- Step 1: Determine rank and count
|> EXTEND 
     ROW_NUMBER() OVER (PARTITION BY host, target_ip ORDER BY network.sent_bytes ASC) as rn,
     COUNT(*) OVER (PARTITION BY host, target_ip) as total_count
-- Step 2: Establish Median analytically without losing row context
|> EXTEND 
     AVG(IF(rn IN (FLOOR((total_count + 1) / 2.0), CEIL((total_count + 1) / 2.0)), network.sent_bytes, NULL)) 
     OVER (PARTITION BY host, target_ip) as median
-- Step 3: Compute absolute deviation
|> EXTEND ABS(network.sent_bytes - median) AS absolute_deviation
-- Root Stage: Aggregate MAD
|> AGGREGATE AVG(absolute_deviation) AS mean_absolute_deviation
   GROUP BY host, target_ip;

This pattern demonstrates YARA-L's multi-stage capability performing a "matchless join," enriching raw events with pre-calculated baseline statistics (such as the median).

• Pipe GoogleSQL optimization: Instead of mimicking the multistage WITH clauses or expensive self-joins, Pipe GoogleSQL bypasses them entirely. It uses analytic window functions (OVER (PARTITION BY...)) inline through EXTEND to establish the median without losing individual event context. This is the most performant, linear way to approach statistical deviations.
• Standard GoogleSQL parity: Standard GoogleSQL emulation of YARA-L stages relies on CTEs (WITH clauses) to prepare and isolate intermediate stages, necessitating an explicit INNER JOIN to inject the aggregated statistics back into the raw events.
• Array safety note: YARA-L's target.ip[0] maps precisely to target.ip[SAFE_OFFSET(0)]. For higher-fidelity security analysis on repeated UDM fields (such as IP arrays), consider UNNEST instead of array offsets to capture deviations across all associated IPs.

Summary: Translation cheat sheet

YARA-L Search concept	Standard GoogleSQL equivalent	Pipe GoogleSQL equivalent (Pipe clauses)
field = "value"	field = 'value'	WHERE field = 'value'
re.regex($f, /pat/) nocase	REGEXP_CONTAINS(f, r'(?i)pat')	WHERE REGEXP_CONTAINS(f, r'(?i)pat')
match: $var over 5m	GROUP BY TIMESTAMP_SECONDS(DIV(..., 300)*300)	AGGREGATE ... GROUP BY window_start
condition: #failed >= 5	HAVING COUNTIF(failed_cond) >= 5	AGGREGATE COUNTIF(...) AS c ... then WHERE c >= 5
all repeated_field != "X"	"X" NOT IN UNNEST(rep)	WHERE "X" NOT IN UNNEST(rep)
any repeated_field = "X"	"X" IN UNNEST(rep)	WHERE "X" IN UNNEST(rep)
repeated_field = $val	CROSS JOIN UNNEST(rep) AS val	|> JOIN UNNEST(rep) AS val
$e1 and !$e2 (non-existence)	LEFT JOIN ... WHERE e2.key IS NULL	|> LEFT JOIN ... |> WHERE e2.key IS NULL

What's next

For more information about GoogleSQL, YARA-L, and query optimization in Google Security Operations, see the following:

• To learn the fundamentals and structure of GoogleSQL, see Get started with GoogleSQL.
• To write efficient queries and optimize performance, see GoogleSQL query performance best practices.
• To learn more about YARA-L 2.0 syntax and rules, see Get started with YARA-L.
• To learn more about building multi-stage searches in YARA-L, see Create multi-stage queries.
• To look up Unified Data Model fields and columns, see the UDM field dictionary reference.

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