Query Syntax

Bronto provides a simple point-and-click query builder for you to search, analyse and visualise your log data. We have several views which include Timeseries, Table, Top List, Pie Chart and Tree Map. These are explained in more detail here.

Use the search bar to filter the events which match your query. The search bar accepts two modes: a simple mode and an SQL mode. You can click on the button on the right side of the search bar to toggle between the two modes.

Search consists of a timerange and your search query, which may include key:value and SQL like syntax.

Simple Search Bar Mode

Autocomplete

In this default mode when you select the search bar you will be presented with an interactive dropdown list. Using this dropdown list the search bar’s autocomplete feature will display selectable options using existing keys and values present in your log data.

Build your query outside autocomplete by typing out your query like host:127.0.0.1 level:error

When you enter any word or sequence of characters into the search bar, e.g., error or 127.0.0.1, the query will only match events that contain that word somewhere in their text (ignoring uppercase/lowercase differences). You can combine multiple terms using the following operators:

Operator	Example	Description
AND	authentication AND failure	Matches log events that contain both authentication and failure in their text.
OR	authenthication OR password	Matches log events that contain either authentication or password in their text.
NOT	NOT success	Matches log events that do not contain success in their text.

Spaces are interpreted as AND operators, for example, searching for Mac OS will match the events which contain both the word Mac and the word OS somewhere in their text, but not necessarily in that order (use quotes to escape spaces and avoid this behaviour).

You can also search on specific attributes by typing a colon-separated attribute-value pair into the search bar, e.g., level:error or host:127.0.0.1, which matches all log events which contain the specified attribute with the specified value. Attribute-value text searches are case-sensitive.

Numerical Values

In order to search on a numerical attribute you can use numerical operators (<,>, <=, or >=) For instance, retrieve all logs that have a response_status greater than 400:

response_status:>400

Escape Special Characters, Keywords & Spaces

A search term must be wrapped in either single ' or double " quotes if it contains spaces or any of the following special characters: :, =, >, <, !.

Similarly, if your term is a reserved keyword (AND, OR, and NOT), then the term will need to be wrapped with quotes.

Switch to SQL mode, disable styling and autocomplete

Toggle the button to the right of the search bar to search in SQL mode where autocomplete and styling is removed. Note: switching from SQL back to Simple Mode requires either no changes to the existing query or clearing the search before toggling modes.

SQL Search Mode

Bronto also supports a subset of SQL as the query language to search and analyse your log data.

WHERE clause

The search bar corresponds to the WHERE clause of a SELECT SQL statement, and can be used to filter the results of your query. For example, you can search your firewall logs for users accessing a specific destination with

direction='outbound' AND destination_address='52.214.86.65'

which retrieves all events that have a field named direction with a value equal to 'outbound' and a field destination_address equal to '52.214.86.65'.

info

The single quotes in the above example are important, as otherwise "outbound" would be interpreted as a field rather than a string value.

Bronto automatically parses the fields in your log data, regardless of whether that log data is structured, semi-structured, or completely unstructured. You can view the fields for a log event in the inspector when you click on a log event.

Any character string that appears on its own is automatically expanded into an ILIKE expression that checks whether the string appears anywhere in the log event. To search for any event that contains "username" as well as either "500" or "404", you can query with

username AND (500 OR 404)

In this example, username is automatically expanded to @raw ILIKE '%username%'. This behaviour is the same for both quoted and unquoted character strings, so single quotes may be omitted.

Fields that begin with @ are reserved fields that contain some special information:

Field	Description
@raw	The full text of a log event in its raw form.
@time	The timestamp when Bronto received the event, in the format YYYY-MM-DD hh:mm:ss.sss UTC.
@timestamp	The timestamp when Bronto received the event, in milliseconds since the Unix epoch.
@origin	The ip address from which Bronto received the event.

Boolean operators

Boolean operators combine the result of other expressions that return a value of TRUE, FALSE or UNKNOWN.

Boolean Operator	Example	Description
AND	expr1 AND expr2	Combines two expressions and returns TRUE when both expressions are TRUE.
OR	expr1 OR expr2	Combines two expressions and returns TRUE when either expressions is TRUE.
NOT	NOT expr	Negates the result of the expression.

Comparison operators

Comparison operators compare one expression or value with another, and return a value of TRUE, FALSE or UNKNOWN.

Comparison Operator	Example	Description
[NOT] LIKE	field LIKE '%value%'	Returns TRUE if the left operand matches the specified pattern (right operand). The pattern can include regular characters or the % wildcard, which matches 0 or more of any character. For example, field LIKE 'foo%' returns any log events that start with "foo". The regular characters specified in the pattern are case sensitive and must match exactly. This operator is only applicable to the STRING data type. The result is negated if NOT appears before the LIKE operator.
[NOT] ILIKE	field ILIKE '%value%'	Same behaviour as the LIKE operator, uppercase/lowercase differences are ignored when matching a pattern.
=	field = value	Compares the equality of two expressions. Returns TRUE if both expressions are equal to each other.
<>	field <> value	Compares the equality of two expressions. Returns TRUE if the left operand is not equal to the right operand.
!=	field != value	Same as the <> operator. Compares the equality of two expressions. Returns TRUE if the left operand is not equal to the right operand.
<	field < value	Compares two expressions. Returns TRUE if the left operand has a lower value than the right operand, otherwise the result is False.
<=	field <= value	Compares two expressions. Returns TRUE if the left operand has a value lower than or equal to the right operand, otherwise the result is False.
>	field > value	Compares two expressions. Returns TRUE if the left operand has a greater value than the right operand, otherwise the result is False.
>=	field >= value	Compares two expressions. Returns TRUE if the left operand has a value greater than or equal to the right operand, otherwise the result is False.

Operator Precedence

When an expression has multiple operators, the operator precedence determines the sequence of operations. Operators have the precedence level shown in the table below, with level 1 being the highest precedence, and 4 being the lowest. An operator with a higher precedence is evaluated before an operator with a lower precedence.

Level	Operators
1	=, !=, <>, <, >, <=, >=
2	[NOT] LIKE, [NOT] ILIKE
3	NOT
4	AND
5	OR

Use parentheses to override the defined operator precedence in an expression.

Quotation Marks & Escaping Special Characters

In SQL mode, a term surrounded by single quotes ' will always be interpreted as a STRING literal, while a term surrounded by double quotes " will always be interpreted as a column. Any unquoted string of characters will always be interpreted as a column.

Your column must be wrapped in double quotes " if it contains any of the following special characters: =, !, <, >, -, +, *, /, %, ^, &, |.

Quotation marks (both single ' and double ") can be escaped by doubling them up, for example 'O''Brien' is a STRING literal with an apostrophe (O'Brien).

Data Types & Literal Values

Any expression in a query has a related data type which defines how they will be treated by operators.

Data type	Description	Literal Value
NUMERIC	Exact number data type for integer data. Valid for integers in the range -2^63 to 2^63-1.	Any unquoted sequence of digits.
DOUBLE	Approximate number data type for decimal data. Precise to 15 significant decimal digits.	Any unquoted sequence of digits with a single . character anywhere in that sequence.
STRING	Data type for variable length character strings that can consist of letters, numbers, and symbols.	Any sequence of characters surrounded by single quotes.

Implicit Data Type Conversions

Bronto automatically converts a value from one data type to another when such a conversion makes sense, for example, if your log event has an attribute duration=0.1291 it will match both duration<1 (LONG literal) and duration<'1' (STRING literal).

The rules for implicit data type conversion for binary operators are as follows:

Precedence	Condition	Implicit Conversion
1	Both sides are attributes.	No implicit conversion takes place.
2	One side is of type DOUBLE and the other side can be converted to DOUBLE.	Other side converted to DOUBLE.
3	One side is of type LONG and the other side can be converted to LONG.	Other side converted to LONG.
4	One side is an attribute with a STRING value and the other side is of type LONG or DOUBLE.	No implicit conversion takes place. Due to the schemaless nature of Bronto it is possible for an attribute to hold values with differing datatypes. This rule exists to avoid lexicographical STRING comparison when a numerical comparison was intended, for example, this means that if your log event has attribute duration equal to 'null' then duration>100 will NOT return TRUE (as it would under a lexicographical string comparison).
5	One side is of type STRING.	Other side is converted to STRING.
6	None of the previous conditions match.	No implicit conversion takes place.

String Functions

String functions perform operations on a string input value and return a string or numeric value.

Bronto supports the following string functions.

Function	Example	Description
LOWER	LOWER('Error')	Returns a string after converting any uppercase characters to lowercase. The function can be applied to a constant string or a column.

Regular Expressions

info

Regular expression operators are currently an early access feature which is disabled by default. Reach out to support if you would like to enable this feature for your account.

Regular expressions provide a powerful and flexible way to perform pattern matching. Compared to simple string matching, regex provides rich wildcards such as . (any character), \d (any number), \s (any whitespace), and [xyz] (any character that is either x, y or z), and repetition operators: * matches a sequence of zero or more string; + matches one or more; ? matches zero or none. For example, the regex cat* matches cat followed by any characters (or none at all). For example,

• cat.* matches "catapult"
• cat.* matches "The cat sat"
• h[aeiou]llo matches any of "hallo", "hello", "hollo"
• (?i)cat matches "cat", "CAT", "cAt" ((?i) is the case-insensitive inline modifier)
• [a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,} matches email addresses.

The precise regex syntax supported By Bronto is RE2, for which you can find the specification here. RE2 does not support some features provided by other regex variants (e.g., PCRE), such as positive & negative lookahead/lookbehind.

When using regex to query your log data, the regex pattern does not need to match the entire event exactly. For example, the pattern colou?r will match the log event "My favourite colour is red" - it is not necessary to write .*colou?r.*.

Operator	Example	Description
[NOT] REGEXP	field REGEXP '197\.149\.[12][0-59][0-9]\.[0-2].*'	Returns TRUE if the left operand matches the specified regex pattern (right operand). This operator is only applicable to the STRING data type. The result is negated if NOT appears before the REGEXP operator.
[NOT] RLIKE	field RLIKE '197\.149\.[12][0-59][0-9]\.[0-2].*'	Same behaviour as the REGEXP operator.
~	field ~ value	Same behaviour as the REGEXP operator.
!~	field !~ value	Same behaviour as the NOT REGEXP operator.
~*	field ~* value	Same behaviour as the REGEXP operator, but with a case insensitive modifier.
!~*	field !~* value	Same behaviour as the REGEXP operator, but with a case insensitive modifier.

Attribute Parsing With Regex Capture Groups

If Bronto has not automatically parsed the attributes in your data (for example, if you have an unstructured custom log format) and you have not set up the GROK parsing rules for your data source, then you can use named regex capture groups to parse attributes in your data.

A named capture groups is declared by using the following syntax:

(?<name>PATTERN)

where PATTERN is a regex pattern, and name denotes the attribute you want to parse.

For example, suppose you are working with this unstructured custom log:

20171223-22:15:36:154|Step_ExtSDM|30002312|calculateCaloriesWithCache totalCalories=127032
20171223-22:15:36:157|Step_ExtSDM|30002312|calculateAltitudeWithCache totalAltitude=240
20171223-22:15:36:157|Step_StandReportReceiver|30002312|REPORT : 7019 5011 150346 240
20171223-22:15:36:356|Step_LSC|30002312|onStandStepChanged 3592
20171223-22:15:36:365|Step_LSC|30002312|onExtend:1514038537000 1 0 4
20171223-22:15:36:657|Step_SPUtils|30002312| getTodayTotalDetailSteps = 1514038440000##7019##548365##8661##13406##27180468
20171223-22:15:36:657|Step_SPUtils|30002312|setTodayTotalDetailSteps=1514038440000##7020##548365##8661##13501##27180976
20171223-22:15:36:668|Step_ExtSDM|30002312|calculateCaloriesWithCache totalCalories=127054
20171223-22:15:36:672|Step_ExtSDM|30002312|calculateAltitudeWithCache totalAltitude=240
20171223-22:15:36:673|Step_StandReportReceiver|30002312|REPORT : 7020 5012 150368 240
20171223-22:15:37:351|Step_LSC|30002312|onStandStepChanged 3593

Each event has some structure - there are individual fields delimited by the "|" character, specifically:

1. the timestamp,
2. the component name,
3. the process id,
4. an unstructured message.

We can parse these attributes at query-time by running the following regex query:

@raw REGEXP '^(?<timestamp>.*?)\|(?<component_name>.*?)\|(?<pid>.*?)\|(?<message>.*?)$'

info

Named capture groups may only be used:

1. In aggregate functions, e.g., count, min, max.
2. In the group by clause.
3. For column selection.

Named capture groups cannot be reused as part of the query filter, for example it is not possible to write a query like

pid=30002312 AND @raw REGEXP '^(?<timestamp>.*?)\|(?<component_name>.*?)\|(?<pid>.*?)\|(?<message>.*?)$'

Now suppose that we are specifically interested in the log events which read REPORT:

20171223-22:15:36:157|Step_StandReportReceiver|30002312|REPORT : 7019 5011 150346 240
20171223-22:15:36:673|Step_StandReportReceiver|30002312|REPORT : 7020 5012 150368 240

which contain additional information on:

1. the number of steps,
2. the report number,
3. the total calories burned,
4. the altitude.

And suppose we want to find the maximum altitude across some time period. First we could restrict the filter to match only the events which contain "REPORT":

@raw LIKE '%REPORT%' AND @raw REGEXP '^(?<timestamp>.*?)\|(?<component_name>.*?)\|(?<pid>.*?)\|(?<message>.*?)$'

And then we could use nested capture groups to further parse the message, by changing the (?<message>.*?) element to

(?<message>REPORT : (?<num_steps>\d+) (?<report_num>\d+) (?<total_cals_burned>\d+) (?<altitude>\d+))

so that the full query is now

@raw LIKE '%REPORT%' AND @raw REGEXP '^(?<timestamp>.*?)\|(?<component_name>.*?)\|(?<pid>.*?)\|(?<message>REPORT : (?<num_steps>\d+) (?<report_num>\d+) (?<total_cals_burned>\d+) (?<altitude>\d+))$'

With this regex filter, the new attributes "timestamp", "report_num", "total_cals_burned", and "altitude" become available, and we can easily apply a max(altitude) function to our query.