Query Cookbook

Fundamentals

Match strings case-insensitively with LOWER()

String values vary in casing across customers and sources (open vs Open, Vulnerability vs vulnerability). Wrap both the column and the literal in LOWER() so a filter never silently misses rows:

-- Correct: case-insensitive
WHERE LOWER(statusCategory) = LOWER('open')
  AND LOWER(severity)       = LOWER('5-critical')

-- Fragile: matches only if the data is cased exactly like the literal
WHERE statusCategory = 'open' AND severity = '5-critical'

'open' here is a placeholder for whatever value your own statusCategory configuration actually produces, not a fixed Brinqa label. Confirm your values first with SELECT DISTINCT statusCategory; see statusCategory is a dimension, not a filter.

Never wrap a DATE, TIMESTAMP, or numeric column in LOWER(). It's unnecessary and defeats partition pruning on date columns. LOWER() is for strings only (severity, statusCategory, finding_type, entity_type, granularity, environment_name, and so on).

Count unique entities with COUNT(DISTINCT id)

v_findings and v_assets are pre-aggregated to one row per entity, so a plain COUNT(*) is correct on either view by itself:

-- Correct: v_findings is 1 row per finding, no join involved
SELECT COUNT(*) AS finding_count
FROM `gold_adm_views.v_findings`
WHERE LOWER(statusCategory) = LOWER('open')

Fan-out shows up once you join a base view to an association or bridge view. v_asset_owner, v_ticket_finding, v_finding_risk_factor, and similar M:N views add one row per linked entity. After that kind of join, count with DISTINCT to avoid overcounting:

-- Correct: unique findings after joining to a bridge view
SELECT COUNT(DISTINCT f.id) AS finding_count
FROM `gold_adm_views.v_findings` AS f
JOIN `gold_adm_views.v_finding_risk_factor` AS rf
  ON rf.finding_id = f.id
WHERE LOWER(f.statusCategory) = LOWER('open')

-- Wrong: overcounts once the join multiplies rows per risk factor
SELECT COUNT(*)
FROM `gold_adm_views.v_findings` AS f
JOIN `gold_adm_views.v_finding_risk_factor` AS rf
  ON rf.finding_id = f.id
WHERE LOWER(f.statusCategory) = LOWER('open')

Severity: string vs. severity_bucket integer

• On v_findings: severity is a string, 5-critical, 4-high, 3-medium, 2-low, 0-none. Match it case-insensitively.
• v_assets and v_tickets have no severity column. Use riskRating (same string tier scale as severity), riskScore (numeric), or complianceStatus instead.
• On trend and SLA views (v_findings_trend, v_findings_daily, v_mttr_sla, v_mttr_sla_by_env): severity is an integer severity_bucket, 5, 4, 3, 2, 0. Compare it directly, no quotes, no LOWER().

-- v_findings (string)
WHERE LOWER(severity) = LOWER('5-critical')

-- v_assets / v_tickets: no severity column, use riskRating instead
WHERE LOWER(riskRating) = LOWER('5-critical')

-- Trend / SLA view (integer)
WHERE severity_bucket = 5

See severity vs severity_bucket in Core concepts for the full tier list.

Filter by environment

An asset or finding can span several environments. Both v_findings and v_assets expose environment_ids / environment_names arrays plus a scalar environment_id FK (the first environment at the current snapshot). Only v_findings also has a scalar environment_name string; v_assets has no scalar environment name column, so always filter it via the array.

-- v_findings: scalar environment_name (single pick per finding)
SELECT id, displayName
FROM `gold_adm_views.v_findings`
WHERE LOWER(environment_name) = LOWER('Production deployment')

For v_assets, or to match any of a multi-environment entity's environments on either view, use array membership:

-- Assets in a given environment (array membership)
SELECT id, displayName
FROM `gold_adm_views.v_assets`
WHERE 'Production deployment' IN UNNEST(environment_names)

For a case-insensitive array match, UNNEST and LOWER() each element, same pattern as Filter by a value in an array below.

Handle NULLs

A NULL often carries meaning. On v_coverage_gaps, lastAssessed IS NULL means never scanned. Use IS NULL / COALESCE, and SAFE_DIVIDE to avoid divide-by-zero:

SELECT
    COUNTIF(lastAssessed IS NULL)      AS never_scanned,
    COUNTIF(lastAssessed IS NOT NULL)  AS scanned_but_stale
FROM `gold_adm_views.v_coverage_gaps`

---

Array columns (UNNEST)

v_findings and v_assets expose ARRAY<STRING> scanner columns: connectorNames, connectorCategories, dataIntegrationTitles. v_threat_intel and v_findings also expose threat-intel arrays (see Parallel arrays below).

Filter by a value in an array

-- Findings from a specific scanner
SELECT *
FROM `gold_adm_views.v_findings`
WHERE 'Rapid7 InsightVM' IN UNNEST(connectorNames)

For a case-insensitive array match, UNNEST and LOWER() each element: EXISTS (SELECT 1 FROM UNNEST(connectorNames) c WHERE LOWER(c) = LOWER('rapid7 insightvm')).

Count per array element

Cross-join the array into rows, then group:

-- Finding count by scanner connector
SELECT connector, COUNT(DISTINCT id) AS cnt
FROM `gold_adm_views.v_findings`, UNNEST(connectorNames) AS connector
GROUP BY 1
ORDER BY cnt DESC

Array length / multi-source

-- Assets discovered by 2+ scanners
SELECT displayName, asset_type, connectorNames
FROM `gold_adm_views.v_assets`
WHERE ARRAY_LENGTH(connectorNames) > 1

Parallel arrays: do not zip by position

The threat-intel rollups are parallel sets, not positionally paired. v_findings carries cwe_ids / cwe_uids / cwe_names and attack_technique_ids / attack_technique_uids / attack_technique_names. v_threat_intel carries all of those plus attack_tactic_* and attack_pattern_* (not present on v_findings). Element N of cwe_ids doesn't necessarily describe element N of cwe_names, so never assume you can index them together.

UNNEST a single family at a time:

-- Most common ATT&CK techniques across findings
SELECT technique, COUNT(*) AS finding_count
FROM `gold_adm_views.v_threat_intel`, UNNEST(attack_technique_names) AS technique
GROUP BY 1
ORDER BY finding_count DESC

If you genuinely need an id paired with its name, that pairing lives in the corresponding catalog view (for example v_cwe, v_attack_techniques). See Parallel Array Sets in the Core concepts glossary.

---

Aggregating the pre-aggregated views

Trend, summary, and SLA views are already grouped. When you roll them up further, for example across a date range, combine the raw components. Don't average pre-averaged numbers.

Additive measures (MTTR, SLA): sum the components

v_mttr_sla / v_mttr_sla_by_env carry the numerators and denominators precisely so a custom range sums correctly. Compute MTTR as SUM(days) / SUM(count), never AVG(avg_mttr_days):

-- MTTR and SLA attainment by severity (30-day window)
SELECT
    severity_bucket,
    ROUND(SAFE_DIVIDE(SUM(sum_mttr_days), SUM(cnt_mttr_days)), 1)        AS avg_mttr_days,
    ROUND(100 * SAFE_DIVIDE(SUM(within_sla_count), SUM(cnt_sla)), 1)     AS pct_within_sla
FROM `gold_adm_views.v_mttr_sla`
WHERE window_days = 30
GROUP BY 1
ORDER BY 1

Weighted averages (risk): weight by count

avg_risk_score on the summary/trend views is a per-group average. To combine groups, weight by finding_count rather than averaging the averages.

v_risk_summary is an environment cube: one row per environment, finding type, severity, and statusCategory. In the view's own words, "per-environment slices are exact, but summing finding_count across the environment dimension over-counts multi-environment findings." Keep environment_name in the GROUP BY so the rollup never sums across it:

-- Average open risk score by severity, per environment (correctly weighted)
SELECT
    environment_name,
    severity,
    SUM(finding_count) AS finding_count,
    ROUND(SUM(avg_risk_score * finding_count) / SUM(finding_count), 2) AS avg_risk_score
FROM `gold_adm_views.v_risk_summary`
WHERE LOWER(statusCategory) = LOWER('open')
GROUP BY 1, 2
ORDER BY 1, 2

For a single company-wide number with no environment dimension, aggregate v_findings directly instead (one row per finding, so no weighting is needed): SELECT severity, COUNT(DISTINCT id) AS finding_count, ROUND(AVG(riskScore), 2) AS avg_risk_score FROM \gold_adm_views.v_findings` WHERE LOWER(statusCategory) = LOWER('open') GROUP BY 1`.

This isn't the same metric as v_risk_summary.avg_risk_score: the two aggregate different source columns, and v_risk_summary excludes zero scores from its average while this query doesn't. Don't expect the two numbers to match.

Percentages

v_compliance_posture pre-computes category_rate (a 0.0-1.0 fraction). Multiply for a percentage; use SAFE_DIVIDE when computing your own ratios:

SELECT severity, ROUND(category_rate * 100, 1) AS closed_pct
FROM `gold_adm_views.v_compliance_posture`
WHERE LOWER(statusCategory) = LOWER('closed')

---

Time-travel and snapshots

Event history: state on/before a date (QUALIFY ROW_NUMBER())

v_finding_history and v_asset_history are append-only event logs, partitioned on event_date with a required partition filter: a query against either view without an event_date predicate fails outright rather than scanning the full history. Reconstruct the latest state on or before a target date by ranking each entity's events:

WITH snapshot AS (
    SELECT id, severity, statusCategory, riskScore, asset_name
    FROM `gold_adm_views.v_finding_history`
    WHERE event_date <= '2026-01-15'
      AND event_date >= DATE_SUB(DATE '2026-01-15', INTERVAL 1 YEAR)
    QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY event_date DESC) = 1
)
SELECT severity, COUNT(*) AS cnt
FROM snapshot
WHERE LOWER(statusCategory) = LOWER('open')
GROUP BY 1

Rules:

1. Always include an event_date filter, both a lower and upper bound. These views require a partition filter, so a query with no date predicate at all errors instead of running.
2. Never combine QUALIFY with aggregates in the same query level. Snapshot in a CTE, then aggregate in the outer query.
3. ROW_NUMBER() OVER (PARTITION BY id ORDER BY event_date DESC) = 1 picks each entity's latest event in the window.

Compare two points in time

WITH jan AS (
    SELECT id, severity FROM `gold_adm_views.v_finding_history`
    WHERE event_date <= '2026-01-01' AND event_date >= '2025-01-01'
    QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY event_date DESC) = 1
),
feb AS (
    SELECT id, severity FROM `gold_adm_views.v_finding_history`
    WHERE event_date <= '2026-02-01' AND event_date >= '2025-02-01'
    QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY event_date DESC) = 1
)
SELECT 'January' AS period, COUNT(*) AS total FROM jan
UNION ALL
SELECT 'February',          COUNT(*)          FROM feb

Bridge snapshots: the latest snapshot

v_finding_asset_history and v_asset_owner_history store a row per relationship per snapshot_date (a snapshot series, not an event log), also partitioned with a required partition filter on snapshot_date. A whole-table MAX(snapshot_date) subquery has no partition filter of its own, so it errors even nested inside a bounded outer WHERE. Instead, bound the scan with a literal snapshot_date predicate first, then pick the max within that window with QUALIFY:

-- Findings on assets as of the latest snapshot
SELECT finding_id, asset_id, severity, status
FROM `gold_adm_views.v_finding_asset_history`
WHERE snapshot_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
QUALIFY snapshot_date = MAX(snapshot_date) OVER ()

The WHERE bound must be a literal or session-constant expression, never a correlated subquery over the same table. That reintroduces the missing-partition-filter error.

---

Trends

Daily / weekly / monthly

v_findings_trend unifies all three granularities. Always filter granularity, and filter period_date to keep the scan bounded (the view isn't partitioned, so this is a cost control, not partition pruning):

SELECT period_date, SUM(finding_count) AS total
FROM `gold_adm_views.v_findings_trend`
WHERE LOWER(granularity) = LOWER('weekly')
  AND period_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
GROUP BY 1
ORDER BY 1

New vs. remediated (are we keeping pace?)

SELECT
    change_date,
    SUM(new_count) AS new_findings,
    SUM(status_transitioned_count) AS transitioned
FROM `gold_adm_views.v_changes_daily`
WHERE LOWER(entity_type) = LOWER('finding')
  AND change_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
GROUP BY 1
ORDER BY 1

---

Joining views

The views are pre-joined, so most questions need no join at all. v_findings already carries asset, owner, environment, and definition context. When you do need to combine, join a detail view back to v_findings on the finding id:

-- Open findings enriched with their CWE / ATT&CK threat intel
SELECT f.id, f.severity, f.asset_name, ti.cwe_names, ti.attack_technique_names
FROM `gold_adm_views.v_findings` AS f
JOIN `gold_adm_views.v_threat_intel` AS ti
  ON ti.finding_id = f.id
WHERE LOWER(f.statusCategory) = LOWER('open')

v_cve_details.finding_id joins to v_findings.id the same way.

---

Ranking: top-N per group

QUALIFY also ranks without a subquery. To keep the top 5 highest-risk findings per environment:

SELECT environment_name, displayName, riskScore
FROM `gold_adm_views.v_findings`
WHERE LOWER(statusCategory) = LOWER('open')
QUALIFY ROW_NUMBER() OVER (PARTITION BY environment_name ORDER BY riskScore DESC) <= 5

---

Tips

• Use LIMIT while exploring. BigQuery charges by data scanned, so a LIMIT keeps interactive queries cheap.
• LOWER() both sides for every string compare. Never LOWER() a DATE/TIMESTAMP/numeric column.
• Date-filter the history and snapshot views (v_finding_history, v_asset_history, v_finding_asset_history, v_asset_owner_history). These require a partition filter, so a query without one errors rather than running. v_changes_daily is partitioned on change_date too, so a change_date filter prunes the scan. v_findings_trend isn't partitioned; filtering period_date is still good practice for cost control, but it won't prune partitions.
• v_findings and v_assets are one row per entity; a plain COUNT(*) is correct there. Use COUNT(DISTINCT id) only after joining to an M:N/bridge view (v_asset_owner, v_ticket_finding, v_finding_risk_factor, and similar), which fans out.
• Use SAFE_DIVIDE(a, b) instead of a / b to avoid divide-by-zero.
• Backtick-qualify every table: `gold_adm_views.v_findings`.