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.

Insights docs in Honeybadger

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

BadgerSense documentation tips

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.

Expand

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

sql
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:

sql
expand charges[*]::int as charge

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.

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

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

sql
fields user_name::str as `User name`

Aliased fields can be used in later functions:

sql
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
@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
@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.
@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.

sql
filter boolean_expr [and|or ...]*

Multiple piped filter functions will act as AND operations.

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

Limit

Limit the number of results returned by the query.

sql
limit 100

Only

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

sql
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:

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

Parse

Extract fields using regular expressions

sql
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.

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

Sort

Order events based on fields.

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

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

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

Unique

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

sql
unique field[, ...]

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

Function Description

>

<

==

!=

<>

<=

>=

between

not between

isNotNull

isNull

in

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

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.

either

Returns the first non-null value

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

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

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

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

match

Returns true when the regex matches

The regex uses re2 regex syntax

not match

Returns true when the regex does not match

The regex uses re2 regex syntax

Array macros

Function Description

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.

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

Logic

Function Description

and

not

or

if

Single path conditional branching

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."

Arithmetic

Function Description

+

-

/

%

*

abs

log

round

floor

ceil

Conversion

Function Description

toInt

toFloat

toString

toDate

toDateTime

toUnix

Dates

Function Description

now

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).

toYear

toHour

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

toDay

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

toDayOfWeek

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

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 %

bin

toStartOf

toEndOf

Url

Function Description

urlParameter

Parse out value from valid URL query string

urlPath

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

urlDomain

Extracts the hostname from a URL.

Json

Function Description

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

String

Function Description

trim

concat

lowercase

uppercase

substring

length

replace

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

replaceFirst

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

startsWith

toHumanString

Transform quantities into human readable friendly formats.

Aggregate

Function Description

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.

percentile

Calculate the percentile.

This is an approximated result.

unique

Count all unique values

min

max

sum

avg

first

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

last

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

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

N+1 query results