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

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