Advanced Log Filtering
Querying the logs
Understanding field references
The log tables are queried with a subset of BigQuery SQL syntax. They all have three columns: event_message
, timestamp
, and metadata
.
column | description |
---|---|
timestamp | time event was recorded |
event_message | the log's message |
metadata | information about the event |
The metadata
column is an array of JSON objects that stores important details about each recorded event. For example, in the Postgres table, the metadata.parsed.error_severity
field indicates the error level of an event. To work with its values, you need to unnest
them using a cross join
.
This approach is commonly used with JSON and array columns, so it might look a bit unfamiliar if you're not used to working with these data types.
Expanding results
Logs returned by queries may be difficult to read in table format. A row can be double-clicked to expand the results into more readable JSON:
Filtering with regular expressions
The Logs use BigQuery Style regular expressions with the regexp_contains function. In its most basic form, it will check if a string is present in a specified column.
There are multiple operators that you should consider using:
Find messages that start with a phrase
^
only looks for values at the start of a string
Find messages that end with a phrase:
$
only looks for values at the end of the string
Ignore case sensitivity:
(?i)
ignores capitalization for all proceeding characters
Wildcards:
.
can represent any string of characters
Alphanumeric ranges:
[1-9a-zA-Z]
finds any strings with only numbers and letters
Repeated values:
x*
zero or more x
x+
one or more x
x?
zero or one x
x{4,}
four or more x
x{3}
exactly 3 x
Escaping reserved characters:
\.
interpreted as period .
instead of as a wildcard
or
statements:
x|y
any string with x
or y
present
and
/or
/not
statements in SQL:
and
, or
, and not
are all native terms in SQL and can be used in conjunction with regular expressions to filter results
Filtering and unnesting example
Filter for Postgres
Limitations
Log tables cannot be joined together
Each product table operates independently without the ability to join with other log tables. This may change in the future.
The with
keyword and subqueries are not supported
The parser does not yet support with
and subquery statements.
The ilike
and similar to
keywords are not supported
Although like
and other comparison operators can be used, ilike
and similar to
are incompatible with BigQuery's variant of SQL. regexp_contains
can be used as an alternative.
The wildcard operator *
to select columns is not supported
The log parser is not able to parse the *
operator for column selection. Instead, you can access all fields from the metadata
column: