Work with Queries in Trace Explorer

You can work with the out-of-the-box queries displayed in the Quick Picks in Trace Explorer or edit them to filter trace data by dimensions.

About Trace Explorer Queries

A query in Trace Explorer follows a simplified SQL-like syntax and consists of:

  • Clauses: Statements that filter, categorize, and display information in Trace Explorer.
  • Dimensions: Metadata or attributes defined by tagging a span, which enable the effective monitoring of the various tiers of an application. A complete list of dimensions is available in Fields in Trace Explorer.

The queries in Trace Explorer are a combination of clauses and dimensions, which you can use to filter trace data. You can either use the out-of-the-box queries available in each quick pick such as Traces and Services or edit these queries to further customize results to meet your specific requirements.

When editing queries, add or update clauses in the order given in the following syntax. The required clause is in italics and the other clauses are optional.

SHOW (TRACES) or (SPANS)
dimensioncolumnsToShow_oneOrMore 
WHERE filtersdimensions_oneOrMore
GROUP BY dimensioncolumnsToShow_oneOrMore
HAVING expressionOverdimensionColumns_one
ORDER BY dimensioncolumnsToShow_oneOrMore
FIRST <n> ROWS
TIMESERIES (FOR dimensions_oneOrMore) (<n> minutes) 
BETWEEN time expression AND time expression

In the syntax, note that ' ' (single quotes) are used for string constants and " " (double quotes) for quoted identifiers. If the identifier is a single word, then the double quotes are optional. Note that the dimension values added with various clauses such as where and group by are case-sensitive.

Arithmetic operations such as, addition, subtraction, multiplication, and division, can be used in expressions as you can see below:

show (traces) min(UserName) as "User Name",
    min(BrowserName) as "Browser Name",
    min(BrowserVersion) as "Browser Version",
    sum(PageResponseTime) as "Total Response Time",
    sum(PageViews) as "Page Views",
    sum(ErrorCount) as "Error Count",
    count(*) as "Traces",
    avg(ConnectTime) as "Connect Time",
    max(TraceLatestSpanEndTime) - min(TraceFirstSpanStartTime) as "Session Duration"  
where ApmrumPageUpdateType is not omitted OR ApmrumType='Connection'
group by SessionId
order by max(TraceLatestSpanEndTime) - min(TraceFirstSpanStartTime)

Query assist: When working with queries, a list of available attributes and functions appear after typing a space and pausing automatically. The query assist feature can be invoked by using CTRL+Space manually if needed.

Trace Explorer Query Language

Detailed information on the clauses that can be used in a Trace Explorer query is given in the following table:

Clause Description
show Specifies the source of the base data for the query, which can be spans, traces, or a request for trace or span dimensions.

Example 1: show (traces)

Example 2: show (spans)

This is not a required clause. If not specified, the query assumes that all traces must be displayed.

Nested queries and Subqueries: You can use nested queries with the show clause.

Example 1: show (show (show traces * ) count(*) as c ) c

Example 2: show traces traceid where serviceName in (show spans serviceName where serviceName is not omitted and operationName= 'A' group by serviceName first 10 rows)

Nested queries restrictions:
  • May not contain a selection clause. Use IN and NOT IN nested queries instead.
  • A nested query may not contain multi-pass operations (also known as dependent queries). Only the outermost show clause may contain them, in which case they run over the results of the nested query.
  • There is no default row limit for a nested query. By default, a nested query will consider all the rows. See above example 1.

    If needed, fewer rows may be requested by adding an explicit first n rows clause to the nested query.

  • May not refer to values in an outer query (no correlated nested query).
Subqueries restriction:
  • A subquery used in an expression, as seen in above example 2, must have a first n rows clause or the query will return an error. The maximum number of rows that can be requested in an IN or NOT IN expression is 1 million.

Selection clause

Finds a set of traces or spans that will be searched by the rest of the query.

Syntax: FROM <virtualTable> WHERE <whereClause> FIRST n rows

Example:

show traces from spans where serviceName='a' first 100 rows *

A limit is required in this clause, as only a limited number of items may be used for the search, and this clause can only be used inside a trace query.

Returned Expressions clause Specifies which trace or span dimensions or aggregate functions must be displayed as columns in the list. The as identifier optionally provides an alias for each column heading. Note that the alias must be unique.

Example:

ServiceName as Service

Note that if there is a space after the as identifier, the column name value must be within quotes " ".

Here are the supported functions that can be used with this clause:

  • abs: Returns the absolute value of a numeric expression.

    Example:

    abs(errorcount)

  • avg: Returns the average value of a numeric dimension.

    Example:

    avg(SpanDuration) as "Duration"

  • cast: Allows to specify a unit for an expression, taking the expression and the desired unit as arguments. This may cause the user interface to render the returned value differently.

    Syntax: cast(expression, unit)

    • expression is the input data to be represented. It must be numeric or time expressions.
    • unit is the unit that determines how the input is displayed/transformed to the output. The available values are:
      • EPOCH_TIME_MS: Time in milliseconds.
      • DURATION_MS: Duration in milliseconds.
      • BYTES: Size in bytes.

    Example 1:

    cast(myTimeAttribute, 'EPOCH_TIME_MS')

    Example 2:

    cast(myDuration, 'DURATION_MS')

    Example 3:

    cast(myStorageSize, 'BYTES')

  • ceil: Returns the next highest long value if the expression has a non-zero fractional part, otherwise it returns the value as a long.

    Example:

    ceil(traceDuration)

  • conditional_count: Returns the total count (number) of a conditional expression.

    Syntax: conditional_count(conditional expression)

    Examples:

    conditional_count(dimensionA = 'abc')

  • count: Returns the total count for a dimension.

    Examples:

    count(OperationName)as "Operation Count"

    count(*) as "Trace Count"

  • count_distinct: Returns the number of distinct values for a dimension.

    Example:

    count_distinct(sessionId) as "Sessions"

  • date_bucket: Returns a transformed time expression.
    Syntax: date_bucket(numeric expression, string)
    • numeric expression is the time input which must be a numeric that has a unit EPOCH_TIME_MS.
    • string is the string that determines how the time input is transformed to the output. The valid options available are:
      • 'day_of_week': Valid return values are 1-7 indicating the UTC day of the week (Sunday is 1).
      • 'day_of_month': Valid return values are 1-31 indicating the UTC day of the month.
      • 'day_of_year': Valid return values are 1-366 based on UTC.
      • 'month_of_year': Valid return values are 1-12 based on UTC.
      • 'hour_of_day': Valid return values are 0-23 based on UTC.
      • 'minute_of_hour': Valid return values are 0-59.
      • 'calendar_quarter_of_year': Valid return values are 1-4 based on UTC.
      • 'truncate_day': Valid return time value is the current day at midnigh UTC.
      • 'truncate_hour': Valid return value is the beginning of the hour UTC.
      • 'truncate minute': Valid return value is the beginning of the minute UTC.
  • floor: Truncates fractional part of the expressions value and returns a long.

    Example:

    floor(traceDuration)

  • histogram: Produces a histogram of the data in numerical attributes. It distributes the data in buckets of equal widths (histogram(numeric_attr, min_value, max_value, num_buckets)).

    Example:

    histogram(spanDuration, 0, 6000, 3)

  • max: Returns the maximum value of a dimension.

    Example:

    max(SpanDuration) as "Maximum Duration"

  • min: Returns the minimum value of a dimension.

    Example:

    min(SpanDuration) as "Minimum Duration"

  • mod: Returns the remainder of expression2 divided by expression1. If expression1 is 0, it returns the value of expression2.

    Syntax: mod(expression1, expression2)

    This function takes as arguments any numeric data type or any non-numeric data type that can be implicitly converted to a numeric data type. The argument with the highest numeric precedence is determined, and the remaining arguments are implicitly converted to that data type, and that data type is returned.

    Example:

    mod (errorcount, spancount)

  • omittedTo: Provides the option to assign a value to a dimension that does not have a value.

    Example:

    omittedTo(UserName, 'John Doe')

    In the example above, if the UserName dimension has a value assigned, then the assigned value is displayed in the results, and if a value is not assigned, then John Doe is displayed.

  • percent_of_items: Returns the percentage of the traces or spans represented by the current row. The value of percent_total_items() is equal to count(*)/total_items()*100.

    Example:

    show traces serviceName, count(*), total_items(), percent_of_items() group by serviceName

  • percent_with_root_error: Returns the percentage of completed traces that have a root span marked with an error. This value is equal to sum(traceRootSpanErrorCount)/count(traceRootSpanErrorCount)*100.

    Example:

    show traces serviceName, sum(traceRootSpanErrorCount), count(traceRootSpanErrorCount), percent_with_root_error() group by serviceName

  • percentile: Returns the approximate percentile value for a numeric attribute ((numeric_attr, 0-100) ).

    Example:

    percentile(traceDuration, 90)

    NOTE: this is currently implemented in terms of the oracle database approx_percentile() function, and computes an approximate percentile value.

  • rate: Returns the count() over time.

    Example:

    show traces rate() between now()-1 hour and now() timeseries 5 minutes

    From the above example, the result of the main query is calculated over 60 minutes so the value shown in the result will be count()/60. When the time series is computed, each data point computes a count for 5 minutes, so the rate value for each point will be count()/5. This gives a rate per minute both in the overall result, and for each point in the timeseries.

  • regexp_count: Returns the total number of times a pattern occurs in the source string starting from the specified position. It returns 0 if no match is found in the source string starting from the search position, or returns the number of occurrences of the search pattern in the source string from the given start position.
    Syntax: regexp_count (expression, pattern, position, match_parameter)
    • expression is a string expression. A valid string expression (source char) is either a valid attribute that is active or a string expression that is created using attributes and operators (such as serviceName || operationName).
    • pattern is a valid regular expression pattern which will be used for the pattern to validate and search.
    • position is a positive integer indicating the position of the source char where the search should begin. After finding the first occurrence, it searches for the second occurrence after the first one.
    • match_parameter is the match parameter which indicates the match behavior.
      The supported values are:
      • i: Specifies case insensitive matching.
      • c: Specifies case sensitive and accent sensitive matching.
      • n: Allows period (.) in the regular expression. If this character is omitted, then the period does not match the new line character.
      • m: The source string is treated as multiple lines. caret (^) is the start and $ is the end of any line anywhere in the source string, rather than only at the start of end of the entire source string. If this parameter is omitted, then the source string is treated as a single line.
      • x: Ignores whitespace characters. By default, whitespace characters match themselves.

    Example:

    show TRACES traceId where REGEXP_COUNT(traceId, 'a', 7, 'c') >= 2

    In the above example, the query searches for traces that have 2 or more occurrences of the char:a within the traceId with the search starting at position 7 of the traceId.

    Note

    You can also provide only 2 arguments:

    regexp_count (expression, pattern)

    Example: show TRACES traceId where REGEXP_COUNT(traceId, 'a') >= 2

    The above example searches for traces that have 2 or more occurrences of the pattern/char: a within the traceId.

  • regexp_instr: Searches for a regular expression pattern in a given expression and returns the starting position of the first char of the pattern in the string.
    Syntax: regexp_instr (expression, pattern)
    • expression is a string expression. A valid string expression is either a valid attribute that is active (such as traceId and serviceName) or a string expression that is created using attributes and operators (such as serviceName || operationName).
    • pattern is a valid regular expression pattern which will be used for the pattern to validate and search.

    Example:

    show TRACES REGEXP_INSTR(serviceName, 'cli') as RegexInstr, traceId

    In the above example, the query searches for traces that have serviceName values that start with the pattern/char: cli.

  • regexp_like: Performs regular expression matching on the given string expression and returns the string expressions (attributes) that have matched.

    Syntax: regexp_like (expression, pattern, match_parameter)

    • expression is a string expression. A valid string expression is either a valid attribute that is active (such as traceId and serviceName) or a string expression that is created using attributes and operators (such as serviceName || operationName).
    • pattern is a valid regular expression pattern which will be used for the pattern to validate and search.
    • match_parameter is the match parameter which indicates the match behavior. This is optional.
      The supported values are:
      • i: Specifies case insensitive matching.
      • c: Specifies case sensitive and accent sensitive matching.
      • n: Allows period (.) in the regular expression. If this character is omitted, then the period does not match the new line character.
      • m: The source string is treated as multiple lines. caret (^) is the start and $ is the end of any line anywhere in the source string, rather than only at the start of end of the entire source string. If this parameter is omitted, then the source string is treated as a single line.
      • x: Ignores whitespace characters. By default, whitespace characters match themselves.

    Examples:

    • show TRACES traceId where REGEXP_LIKE(traceId, 'aa')

      In the above example, the query searches for traces that have traceId values that match the pattern/char: aa.

    • show TRACES traceId where REGEXP_LIKE(traceId, '^aa', 'c')

      In the above example, the query searches for traces that have traceId values that match the pattern/char: aa in lower case (case sensitive).

  • regexp_replace: Searches for a source pattern in a given string and returns a string with any occurrence of the source pattern replaced with the given pattern.
    Syntax: regexp_replace (expression, pattern, replace_string, numeric expression)
    • expression is a string expression. A valid string expression (source char) is either a valid attribute that is active (such as traceId or serviceName) or a string expression that is created using attributes and operators (such as serviceName || operationName).
    • pattern is a valid regular expression pattern which will be used for the pattern to validate and search.
    • replace_string is the string that replaces the occurrences of the source string.
    • numeric expression is a number or a valid numeric expression which indicates the occurrence of the source string to be replaced. This is optional.
    Examples:
    • show TRACES regexp_replace(traceId, 'aa', 'ee') as traceIdReplaced, traceId where REGEXP_LIKE(traceId, 'aa')

    • show TRACES regexp_replace(traceId, 'a', 'apm', 3) as traceIdReplaced, traceId where REGEXP_LIKE(traceId, 'a')

    Note

    You can also provide 5 arguments.

    regexp_replace (expression, pattern, replace_string, numeric expression1, numeric expression2)

    In this case, numeric expression1 is a number or a valid numeric expression which indicates the occurrence of the source string to start search from, and numeric expression2 is a number or a valid numeric expression which indicates the occurrence of the source string to be replaced.

    For example, use the below query to start the pattern match with the first occurrence of the pattern/char a, and replace the second occurrence of a with the apm string.
    show TRACES regexp_replace(traceId, 'a', 'apm', 1, 2) as traceIdReplaced, traceId where REGEXP_LIKE(traceId, 'aa')
  • regexp_substr: Searches for a regular expression pattern in a given string expression or attribute and returns the substring from that string expression or attribute.
    Syntax: regexp_substr (expression, pattern)
    • expression is a string expression. A valid string expression (source char) is either a valid attribute that is active (such as traceId or serviceName) or a string expression that is created using attributes and operators (such as serviceName || operationName).
    • pattern is a valid regular expression pattern which will be used for the pattern to validate and search.

    Example 1:

    show TRACES REGEXP_SUBSTR(serviceName, 'cli') as RegexSubstr, traceId

    The above example searches for traces that have serviceName that start with the pattern/char: cli.

    Example 2:

    show TRACES traceId, serviceName, REGEXP_SUBSTR(serviceName, 'RUM') as RegexSubstr where REGEXP_SUBSTR(serviceName, 'RUM') = 'RUM'

    The above example searches for traces that have serviceName containing 'RUM' and strip out RUM from the serviceName and return that back.

  • span_summary: Returns a summary of the spans in a trace. This function can only be used with show(traces) and in non-grouped queries.

    Example:

    span_summary() as Spans

  • stddev: Returns the standard deviation of the given expression.

    Example:

    stddev(traceDuration+1)

  • substr: Returns a substring from the given string.
    Syntax: substr (expression, numeric expression1, numeric expression2)
    • expression is a string expression. A valid string expression (source char) is either a valid attribute that is active (such as traceId or serviceName) or a string expression that is created using attributes and operators (such as serviceName || operationName).
    • numeric expression1 (start position) is a numeric expression that evaluates to a number or a number which indicates the start position of the substring.

      If start position is 0, then it is treated as 1.

      If start position is positive, then the function counts from the beginning of the char to find the first character. If it's negative, then the function counts backward from the end of the char.

    • numeric expression2 (length) is a numeric expression that evaluates to a number or a number which indicates the length of the substring starting from the start position of the substring.

      The length is always positive and will only return as many characters as exist in the value.

      If length is less than 1, then null is returned. If length is greater than the length of the string expression (source string), then the entire string is returned.

    Example:

    show TRACES SUBSTR(traceId, 0, 1) as traceShortId, traceId

    The above example searches the traceShortId with the traceShortId length 1, starting at position 1 of the traceId.

  • sum: Returns the aggregate value of a numeric dimension.

    Example:

    sum(ErrorCount) as "Errors"

  • time_bucket: Given the time attribute for the table, (StartTime or TraceStartTime) and a supported time grain, the function returns a bucket number into which the row falls. This is mostly for use in expressing time series queries.

    Example:

    In a span query: time_bucket(15, StartTime)

    In a trace query: time_bucket(15, TraceStartTime)

    A time_bucket() value can be converted back to a unix epoch time in milliseconds using the following java expression: bucketNumber * TimeUnit.MINUTES.toMillis(bucketInMinutes);

  • time_bucket_start: Returns the start of the time bucket as a time value in milliseconds since epoch (unit: EPOCH_TIME_MS). It can be called with a single argument, the size of the bucket in minutes, or with no arguments, in which case the system will determine the bucket size based on the time span of the query.

    Example:

    In a span query: time_bucket(15)

    In a trace query: time_bucket()

  • total_items: Returns the total number of traces or spans considered by the query.

    Example 1:

    show traces count(*), total_items()

    Example 2:

    show traces count(*), total_items() group by service name

    NOTE: In example 1 count(*) and total_items() are the same because the query is not grouped. In example 2, count(*) refers to the number of items in the group for the row, while total_items() has the same value as in example 1.

  • total_rows: Returns the total number of rows that would appear in the result set if FIRST x ROWS could be set to unlimited.

    This can be useful when looking for the cardinality of a certain combination of dimensions.

    Example:

    show spans total_rows() where component = 'SERVLET' group by serviceName, operationName first 1 rows

  • unique_values: Returns the unique values of a dimension and the number of times each of the values occur. If a dimension has five or less unique values, then they are displayed in a pie chart. If a dimension has more than five unique values, a link is displayed in the column, which you can click to view the list of unique values.

    Example:

    unique_values(ApmrumPageUpdateType) as "Page Load/Update"

where Filters the results before they are grouped.

Example:

where ApmrumPageUpdateType is not omitted OR ApmrumType='Connection' OR ApmrumType='Script Error'

Here are the supported expressions:

  • Boolean AND of two expressions.

    Example:

    ServiceName='service1' and OperationName='operation1'

  • Boolean OR of two expressions.

    Example:

    ServiceName='service1' or ServiceName='service2'

  • Grouping () to determine the order of expressions in a complex expression.

    Example:

    (ServiceName ='a' or ServiceName='b') and (OperationName='a' or OperationName='b')

  • Comparison of a dimension made via a dimension reference and a constant value. The supported operations are:

    =,<>, >, >=, <, <=

    Example:

    ServiceName <> 'DoNotReturn'

  • IN value expression which compares the dimension with the listed values.

    Example:

    ServiceName in ('service1', 'service2')

  • IN and NOT IN value expression which compares multiple dimensions with listed values.

    Example:

    (ServiceName, OperationName) in (('service1', 'operation1'))

  • IS OMITTED expression to test if a dimension value is included or IS NOT OMITTED to test if a dimension value is missing.

    Example:

    OperationName is omitted

  • LIKE expression to find matches of a particular dimension value or NOT LIKE to search for dimension values except for the one specified.

    Example:

    OperationName like ‘Ajax /path/%’

group by Groups the results by a certain dimension and displays them in rows.

Example:

group by ServiceName

If the group by clause is a part of a query with timeseries or unique_values, then the time series or unique_values() columns are not displayed in the result rows in which the group by dimension does not have a value.

Example:

show (traces) UserName as "User Name", unique_values(OperationName), count(*) group by UserName timeseries

In the example, the spans are grouped by the UserName dimension, and if a span does not have a value assigned to the UserName dimension, then that row does not display the timeseries and unique_values columns.

having Further filters the results grouped by the group by clause.

Syntax: group by dimension having expression

expression: The expression in the having clause can refer to either the expressions in the group by clause or to aggregates about the groups.

Here are the supported expressions:

  • Boolean AND of two expressions.

    Example:

    ServiceName='service1' and OperationName='operation1'

  • Boolean OR of two expressions.

    Example:

    ServiceName='service1' or ServiceName='service2'

  • Grouping () to determine the order of expressions in a complex expression.

    Example:

    (ServiceName ='a' or ServiceName='b') and (OperationName='a' or OperationName='b')

  • Comparison of a dimension made via a dimension reference and a constant value. The supported operations are:

    =,<>, >, >=, <, <=

    Example:

    ServiceName <> 'DoNotReturn'

  • IN value expression which compares the dimension with the listed values.

    Example:

    ServiceName in ('service1', 'service2')

  • IN and NOT IN value expression which compares multiple dimensions with listed values.

    Example:

    (ServiceName, OperationName) in (('service1', 'operation1'))

  • IS OMITTED expression to test if a dimension value is included or IS NOT OMITTED to test if a dimension value is missing.

    Example:

    OperationName is omitted

  • LIKE expression to find matches of a particular dimension value or NOT LIKE to search for dimension values except for the one specified.

    Example:

    OperationName like ‘Ajax /path/%’

Examples:
  • group by errorCount having ErrorCount = 0
  • show traces errorCount, count(*) group by errorCount having errorCount < 10 and count(*) = 10
order by Defines the ordering of the list by a particular dimension in ascending (asc) or descending (desc) order.

Example:

order by avg(ApdexScore) asc

This is an optional clause and if it's not specified, then the results are unordered.

Limit clause Limits the list to the specified number. If omitted, the default is FIRST 100 ROWS.

Example:

FIRST 5 ROWS

timeseries Displays a times series graph for the dimensions selected in the select clause for a defined time range. You can determine the dimension for which you want to see the time series graph in the timeseries clause, and if a dimension is not specified, then the time series graphs are displayed for each numeric dimension in the Returned Expressions clause.

If the time range (<n> minutes) is not added to the clause, then the time range is based on the time period selected in Trace Explorer.

Examples:

timeseries for avg(TraceDuration) 60 minutes

timeseries

between Displays results for the selected timestamps in UTC time. If this clause is used, it overrides the time period selected in Trace Explorer.

Example:

BETWEEN 2021-02-17T01:38:49.318Z AND 2021-02-18T01:38:49.318Z

BETWEEN can also take a time expression instead of a timestamp.

Example:

BETWEEN queryStartTime() AND queryEndTime()

time expression Displays a time or a duration in milliseconds.