Running Federated Search from Databricks

Run FSQL across every connected source and land OCSF-normalized rows in Unity Catalog

📘

TL;DR

run_fsql_query takes one FSQL statement as a job parameter, runs it across every source on the mesh, and writes OCSF-normalized rows to query_mesh_events. It needs no human behind it, so it is the path for anything scheduled or scripted.

Why Run It From Databricks

FSQL is Query's query language for asking one question of every connected platform at once. You write a statement against the Query Data Model; the mesh translates it to each source's native syntax, runs the searches in parallel, and returns the results normalized to OCSF — so a sign-in from Okta and a sign-in from Entra ID come back in the same shape.

Running it from a Databricks job rather than the Query console gets you two things: the rows land in Unity Catalog where the rest of your tooling can join against them, and the run needs no human behind it. That makes this the path for anything scheduled, triggered, or driven by a detection.

Running a Statement

databricks bundle run run_fsql_query --profile <profile> \
  --params fsql="QUERY detection_finding.** AFTER 1days LIMIT 25"

Only read-only FSQL is accepted. Results land in two tables:

TableHolds
query_mesh_eventsOne row per OCSF event returned
fsql_run_auditOne row per run — statement, row count, which connectors answered, timings

Writing a Statement That Runs

Prefer the wildcard. QUERY <event_class>.** AFTER <N>days LIMIT <n> returns every attribute of the class, so you need no attribute path.

QUERY authentication.** AFTER 7days LIMIT 100
QUERY detection_finding.** WITH %hostname = 'somehost' AFTER 7days LIMIT 20
QUERY osint_inventory_info.** WITH %hash = '...' LIMIT 10

Use the entity selectors. %username %hostname %ip %hash %email %filename %processname each search every field mapped to that entity across every connector, so you need no per-source field paths. See Entities and the Query Syntax Reference.

If you are new to FSQL, the Quick Start walks through writing a statement, and the Cheat Sheet is the one page worth keeping open while you do.

🚧

Always name an event class and a LIMIT

A federated search is satisfied as soon as the LIMIT is met from any source, so one chatty connector can fill the result while the quiet ones never appear. An unbounded query samples one platform; it does not survey many.

Results Are Partial by Design

A federated search reaches live systems, and some will not answer. The response carries per-connector errors alongside the records; a connector failing is informative, not fatal.

fsql_run_audit records which connectors answered, so a query that returned nothing can be told apart from one that could not look.

What It Costs

On the Databricks side, a job run costs the same whether its search returns 20 rows or 20,000 — the cost is time spent waiting on the federated search, not result size.

So batching is the biggest lever. Many searches in one job run cost far less than one run per detection.

Seeing What Is Connected

databricks bundle run refresh_connectors --profile <profile>

Writes a snapshot to query_mesh_connectors, backing the dashboard's Connected Platforms tab — every configured connector, the event classes each answers for, and when the snapshot was taken. Connectors serving threat feeds appear with no event classes; that is expected.

The snapshot is a point in time. Re-run when connectors change, then refresh the dashboard.

Querying the Results

Everything is in Unity Catalog, readable by anything that reads a table. The bundle's Genie space answers questions over the same tables — open critical detections with no investigation, events per source, events per hour. source, severity, host and event_time are promoted out of the event payload so Genie can write plain SQL against columns rather than extracting JSON.


Did this page help you?