Work with Queries in Trace Explorer

You can work with the out-of-the-box queries displayed on the Quick Pick tabs 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 tab 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 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.

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:

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

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:

  • min: Displays the minimum value of a dimension.

    Example:

    min(SpanDuration) as "Minimum Duration"

  • max: Displays the maximum value of a dimension.

    Example:

    max(SpanDuration) as "Maximum Duration"

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

    Example:

    avg(SpanDuration) as "Duration"

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

    Example:

    sum(ErrorCount) as "Errors"

  • count: Displays the total count for a dimension.

    Examples:

    count(OperationName)as "Operation Count"

    count(*) as "Trace Count"

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

    Example:

    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.

    Example:

    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.

    Example:

    span_summary() as Spans

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

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

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 value expression which compares multiple dimensions with listed values.

    Example:

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

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

    Example:

    OperationName is omitted

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.

Example:

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.

Example:

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.

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.

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.

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