BadgerQL guide

BadgerQL is the language you use to interact with your data stored in Insights. It was designed to enable you to enrich, shape, and combine your events so you can craft any view of your data. Quick reference docs are also available in the application via the book icon in the top-right corner of the query box.

We also provide inline hints in the query editor that show info from the quick reference docs as you type:

Functions to enrich, shape, and combine data

Functions are the core of BadgerQL. You can think of your data falling or piping through each function that you specify, getting filtered, aggregated, and so on along the way. The most common functions you will use are fields to select fields to view, filter to restrict what data appears in the results, and stats to do counts, averages, and other analyses. Keep reading to learn about all the functions we offer.

Combining functions

While calling a BadgerQL function on its own can produce interesting results, the real power comes when piping functions together via the pipe (|) operator:

fields status_code::int, controller::str
| filter startsWith(controller, "Stripe")
| stats count() by status_code

Each function builds off the other to create a result showing the distribution of status codes just for Stripe controller requests.

Note that BadgerQL does not work like SQL. Each successive function is applied to the result of the previous, so you can only reference fields down the pipeline.

For example, if you want to convert a string to a number gathered from a parse function, you can pipe into another fields function:

parse url::str /id=(?<id>\d+)/
| fields toInt(id) as id

Expand

You can use expand to turn an event that has a field with array data into multiple events.

expand array_field [as alias][, ...]

With data that has a single event like {"id": 1, "charges": [700, 430, 200]}, the following query will return three events, with id and charge fields:

expand charges[*]::int as charge

See the Arrays section for more detail on working with array data.

Fill

Use fill to inject events for missing data points.

fill field_expression [as alias] [asc|desc] [from ...] [to ...] [step ...] [with field[ = expression][, ...]*]

Unless specified with from or to, fill will determine the min and max values of the field_expression, sort, and produce new events with missing field_expression values replaced by the incremented or decremented step value.

field_expression only allows for number or temporal types. The resulting optional clause types differ based on the resolved type:

fill number [from number] [to number] [step number]
fill temporal [from temporal] [to temporal] [step interval]

Fill works best when referencing an already existing field. Since fill inserts data at a regular interval, you will also get the best results if the field follows the step size of the fill.

Typical usage

Take a stats call that bins the count of events per hour:

stats count() as ct by bin(1h) as bin

You might get sparse results if there is not enough data to fill each bin:

ct	bin
5	2023-04-05 02:00:00.000
10	2023-04-05 04:00:00.000
2	2023-04-05 06:00:00.000

With the fill function:

stats count() as ct by bin(1h) as bin
| fill bin step 1h

You can produce a full binned result set:

ct	bin
5	2023-04-05 02:00:00.000
0	2023-04-05 03:00:00.000
10	2023-04-05 04:00:00.000
0	2023-04-05 05:00:00.000
2	2023-04-05 06:00:00.000

Fill order

By default, the fill function sorts the field_expression in ascending order before injecting fill events. You can change this by providing an order direction after the field_expression:

fill @ts desc step -1h

When filling in descending order, from must be greater than to and step must be a negative value.

@fill internal field

Filled events have an additional internal @fill field added to the results. You can use this field to determine when an event is filled:

fields @fill
| fill duration::int from 100 to 500 step 100

@fill	duration
true	200
true	300
	325
true	400

Filling other fields

Most fields other than field_expression will be filled with a null value for injected events. You can control what data is replaced using the with clause. Setting the with field to the field_expression will result in an error.

If with is given only a field, it will carry over the field value from the previous event:

fields @fill, controller::str
| fill duration to 340 step 10 with controller

@fill	duration	controller
	300	login
true	310	login
	320	sign-up
true	330	sign-up

with fields can also be set to specific values for filled events:

stats avg(temp::float) as avgTemp by bin(1d) as bin
| fill bin step 1d with avgTemp = 65.0

avgTemp	bin
73.3	2023-04-08
65.0	2023-04-09
68.9	2023-04-10
65.0	2023-04-11

Referencing other fields from previous events is also possible, acting like a LAST_VALUE() window function.

Notes

• Having multiple fills is possible by piping together fill functions, but take care to ensure you are not injecting too many events.
• from and to values are not inclusive when producing injected results.

Fields

The fields function enriches your results by adding extra fields. Any fields that you select or alias can be referenced in later functions, and they will be returned in the final dataset unless rewritten by later functions.

fields expr [as alias][, ...]*

Fields can be aliased with the as clause, and unsupported characters (like spaces) can be used by using backticks.

fields user_name::str as `User name`

Aliased fields can be used in later functions:

fields concat(first_name::str, " ", last_name::str) as full_name
| filter full_name match /^Bob.*/

Internal fields

We set the following internal fields for you as the data is ingested:

Name	Type	Description
@id	String	The event ID
@ts	DateTime	The reported timestamp if provided as ts or timestamp; otherwise, the time when the event was received
@received_ts	DateTime	The time when the event was received
@stream.id	String	The ID of the stream that contains the event. Each project contains at least two streams: the internal Honeybadger stream used for notices, etc., and the stream used for storing events that you send to our API.
@stream.name	String	The name of the stream
@query.start_at	DateTime	The timestamp of start of the range queried. E.g., when searching back 3 hours (the default), this will be three hours ago
@query.end_at	DateTime	The timestamp of end of the range queried. E.g., for the default query, this would be the time when the query was executed, since the default query searches for data up to the time the query was sent.
@size	Integer	The size in bytes of the event
@fill	Boolean	Whether the result has filled-in values
@preview	JSON Object	A preview of the data stored for the event

Filter

Filter expects a body that results in a boolean expression, and it will exclude events where the expression returns false.

filter boolean_expr [and|or ...]*

Multiple piped filter functions will act as AND operations.

filter controller_name::str == "StripeController" and duration::float > 2000
| filter action_name::str == "hook"

Limit

Limit the number of results returned by the query.

limit integer [by expr[, ...]*]

Caution

Limiting can adversely affect piped function results. For example, adding a limit before a stats call will only gather stats on the limited events:

limit 10
| stats count() by controller::str

If you want to restrict the number of returned results, make sure limit is at the end of your pipeline:

stats count() by controller::str
| limit 10

Include a by clause to limit the number of results per group.

limit 10 by user_id::int

Pipe into limit to restrict the final number of results returned by the query.

limit 5 by user_id::int
| limit 100

Only

Use only to restrict which fields are rendered in the results and in which order they will appear.

only expr [as alias][, ...]*

For example, if you want to filter on a particular field, but you don’t want that field to appear in the results, you can use only to select the fields you want to see:

fields a, b, c
| filter c > 2
| only b, a

Parse

Extract fields using regular expressions

parse expr /regex/

If your events have data that can be extracted using regular expressions, you can create fields from that data. The following example will extract “redis” from an event that has a field named “addon” that contains the value “redis-fitted-71581” and place it in a new field called “service”. Both the “addon” and “service” fields will appear in the results.

fields addon::str
| parse addon /(?<service>[[:alpha:]]+)/

Sort

Order events based on fields.

sort expr [desc|asc][, ...]*

Queries without an explicit sort are unordered and non-deterministic. Sort direction can be either desc (descending) or asc (ascending). By default, fields are sorted in descending order if not specified.

sort day desc, duration asc

Sort is useful to order results by time, or when calculating stats:

fields email
| filter action::str == "Logged in"
| stats count() as count by email
| sort count

It can make sense to call sort multiple times, as sorting after rewriting functions might be necessary.

Stats

Aggregate event fields

stats agg_expr[, ...]* by [expr][, ...]*

The workhorse of Insights, stats allows you to perform calculations on your data. You can count events, calculate averages, and more.

stats avg(response_time::float)

Note

Results from stats are unsorted by default, even if the events piped into stats are sorted. Pipe into sort if order matters.

Aggregation

Available aggregate functions:

Function	Description
count()	Returns the total count of all results. Can contain an expression that filters the count
avg(field)	Calculates the average (mean) value for a numeric field
min(field), max(field)	Returns the minimum/maximum value for the given field
sum(field)	Calculates the sum of values for a numeric field
percentile(percentage, field)	Returns the value at the specified percentile for the given numeric field
unique(field)	Returns the number of unique values for the specified field
first(field), last(field)	Returns the first/last value of the specified field for the whole aggregate
apdex(field, threshold)	Calculates an Apdex (Application Performance Index) score between 0 and 1

Find the number of 500 errors over a time period:

filter status_code::int == 500
| stats count()

Find the average response time for a specific endpoint:

filter endpoint::str == "/api/v1/orders"
| stats avg(duration::float)

Combine multiple aggregate functions in a single query:

filter environment::str in ["production", "staging"]
| stats count(), percentile(95, duration::float)

Grouping

The by clause allows you to group the results by one or more fields.

stats avg(response_time::float) by location::str

One of the most common use cases for grouping is to create a time series by grouping with bin().

The bin() function

bin() rounds a datetime down to the nearest interval boundary, which lets you group events into time buckets (e.g., “all events in this 1-hour window”).

bin([interval[, datetime]])

Both arguments are optional:

• interval — the bucket size, written using interval syntax (e.g. 1h, 30m, 2d). If omitted, bin() automatically picks a reasonable size based on your selected time range.
• datetime — the field to bin. Defaults to @ts. Use this when you want to bin on a field other than the event timestamp.

stats count() by bin(1h) as time

stats count() by bin(1h, toDateTime(user.created_at::str)) as time

When no alias is given, the result column is named after the call itself (e.g. bin(1h)). Always alias bin() when you need to reference it in a later function like sort or fill.

Interval syntax — an integer followed by a unit abbreviation:

Unit	Abbreviation	Example
Seconds	s	30s
Minutes	m	15m
Hours	h	1h
Days	d	7d
Weeks	w	1w
Months	mon	1mon

Auto-sizing — when bin() is called with no interval argument, the bin size is automatically chosen to produce a reasonable number of buckets for your selected time range.

stats count() by bin() as time, status_code::int

You can use any field or expression in the by clause:

stats avg(duration::float), max(duration::float)
by bin() as time, concat(controller::str, "#", action::str) as controllerAction

Unique

The unique function filters out duplicate events based on the field(s) you specify.

unique field[, ...]

Toggling functions

Hotkey: CTRL + /

When exploring data in BadgerQL, you might find it useful to temporarily ignore certain functions while keeping them in the query.

To do this, add a bang (!) at the beginning of the BadgerQL function. This comments out the function, effectively ignoring it without removing it from the query.

This is particularly useful for toggling conditions in statistical analyses. For example, you might want to alternate between including and excluding certain filters:

fields event_type::str, duration::int
| filter event_type == "page_view"
| !filter duration > 100
| stats count() by bin(1d)

Note that if a function spans multiple lines, placing a bang (!) at the beginning will toggle the entire function, not just the first line:

fields event_type::str, duration::int
| filter event_type == "page_view"
| !filter
    duration > 100 and
    duration < 200
| stats count() by bin(1d)

Types

In Insights, data is stored and accessed in its typed format. BadgerQL is a strongly typed language, which means it is particular about type consistency.

We currently support storing data with these types:

Short	Long
str	String
bool	Boolean
float	Float
int	Integer

Type hinting

Type hinting is key in BadgerQL. You indicate the expected field type using :: and the short type name.

For example, if you know you are sending status codes as integers, you must augment your query to point to the field like:

fields status_code::int

This only gives the system a hint for where to look for the event field. It does not coerce the value into another type. If you want to convert types, use one of the conversion expression functions.

It’s not required to repeat type hints. If you use a field with a type hint earlier, it carries over:

fields status_code::int
| stats count() by status_code

Conflicting type hints or inaccurate hints can result in null values or errors.

We also support using these types (either through conversion or as a function result) in queries:

Short	Long
datetime	Datetime
date	Date
tzdatetime	Datetime with timezone
interval	Relative time intervals (e.g. 1h)

Note: you can’t hint these types, as we don’t store data in these formats.

Union types

You may see number and temporal appear in function signatures throughout the docs. These are not types you can use directly in queries; they are shorthand for describing which concrete types a function accepts. number means the function works with either int or float, and temporal means it works with either date or datetime.

Literal values

Some function arguments don’t accept field references, only literals (e.g., 1.5, "hi"). This is denoted in the type signature.

For instance, round(duration::float, 0) is valid with the second argument as a literal integer. round(duration::float, precision::int) would produce an error.

Dates

Creating dates

We provide a shorthand for creating datetime literal values by wrapping the date in curly brackets {}:

fields {2023-01-01} as baseDate

Casting dates

There is no way to store native dates in Insights, so if you want to interact with a native date or datetime, you will need to cast a string column to one of the temporal types:

fields toDateTime(created_at::str) as created_at
| filter created_at > {2023-04-08 12:00:00}

Timezones

All datetimes are returned in your selected timezone by default. This means that if you input a datetime, it will be automatically converted to match your preferred timezone setting.

To adjust datetimes to a specific timezone for a query, use the toTimezone function:

fields toTimezone(@ts, "America/Los_Angeles")

This will show the timestamp in PST, which will be denoted in the timezone information contained within the field type (tzdatetime.PST for this example).

Arrays

Insights is primarily designed to work with simple key/value data mappings, however, it does support ingesting and querying array data in your events.

To access fields within an array, use bracket notation to specify an index. For example, user.scopes[0].name::str is a valid path into your event data.

Expand function

The most flexible tool for working with arrays is the expand BadgerQL function. expand unwraps array data into individual events, which you can then pipe into any other function.

For example, given events containing this data:

{"id": 1, "charges": [700, 430, 200]}
{"id": 2, "charges": [100]}

You can expand the charges field using wildcard notation:

expand charges[*]::int as charge

This will expand each result to:

id	charge
1	700
1	430
1	200
2	100

Note: Just like looking up a field, the path must reference a set of values. You can’t expand into an object or another array.

You can then use stats to group events back together after processing:

expand charges[*]::int as charge
| filter charge > 200
| stats sum(charge) as total_cost by id

Which will combine the filtered events back with summed charges:

id	total_cost
1	1130

Conditional array matching

Sometimes you want to know if a value within an array passes some condition. We have special expression functions just for this case. For example, to find events with a specific tag:

filter any(tags[*]::str == "funky")

The any function also works with nested object data within an array:

filter any(events[*].user.email::str like "kwebster%")

Performance implications

Array support is limited in terms of performance optimizations. Where possible, consider flattening array data into separate events before sending them to Honeybadger.

Expression functions

Expression functions can be used in a variety of places, such as filtering data, creating fields, calculating aggregates, etc. They are used to compare fields, perform arithmetic, reformat data, and more.

Comparison

The comparison operators work across number, string, boolean, and datetime types. != and <> are equivalent operators.

between and not between are inclusive on both ends:

filter status_code::int between 200 and 299

either returns the first non-null value from its arguments — useful as a fallback when a field may be stored under different names:

fields either(name::str, full_name::str, username::str) as name

>

Signature number > number -> booleanstring > string -> booleanboolean > boolean -> booleandatetime > datetime -> boolean

Example

fields status_code::int > 500

<

Signature number < number -> booleanstring < string -> booleanboolean < boolean -> booleandatetime < datetime -> boolean

Example

fields status_code::int < 500

==

Signature number == number -> booleannumber[] == number[] -> booleanstring == string -> booleanstring[] == string[] -> booleanboolean == boolean -> booleanboolean[] == boolean[] -> booleandatetime == datetime -> booleandatetime[] == datetime[] -> boolean

Example

fields status_code::int == 200

!=

Signature number != number -> booleanstring != string -> booleanboolean != boolean -> booleandatetime != datetime -> boolean

Example

fields status_code::int != 200

<>

Signature number <> number -> booleanstring <> string -> booleanboolean <> boolean -> booleandatetime <> datetime -> boolean

Example

fields status_code::int <> 200

<=

Signature number <= number -> booleanstring <= string -> booleanboolean <= boolean -> booleandatetime <= datetime -> boolean

Example

fields status_code::int <= 200

>=

Signature number >= number -> booleanstring >= string -> booleanboolean >= boolean -> booleandatetime >= datetime -> boolean

Example

fields status_code::int >= 200

between

Signature number between number and number -> booleanstring between string and string -> booleandatetime between datetime and datetime -> boolean

Example

filter status_code::int between 200 and 300

not between

Signature number not between number and number -> booleanstring not between string and string -> booleandatetime not between datetime and datetime -> boolean

Example

filter status_code::int not between 300 and 400

isNotNull

Signature isNotNull(number) -> booleanisNotNull(string) -> booleanisNotNull(boolean) -> booleanisNotNull(datetime) -> boolean

Example

filter isNotNull(status_code::int)

isNull

Signature isNull(number) -> booleanisNull(string) -> booleanisNull(boolean) -> booleanisNull(datetime) -> boolean

in

Return true if field value is contained within the array of literal values. The field type must match value type in the array.

Signature number in number[] -> booleanstring in string[] -> booleandatetime in datetime[] -> boolean

Example

filter status_code::int in [300, 301, 404]

not in

Return true if field value is not contained within the array of literal values. The field type must match value type in the array.

Signature number not in number[] -> booleanstring not in string[] -> booleandatetime not in datetime[] -> boolean

Example

filter status_code::int not in [300, 301, 404]

either

Returns the first non-null value

Signature either(integer, ...integer) -> integereither(float, ...float) -> floateither(string, ...string) -> stringeither(boolean, ...boolean) -> booleaneither(datetime, ...datetime) -> datetime

Example

fields either(name::str, full_name::str, username::str) as name

like

Returns true when the search string matches

Can use these metacharacters: % - Matches an arbitrary amount of characters _ - Matches single arbitrary character

The string matcher is case sensitive

Signature string like string -> boolean

Example

filter email::str like "%compuserve%"

not like

Returns true when the search string does not match

Can use these metacharacters: % - Matches an arbitrary amount of characters _ - Matches single arbitrary character

The string matcher is case sensitive

Signature string not like string -> boolean

Example

filter email::str not like "%compuserve%"

ilike

Returns true when the search string matches

Can use these metacharacters: % - Matches an arbitrary amount of characters _ - Matches single arbitrary character

The matcher is case insensitive

Signature string ilike string -> boolean

Example

filter email::str ilike "%compuserve%"

not ilike

Returns true when the search string does not match

Can use these metacharacters: % - Matches an arbitrary amount of characters _ - Matches single arbitrary character

The matcher is case insensitive

Signature string not ilike string -> boolean

Example

filter email::str not ilike "%compuserve%"

match

Returns true when the regex matches

The regex uses re2 regex syntax

Signature string match regex -> boolean

Example

filter email::str match /.*compuserve.*/

not match

Returns true when the regex does not match

The regex uses re2 regex syntax

Signature string not match regex -> boolean

Example

filter email::str not match /.*compuserve.*/

Arrays

any

Return true if any conditional logic within the arguments results in a true value.

Effectively, we run the logic in the function argument through each array item, and if one matches, then we return true.

A function body without an expanded array is considered invalid.

Signature any(boolean) -> boolean

Example

filter any(tags[*]::str == "severe")

filter any(coupon_ids[*]::int in [123, 456])

all

Return true if all conditional logic within the arguments results in a true value.

Effectively, we run the logic in the function argument through each array item, and if they all match, then we return true.

A function body without an expanded array is considered invalid

Signature all(boolean) -> boolean

Example

filter all(tags[*]::str != "severe")

filter all(coupon_ids[*]::int not in [123, 456])

Logic

if is single-branch conditional: if the condition is true it returns the then value, otherwise it returns the else value. The else arm also fires when the condition evaluates to null.

fields if(status_code::int >= 500, "error", "ok") as result

cond is multi-branch: condition/value pairs are evaluated in order and the value from the first matching pair is returned. A final bare value (no preceding condition) acts as the fallback:

fields cond(
  status_code::int >= 500, "red",
  status_code::int >= 300, "yellow",
  "green"
) as severity

and

Signature boolean and boolean -> boolean

not

Signature not(boolean) -> boolean

or

Signature boolean or boolean -> boolean

if

Single path conditional branching

Signature t = string | boolean | number | temporal | intervalif(boolean, t, t) -> t

Example

fields if(toDayOfWeek(ts) == 2, "taco", "slop") as food_day

cond

Multiple path conditional branching

The cond() function allows for evaluating branches (ala. if and else if) through positional arguments. Each successive pair of arguments acts as an else if, with the first true boolean passing it's result as a return."

Signature t = string | boolean | number | datetime | datecond(boolean, t, boolean, t, ..., t) -> t

Example

fields cond(
  status_code >= 300, "yellow",
  status_code >= 500, "red",
  "green"
) as status_code_color

Arithmetic

The standard operators (+, -, *, /, %) work on numbers. A few noteworthy behaviors:

• Subtracting two datetime values returns the difference in seconds as an integer: end_ts::datetime - start_ts::datetime
• Adding an interval to a datetime shifts it forward: @ts + 1h
• The second argument to round, floor, and ceil is the number of decimal places and must be a literal integer — you cannot pass a field reference. round(duration::float, 2) is valid; round(duration::float, precision::int) is not.

+

Signature number + number -> numberdatetime + number -> datetimedatetime + interval -> datetime

-

Signature number - number -> numberdatetime - number -> datetimedatetime - interval -> datetimedatetime - datetime -> integer

/

Signature number / number -> float

%

Signature number % number -> number

*

Signature number * number -> number

abs

Signature abs(number) -> number

pow

Signature pow(number, number) -> float

log

Signature log(number) -> float

log2

Signature log2(number) -> float

log10

Signature log10(number) -> float

round

Signature round(number, literal integer) -> float

floor

Signature floor(number, literal integer) -> float

ceil

Signature ceil(number, literal integer) -> float

exp

Signature exp(number) -> float

Conversion

A few things worth knowing:

• toDateTime from a string uses best-effort parsing, so it handles a wide variety of date formats (ISO 8601, RFC 2822, etc.) without needing an exact format string.
• toUnix returns milliseconds since the Unix epoch, not seconds.
• toDate strips the time component from a datetime and returns a date-only value.

toInt

Signature toInt(any) -> integer

toFloat

Signature toFloat(any) -> float

toString

Signature toString(any) -> string

toDate

Signature toDate(string) -> datetoDate(datetime) -> date

toDateTime

Signature toDateTime(number) -> datetimetoDateTime(date) -> datetimetoDateTime(string) -> datetime

toUnix

Signature toUnix(datetime) -> integer

Dates

now() returns the current datetime in the query’s configured timezone.

toStartOf and toEndOf are lower-level alternatives to bin() when you need the start or end of an interval boundary rather than grouping:

fields toStartOf(1w) as week_start

fields toEndOf(1d) as end_of_day

toDayOfWeek returns 1–7 where 1 = Monday and 7 = Sunday.

See also the Dates section above for creating and casting date literals.

now

Signature now() -> datetime

toTimezone

Convert datetimes to a specific timezone.

Note: This does not explicitly embed the timezone into the datetime, but updates the type to reflect the selected timezone (tzdatetime).

Signature toTimezone(datetime, literal string) -> datetime

toYear

Signature toYear(datetime) -> integer

toHour

Returns the 24-hour number (0-23) for the supplied datetime.

Signature toHour(datetime) -> integer

toDay

Returns the day of month (1-31) for the supplied datetime.

Signature toDay(datetime) -> integer

toDayOfWeek

Returns the number of the day in a week (1-7, 1 = monday) for the supplied datetime.

Signature toDayOfWeek(datetime) -> integer

formatDate

Render date from format string.

Date tokens

%j	day of the year (001-366)	002
%d	day of the month, zero-padded (01-31)	02
%e	day of the month, space-padded (1-31)	2
%V	ISO 8601 week number (01-53)	01
%w	weekday as a integer number with Sunday as 0 (0-6)	2
%u	ISO 8601 weekday as number with Monday as 1 (1-7)	2
%a	abbreviated weekday name (Mon-Sun)	Mon
%W	full weekday name (Monday-Sunday)	Monday
%m	month as an integer number (01-12)	01
%M	full month name (January-December)	January
%b	abbreviated month name (Jan-Dec)	Jan
%Q	Quarter (1-4)	1
%y	Year, last two digits (00-99)	18
%Y	Year	2018
%C	year divided by 100 and truncated to integer (00-99)	20
%g	two-digit year format, aligned to ISO 8601, abbreviated from four-digit notation	18
%G	four-digit year format for ISO week number, calculated from the week-based year defined by the ISO 8601 standard, normally useful only with %V	2018
%D	Short MM/DD/YY date, equivalent to %m/%d/%y	01/02/18
%F	short YYYY-MM-DD date, equivalent to %Y-%m-%d	2018-01-02

Time tokens

%s	second (00-59)	44
%S	second (00-59)	44
%f	fractional second	1234560
%i	minute (00-59)	33
%h	hour in 12h format (01-12)	09
%I	hour in 12h format (01-12)	10
%H	hour in 24h format (00-23)	22
%l	hour in 12h format (01-12)	09
%k	hour in 24h format (00-23)	22
%r	12-hour HH:MM AM/PM time, equivalent to %H:%i %p	10:30 PM
%R	24-hour HH:MM time, equivalent to %H:%i	22:33
%p	AM or PM designation	PM
%T	ISO 8601 time format (HH:MM:SS), equivalent to %H:%i:%S	22:33:44
%z	Time offset from UTC as +HHMM or -HHMM	-0500

Other tokens

%n	new-line character (‘’)
%t	horizontal-tab character (’)
%%	a % sign	%

Signature formatDate(literal string, datetime = `@ts`) -> string

bin

Signature bin(datetime = `@ts`) -> datetimebin(interval, datetime = `@ts`) -> datetime

Example

fields bin(1w) as beginning_of_week

stats count() by bin(1h, toDateTime(user.created_at::str))

toStartOf

Signature toStartOf(interval, datetime = `@ts`) -> datetime

toEndOf

Signature toEndOf(interval, datetime = `@ts`) -> datetime

URL

urlParameter

Parse out value from valid URL query string

Signature urlParameter(string, literal string) -> string

Example

fields urlParameter(url::str, "user_id") as user_id_param

urlPath

Extracts the path from a URL. Example: /hot/goss.html The path does not include the query string.

Signature urlPath(string) -> string

urlDomain

Extracts the hostname from a URL.

Signature urlDomain(string) -> string

JSON

json

Extract a value from JSON string.

The result will be null if the path does not evalutate to a value, meaning arrays and objects are not valid targets.

NOTE: While this is something we support, we encourage that you merge any JSON data into your event

Signature json(string, literal string) -> string

Example

fields json(user_config::str, "$.login_info.last_login") as last_logged_in

String

toHumanString supports five format types: "number" (default), "bytes", "short", "milliseconds", and "microseconds" for microsecond-precision duration fields.

startsWith is a convenience wrapper around like — it is case-sensitive and does not accept wildcards in the match string.

trim

Signature trim(string) -> string

concat

Signature concat(string, string...) -> string

lowercase

Signature lowercase(string) -> string

uppercase

Signature uppercase(string) -> string

substring

Signature substring(string, integer, integer) -> string

Example

fields substring(token::str, 1, 3) as token_type

length

Signature length(string) -> integer

replace

Replace all matches of a substring or regex pattern with another string.

Signature replace(string, string, string) -> stringreplace(string, regex, string) -> string

Example

fields replace(controller::str, /Controller/, "") as controller

replaceFirst

Replace the first match of a substring or regex pattern with another string.

Signature replaceFirst(string, string, string) -> stringreplaceFirst(string, regex, string) -> string

Example

fields replaceFirst(controller::str, /Controller/, "") as controller

startsWith

Signature startsWith(string, string) -> boolean

toHumanString

Transform quantities into human readable friendly formats.

Signature toHumanString(number, string = "number") -> string

Aggregate

Aggregate functions are only valid inside a stats call.

count() with no argument counts all events. Passing a boolean expression counts only events where the expression is true. Passing a field name counts only non-null occurrences of that field:

stats count()                          -- all events
stats count(status_code::int >= 500)   -- events with 5xx status
stats count(user_id::str)              -- events where user_id is not null

first and last return the first or last value seen within the group. If the data is not sorted before stats, the result is non-deterministic. Pipe through sort first if order matters.

percentile is an approximated result.

count

Return the total counts of all results.

The count can be affected by supplying a boolean expression argument. If given a field, it will implicitly count non-null occurrences.

Signature count() -> integercount(boolean) -> integercount(number) -> integercount(string) -> integer

Example

stats count()

stats count(status_code::int < 500)

percentile

Calculate the percentile.

This is an approximated result.

Signature percentile(literal number, number) -> number

Example

stats percentile(90, duration::int)

unique

Count all unique values

Signature unique(string) -> integerunique(number) -> integerunique(datetime) -> integer

Example

stats unique(concat(controller::str, action::str))

min

Signature min(string) -> stringmin(number) -> numbermin(datetime) -> datetime

max

Signature max(string) -> stringmax(number) -> numbermax(datetime) -> datetime

sum

Signature sum(number) -> number

avg

Signature avg(number) -> number

first

Returns the first encountered value. Results could be random if the source is not sorted.

Signature first(string) -> stringfirst(number) -> numberfirst(boolean) -> booleanfirst(datetime) -> datetime

Example

stats first(user_name::str) by error_class::str

last

Returns the last encountered value. Results could be random if the source is not sorted.

Signature last(string) -> stringlast(number) -> numberlast(boolean) -> booleanlast(datetime) -> datetime

Example

stats last(severity::str) by error_class::str

apdex

Returns the Application Performance Index (Apdex) score, which measures user satisfaction with response time.

Signature apdex(number, number) -> float

Example

stats apdex(duration::int, 500) as apdex_score

Example queries and use cases

Find N+1 queries in your Rails app:

filter event_type::str == "sql.active_record"
| stats count() as queryCt, sum(duration::float) by request_id::str, query::str
| sort queryCt desc

What events are consuming my Insights quota?

Be sure to deselect the Internal Stream so you only see the data you are sending:

stats sum(@size) as size by event_type::str
| sort size
| only toHumanString(size, "bytes"), event_type