| 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:
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)
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"
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.
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"
|