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.

WHERE filtersdimensions_oneOrMore
GROUP BY dimensioncolumnsToShow_oneOrMore
HAVING expressionOverdimensionColumns_one
ORDER BY dimensioncolumnsToShow_oneOrMore
TIMESERIES (FOR dimensions_oneOrMore) (<n> minutes) 
BETWEEN time AND time

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)

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.


show (traces)

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

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


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.


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:

  • min: Displays the minimum value of a dimension.


    min(SpanDuration) as "Minimum Duration"

  • max: Displays the maximum value of a dimension.


    max(SpanDuration) as "Maximum Duration"

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


    avg(SpanDuration) as "Duration"

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


    sum(ErrorCount) as "Errors"

  • count: Displays the total count for a dimension.


    count(OperationName)as "Operation Count"

    count(*) as "Trace Count"

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


    count_distinct(sessionId) as "Sessions"

  • unique_values: Displays 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.


    unique_values(ApmrumPageUpdateType) as "Page Load/Update"

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


    span_summary() as Spans

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


    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.


    In a span query: time_bucket(15)

    In a trace query: time_bucket()

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


    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.

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


    histogram(spanDuration, 0, 6000, 3)

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


    percentile(traceDuration, 90)

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

  • total_items: This function 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: This function 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.


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

  • percent_of_items: This function 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.


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

  • percent_with_root_error: This function 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.


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

  • cast: This function allows the user 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.

    Example 1:

    cast(myTimeAttribute, 'EPOCH_TIME_MS')

    Example 2:

    cast(myDuration, 'DURATION_MS')

    Example 3:

    cast(myStorageSize, 'BYTES')

where Filters the results before they are grouped.


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

Here are the supported expressions:

  • Boolean AND of two expressions.


    ServiceName='service1' and OperationName='operation1'

  • Boolean OR of two expressions.


    ServiceName='service1' or ServiceName='service2'

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


    (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:

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


    ServiceName <> 'DoNotReturn'

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


    ServiceName in ('service1', 'service2')

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


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


    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.


    OperationName like ‘Ajax /path/%’

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


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.


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.


group by ErrorCount having ErrorCount = 0

order by Defines the ordering of the list by a particular dimension in ascending (asc) or descending (desc) order.


order by avg(ApdexScore) asc

This is an optional clause and if it's not specified, then the following order is displayed by default:

  • In a query for traces in which the group by clause is not specified, the default order is by the TraceFirstSpanStartTime dimension in descending order.
  • In a query for traces in which the group by clause is specified, the default order is determined by the dimension used to group the traces by in ascending order.
  • In a query for spans in which the group by clause is not specified, the default order is by the StartTime dimension in descending order.
  • In a query for spans in which the group by clause is specified, the default order is determined by the dimension used to group the spans by in ascending order.
Limit clause Limits the list to the specified number. If omitted, the default is FIRST 100 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.

The supported time ranges are 1 minute, 15 minutes, 60 minutes, and 720 minutes. 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.


timeseries for avg(TraceDuration) 60 minutes


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


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