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.

Expand

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

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:

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 can be aliased with the as clause, and unsupported characters (like spaces) can be used by using backticks.

Aliased fields can be used in later functions:

Internal fields

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

Filter

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

Multiple piped filter functions will act as AND operations.

Limit

Limit the number of results returned by the query.

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

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

Only

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

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:

Parse

Extract fields using regular expressions

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.

Sort

Order events based on fields.

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

Stats

Aggregate event fields

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

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

Unique

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

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
pow
log
log2
log10
round
floor
ceil
exp

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.
apdex	Returns the Application Performance Index (Apdex) score, which measures user satisfaction with response time.

Example queries and use cases

Find N+1 queries in your Rails app:

What events are consuming my Insights quota?