Query data in Lakewatch

Important

Lakewatch is in Private Preview. To request access, reach out to your Azure Databricks account team.

The query page is a central hub for searching, filtering, and analyzing security data using SQL. The interface allows you to write custom queries, apply real-time filters, and visualize results across time.

Genie Code can help you explore data, refine results, optimize queries, and interpret findings. For ideas, see Genie Code example prompts.

Note

Queries saved in Lakewatch do not appear in the Databricks SQL editor. Lakewatch stores its queries in an internal, per-workspace Lakewatch catalog that isn't shown in Catalog Explorer, and manages them through the Lakewatch app.

Getting started

When you open Query with no query tabs open, you land on the queries page, which lists your saved queries and unsaved drafts with search and filter controls:

  • Search queries by name, or filter by Created by or Warehouse.
  • The list shows each query's Name, Created by, Warehouse, and Created and Updated timestamps. Click a query's name to open it.

Start a new query to open a query tab. Your open query tabs persist across navigation: if you leave a tab open and move to another page, it's still there when you return to the queries page. To return to the queries list, close your open tabs. Closing the last one brings it back.

Using query tabs

Query tabs enable you to work with multiple queries simultaneously, making it easy to compare results or maintain different analytical workflows.

Working with tabs

  • Create new tab: Click the + button next to your open tabs to open a new, existing, or recent query
  • Rename tab: Click directly into the active tab name to edit it (duplicate names are not allowed)
  • Close tab: Click the X to the right of the query name to close a tab. Queries you have run are saved to Recents. A query you have never run is cached only in your browser and can't be opened from another machine until you run it one time.
  • Reopen closed queries: Reopen a recently closed query from Quick access on the query homepage, or, if you have a tab open, use the + button to open an existing or recent query.
  • Monitor progress: Tab icons show query status with a circular progress indicator or checkmark when complete

Tab controls

Each open query tab has a toolbar with these controls:

  • Favorite (star): Mark the query as a favorite for quick access later.
  • Share (lock): Manage who can open the query and their permission level.
  • Query history: View the query's run history, available from both the tab toolbar and the overflow menu.
  • Overflow menu (⋮): Copy a link to the query, move it, or delete it.

Note

Favorite, Share, and Query history work only on a saved query. A query is saved the first time you run it.

Writing and executing queries

Queries are written in SQL inside of query tabs.

Query requirements

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

  • A time-range filter. Use WHERE IN_TIMERANGE(time) to scope the query to the range you select in the date/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 is guaranteed to have a time column, so IN_TIMERANGE(time) always works. The range is half-open: a row whose timestamp equals the picker's start is included, but one that equals the end is excluded. (IN_TIMERANGE() replaces the older inRange(), which filters on an inclusive range.)
  • ORDER BY time - Enables stable paging and timeline behavior

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.

Running queries

  • Set time parameters using the date/time picker. Your query runs over data from these time ranges
    • Timezone options: Choose between UTC or local time (auto-detected from your device)
    • Preset ranges: Select from predefined options (e.g. "Last 24 hours")
    • Custom ranges: Use the calendar picker to set specific start/end dates and times
  • Write your SQL query in the editor, including the SQL clauses listed above to make your query run fast
  • Choose the SQL warehouse to run on from the compute picker in the query toolbar. A warehouse is required: if you don't select one, the query doesn't run and Lakewatch prompts you to select a SQL warehouse.
  • Click the Run query button to start execution
  • Click Cancel to stop a running query

Refinements: Filtering and analysis

Refinements allow you to dynamically filter, sort, and group query results without modifying or re-running your original query, providing a fast way to explore different data perspectives while saving on compute costs. You can chain multiple refinements for complex filtering.

Managing refinements

From the refinements bar:

  • Add filter expressions directly using SQL pipe syntax, and hit enter to run
  • To promote refinements into your main SQL query, click the up arrow button at the upper righthand corner of the refinements editor.
  • To remove a refinement, hover over the refinement to reveal the delete (X) button.

From the results table:

  • Right-click column headers to sort ascending/descending or Group by.
  • Right-click cell values to Filter or Exclude by that specific value.
  • For JSON fields, you can also filter by nested fields.

Timeline visualization

The timeline histogram provides a visual overview of your query results distributed across time, making it easy to identify patterns, anomalies, and periods of interest in large datasets. The timeline appears automatically when your query includes time-based columns.

Using the timeline

  • Hover over bars to see event counts and timestamps for specific intervals
  • Click any bar to jump to the results from that specific moment
  • Click and drag to zoom in on a time window for detailed analysis
  • Use the zoom in or zoom out button (upper right) to adjust the interval you're viewing

Viewing and exploring results in the table

The results interface is designed for efficient exploration of large datasets, with flexible column management and detailed row inspection capabilities.

Table navigation

  • Results load automatically as you scroll through large datasets.
  • Use the Fields column to show/hide, reorder, or pin columns.
  • Click to expand JSON objects and arrays for comfortable reading.

Row details

  • Click any row to open the details drawer on the right
  • Opening from a specific cell highlights that property in the drawer

Quick actions

Right-click context menus provide instant access to common operations:

  • Cells: Copy values, filter/exclude by value, or remove columns
  • Headers: Sort data or group by column values
  • Entire rows: Copy complete row data as JSON format

Troubleshooting

  • No results returned: Verify your date/time range and refinements. Try broadening parameters
  • Missing timeline: Ensure your query includes time-based columns in the results
  • Query errors: Review error details displayed on the page and adjust SQL syntax or settings
  • Delayed cancellation: Allow a moment for cleanup processes to complete when canceling queries
  • Lost work: Reopen closed queries from Quick access on the query homepage
  • Last updated on