Query data in Lakewatch

Use the query page to search, filter, and analyze security data with SQL. You can write custom queries, refine results without rerunning them, and see how results are distributed over time.

Find and open queries

When you open Query with no query tabs open, you see the queries list. It shows your saved queries and unsaved drafts, with each query's Name, Created by, Warehouse, and Created and Updated timestamps.

• To find a query, search by name, or filter by Created by or Warehouse.
• To open a query, click its name.
• To start a new query, click New SQL query.

Each query opens in its own query tab. Your open tabs stay open when you leave Query, and they're still there when you come back. To return to the queries list, close all open tabs.

Use query tabs

With query tabs, you can work on several queries at the same time, so you can compare results or keep separate analyses open.

Work with tabs

You can create, rename, close, and reopen query tabs:

• Create a tab: Click Add query tab (+) next to your open tabs to open a new, existing, or recent query.
• Rename a tab: Click the active tab's name to edit it. Each tab name must be unique.
• Close a tab: Click the X to the right of the query name. Queries you have run are saved to Recents. A query you have never run is cached only in your browser, and you can't open it from another machine until you run it one time.
• Reopen a closed query: Open a recently closed query from Quick access on the queries list. If you have a tab open, click + to open an existing or recent query.
• Monitor progress: Each tab icon shows a circular progress indicator while its query runs, and a checkmark when the query completes.

Tab controls

Each open query tab has a toolbar with these controls:

• Favorite: Mark the query as a favorite for quick access later.
• Permissions: Manage who can open the query and their permission level.
• View query history: View the query's run history. It's also in the Query actions menu as Query history.
• Query actions: Select Copy link, Duplicate query (opens a copy in a new tab), Query history, or Delete.

Write and run queries

You write queries in SQL in a query tab. For help writing, debugging, or optimizing a query with Genie Code, see the Genie Code example prompts.

Query requirements

For fast, stable results, include both of these clauses in every query:

• A time-range filter: Use WHERE IN_TIMERANGE(time) to scope the query to the range you select in the date and time picker. You don't write the start and end times into the query yourself. The column passed to IN_TIMERANGE() must be a TIMESTAMP or DATE type. Every Lakewatch table has a time column, so IN_TIMERANGE(time) works on any Lakewatch table. The range is half-open. A row whose timestamp equals the picker's start is included, but a row whose timestamp equals the end is excluded. IN_TIMERANGE() replaces the older inRange(), which filters on an inclusive range.
• ORDER BY time: Keeps paging and the timeline stable.

If you omit either clause, Lakewatch prompts you to add it before the query runs, but you can run the query anyway.

IN_TIMERANGE() is also the recommended way to scope detection rules to their run window. See Scope rules to a time range.

Run a query

To run a query:

1. Set the time range with the date and time picker. The query runs over data in this range. You can set these options:
   • Time zone: Choose UTC or local time, which is detected from your device.
   • Preset ranges: Select a predefined range, such as Last 24 hours.
   • Custom ranges: Use the calendar picker to set specific start and end dates and times.
2. Write your SQL query in the editor. Include the clauses in Query requirements.
3. Choose the SQL warehouse to run on from the compute picker in the query toolbar. If you don't select a warehouse, the query doesn't run, and Lakewatch prompts you to select one.
4. Click Run query.

To stop a running query, click Cancel. Canceling can take a moment while cleanup finishes.

Refine results

Refinements filter, sort, and group query results without changing or rerunning your original query, so you can explore results without using more compute. You can chain multiple refinements to narrow results further.

Manage refinements in the refinements bar

The refinements bar sits above the query results:

• To add a refinement, enter a pipe operation using SQL pipe syntax, and then press Enter. For example, use WHERE to filter, ORDER BY to sort, or AGGREGATE to group and summarize results.
• To move refinements into your main SQL query, click Move refinements to the query in the upper-right corner of the refinements editor.
• To remove a refinement, hover over it, and then click the remove icon.

Refine results from the results table

You can also add filter refinements from the results table:

• Right-click a cell value to filter or exclude by that value.
• For JSON fields, you can also filter by nested fields.

Timeline visualization

The timeline histogram shows how your query results are distributed over time, so you can find patterns, anomalies, and periods of interest in large datasets. The timeline appears when your query results include time-based columns.

Use the timeline

You can explore the timeline in these ways:

• Hover over a bar to see the event count and timestamps for that interval.
• Click a bar to jump to the results from that moment.
• Click and drag to zoom in on a time window.
• Use the zoom in or zoom out button in the upper right to adjust the interval you're viewing.

Explore results in the table

The results table loads results as you scroll, and you can manage columns and inspect individual rows.

Table navigation

You can work with the table in these ways:

• Scroll to load more results.
• Use the Fields panel to show, hide, or reorder columns.
• Click a JSON object or array to expand it.

Row details

Click any row to open the details drawer on the right. If you open the drawer from a specific cell, the drawer highlights that property.

Quick actions

Right-click a cell or row to open a menu of common actions:

• Cells: Copy the value or the row as JSON, filter or exclude by the value, search for the value, or group by the field.
• Rows: Copy the row as JSON.

Troubleshooting

If a query doesn't behave as expected, check these common causes:

• No results returned: Check your time range and refinements. Try a wider time range.
• Missing timeline: Make sure your query results include a time-based column.
• Query errors: Review the error details on the page, and then adjust your SQL or settings.
• Slow cancellation: Canceling a query can take a moment while cleanup finishes.
• Access not refreshed after a permission change: If you change a query's permissions and see Permissions were updated, but your access couldn't be refreshed, select Retry in the warning. Until the refresh succeeds, you can't edit, run, or delete the query.
• Lost work: Reopen closed queries from Quick access on the queries list.