In addition to simple filter expressions, Seq provides an SQL syntax for more advanced queries. SQL queries in Seq permit:
- Tabular queries -
selectevent properties as columns
- Aggregate operators like
- Time groupings to apply an aggregation over individual time slices
SQL queries enable charting through the view selector that will appear above and to the left of a SQL result set.
The view selector is a row of icons representing table, timeseries, bar and pie chart views.
The syntax of an SQL query in Seq is:
select [<column> [as <label>],] [from stream [where <predicate>] [group by [time(<d>)|<grouping>,]] [having <predicate>] [order by [time|<label>] [asc|desc]] [limit <n>] [for refresh]]
from stream clause indicates the query will run over the event stream currently being viewed, with any active signals used to filter results.
Expressions in a SQL query use SQL-style
'single quoted' strings and familiar SQL operators such as
The simplest SQL queries pluck properties out of events into columns.
select ContactId, Eligibility.Category from stream where Application = 'Patient Portal' and Eligibility is not null
This produces a rowset:
In all queries, when SQL syntax is used, Seq requires that a time range is specified using the date range picker in the Seq web interface. By default the last 24 hours will be included; use the calendar drop down to change the time range and refresh the query.
Tabular queries are great for exporting comma-separated text files from Seq. Use the drop-down beside the refresh button to download results in CSV format.
Seq automatically limits the size of rowsets that can be computed using SQL queries, and returns an error if a query would exceed the default limit.
To override the limit, specifying a
limit clause will take a subset of results:
select ContactId, Eligibility.Category from stream where Application like '%Portal' and Eligibility is not null limit 100
limit clause is specified last, after any conditions or groupings.
Instead of specifying a limit directly, it's usually possible to select a shorter time range using the date picker controls, or group the query at a coarser level.
When a tabular query is executed, options to display the data as a a bar or pie chart will appear to the bottom left of the filter bar. Selecting one of these will display the rowset in a chart of that kind.
Most uses for SQL queries in Seq involve aggregate operators. These provide familiar computations like
select count(*) from stream where Application = 'Patient Portal' group by Eligibility.Category
This produces a rowset:
View the full list of aggregate operators.
Rather than compute aggregates across the entire time range, a time slice grouping can be used.
select count(*) from stream where Application = 'Patient Portal' group by time(1h), Eligibility.Category
Time slicing expressions can group events by a number of days (
d), hours (
h), minutes (
m), seconds (
s) or milliseconds (
To retrieve the most-recent results first, specify
order by time desc in the query.
time() grouping may be specified last to render the result in timeseries format. Note that in this configuration,
limit clauses will apply to the raw (un-filled) result set, so
limit 5 may return more than five rows.
Selecting the timeseries icon to the bottom-left of the filter bar will display the results in a timeseries chart.
Need a handy syntax reference to keep by your desk? We've put together a simple cheat sheet with SQL syntax and operator basics. Download the PDF here.