Download PDF
Download page Analytics Functions.
Analytics Functions
Functions serve as arguments in the SELECT clause. Function types in ADQL include:
- Metric functions
- Bucketing functions
- String functions
- Funnel functions
- Data type conversion functions
- Functions that modify other functions
For many functions, only numeric fields are valid. "All" data types means integer, floating point, datetime, and string.
Functions ignore null values, such as those resulting from integer division by zero or empty data fields.
To add a WHERE clause to a query, use the query box in the data tab of the Controller Analytics search UI. When you perform the search, the visualization tab under the table widget shows the results of any metric functions.
Each section below describes one type of function.
Metric Functions
Metric functions:
- Perform a calculation on a set of values for a field.
- Return a result such as a count or sum, or a set of results such as percentile.
- Can only be in the SELECT clause.
- Should always follow a field name if one exists—otherwise, not all visualizations may work.
The distinctcount() function is accurate for up to 3000 unique return values. For over 3000 unique return values, distinctcount() is approximate due to performance optimizations.
Function | Returns | Valid Field Type(s) | ADQL Query Examples |
---|---|---|---|
avg(numeric_field_name) | Average value for a numeric field. | integer, floating point, datetime | SELECT |
count(field_name) | A count of events. | all The form count(*) is also valid. | SELECT The following query returns the top 10 IP addresses sending HTTP requests to an application. SELECT ip, |
distinctcount(field_name) | A count of the number of unique values recorded for a field. | all | SELECT |
max(numeric_field_name) | The maximum recorded value of a numeric field. | integer, floating point, datetime Specify one field only. | SELECT SELECT nodeName, |
min(numeric_field_name) | The minimum recorded value of a numeric field. | integer, floating point, datetime Specify one field only. | SELECT |
stdev(numeric_field_name) | The population standard deviation of a numeric field. | integer, floating point | SELECT stdev (numeric_field_name)FROM transactions |
sum(numeric_field_name) | The sum of the values of a numeric field. | integer, floating point, datetime Specify one field only. | SELECT |
percentile(numeric_field_name, percent) | The specified percentile values between 0 and 100. | integer, floating point, datetime Specify one or more percentiles. | SELECT |
stats(numeric_field_name) | Statistics for a field: | integer, floating point, datetime | SELECT |
totalResultCount() | The total count of events. | Does not accept a field. | This query first selects all error transactions for the "ECommerce" application. It then calculates, for each unique transaction name, the ratio of instances of that transaction name to the total number of error transactions. (The assumption is that any given transaction name can appear in multiple error transactions.) SELECT |
Bucketing Functions
You will need to contact your administrator.
SELECT series
(eventTimestamp, '1h'), count
(*) FROM transactions WHERE application='yourAppName'
To include a larger data set in your results, use the time range selector in the upper right corner of the Controller Analytics search UI.
Dates are different from datetime values. Units from weeks down to milliseconds are supported:
'1w'
for one week'10d'
for ten days'2h'
for two hours'5m'
for five minutes'36s'
for thirty-six seconds'800ms'
for eight hundred milliseconds
When you use dates to define buckets, think about how many buckets your definition produces. The number of buckets for series() cannot exceed 2000, and much smaller numbers of buckets are the norm.
Function | Returns | Valid Field Type(s) | ADQL Query Examples |
---|---|---|---|
range(field_name, n|(n1, n2), n3|(n4, n5), ...) | One or more buckets. | integer, floating point, dates Use a single value See below for explanation of how to define buckets. | This range query: SELECT Results in the following buckets: [-Inf, 0], [0, 10], [10, 20], [20, +Inf] |
If you specify bounds, then all buckets are returned, empty or not. If you do not specify bounds, then:
| A series of buckets based on the provided interval. | integer, floating point, dates For numeric fields, specify the interval as an integer. See below for explanation of optional keyword arguments | SELECT SELECT |
Defining Buckets for range()
Every bucket is defined as bounded by two single values, where the bucket includes the first value and excludes the second.
Pairs of values (x, y)
define an explicit bucket from x
, inclusive, to y
, exclusive.
A single value x
implicitly defines a bucket according to rules that depend on where the value is placed among the arguments to range()
.
- Rules for using a single value to define a bucket:
- Immediately after the field (that is, as the second argument): The bucket starts at negative infinity.
- After another single value: The bucket starts at the previous argument, inclusive, and extends to
x
, exclusive. - After a pair of values, and before another single value: The bucket starts at x, inclusive, and extends to the following argument, exclusive.
- At the end of the parameter list: The bucket ends at positive infinity. This is the one exception to the assumption that
x
is the second of two values. - Between two pairs of values: No bucket can be defined, and the value is ignored.
Range Query Example
Here is a range query that exercises most of the rules above:
SELECT range
(segments.transactionTime, 0, (10, 20), 30, (40, 50), 60, 70) FROM transactions
The query produces the following buckets:
- [-Inf, 0] because rule a applies to 0
- [10, 20] because (10, 20) defines that bucket explicitly
- no bucket for 30 because rule e applies so 30 is ignored
- [40, 50] because (40, 50) defines that bucket explicitly
- [60, 70] because rule c applies to 60
- [70, +Inf] because rule d applies to 70
Optional keyword arguments for series():
offset
, rangeStyleBucketKeys
, and strictEndpoints
Series supports the following optional keyword arguments.
offset
Changes the start value of each series from 0 by the specified positive (+) or negative offset (-) duration, such as 1h for an hour, or 1d for a day. The values alignStart
and alignEnd
align buckets to the start and end of the extended bounds arguments respectively. The function must have extended bounds specified to use these special values. Defaults to 0. Accepts both integer and string values.
Examples:
SELECT series
(responseTime, 1, 1, 10, offset
="alignStart"), count
(*) FROM transactions
SELECT series
(responseTime, 100, 1, 1000, offset=1), count
(*) FROM transactions
rangeStyleBucketKeys
Converts the start and end values in a numeric series to "bucketStart - bucketEnd" format as a string type. For example, "0-10", "10-20", and so on. This output this argument returns is similar to what returned by the range function. The series function must be acting on a numeric type to use this keyword argument. Defaults to false. Accepts a boolean only.
Example:
SELECT series
(responseTime, 100, 1, 1000, rangeStyleBucketKeys
=true), count
(*) FROM transactions
strictEndpoints
Adds a filter on the query starting at the minimum extended bound and extending to the maximum extended bound. This ensures that no buckets outside of the extended bounds are present in the results. Defaults to false. Accepts a boolean only. Extended bound forces the histogram aggregation to start building intervals on a specific minimum value and expands the interval up to a given maximum value.
Example:
SELECT series
(responseTime, 2, 1, 5, strictEndpoints=true, rangeStyleBucketKeys=true), count
(*) FROM transactions
Selecting the Top n Results for Time Series Queries
Deployment Support

Top n results is available for SaaS deployments only.
series
(eventTimestamp, '1m'), transactionName, count
(*) FROM transaction LIMIT 1, 12The series function can compute the global top n over an entire period. To set a top n limit, add a LIMIT clause to the end of your query:This example limits the query to the top 12 results of transactionName. The value 1 is applied to the series
function, but because series is a bucketing function, it does not actually use the limit. You must include a value for the series
function.
The global top n limit does not apply when queries:
- Contain a selection before the series function
SELECT application,series
(eventTimestamp, '1m'), transactionName,...
- Have additional bucketing functions
SELECTseries
(eventTimestamp, '1m'),range
(responseTime, 0, 60, 120), transactionName,...
- Include a HAVING clause
SELECTseries
(eventTimestamp, '1m'), transactionName,avg
(responseTime), ... HAVING a >15
String Functions
ADQL string manipulation functions:
- Resemble their SQL counterparts.
- Are case-sensitive.
- Return null if any argument is null.
- Use 1-based, not 0-based, indexing.
- For example, the
indexOf()
function gives the index of the first character of a string as 1, not 0. - As a result, 0 is not a valid value for any integer argument
- For example, the
String functions can transform any event data that resides in the Analytics Events Service. One important use case is doing ad hoc data transformation when the structure, serialization, or format in which data has been collected needs to be changed.
For AppDynamics < 4.5.2, fixing incorrectly-collected data requires adjusting collection settings.
You can use string functions to:
- Hone in on a
substring()
by working with stringlength()
and/or theindexOf()
characters in a string- For example, you can extract the domain name from a URL
- Combine multiple strings using the
concat()
operator- For example, you can combine values from multiple fields into a single value
trim()
whitespace from the beginnings and ends of fieldsPair the output of the
substring()
function, item by item, with the output of another functionThis implicit aggregation is the ADQL equivalent of GROUP BY in SQL
See the Implicit Aggregation Example:
Function | Returns | Valid Field Type(s) | ADQL Query Examples |
---|---|---|---|
concat(inputString1, string inputString2, ... inputStringN) | A single string which combines the input strings. | string Specify two or more strings. | Function: concat('foo', 'bar') Result:
|
indexOf(inputString, substring, [occurrence]) | 1-based start index of If If If | string for occurrence | Function:
Result:
Function:
Result:
Function (example of chaining and combining functions): SELECT Result:
|
length(inputString) | The number of characters including whitespace of the input string. | string | Function: length(' foo bar ') Result: 9 |
substring(inputString, startIndex, numChars) | The substring of If If If | string for positive or negative int for positive int for
| This example extract the domain name from a URL. Function: substring('https://example.com/home.htm', 9, indexOf('https://example.com/home.htm','/',3) - 9) Result: 'example.com' |
trim(inputString) | A copy of the input string with leading and trailing whitespace removed. | string | Function: trim(' foo bar ') Result: 'foo bar' |
Implicit Aggregation Example
Consider this data:
Application ID | responseTime (ms) |
---|---|
SJC-001 | 500 |
SJC-002 | 600 |
SJC-003 | 700 |
LAX-001 | 200 |
LAX-002 | 300 |
LAX-003 | 400 |
The following ADQL query pairs each item from the output of substring()
with a corresponding item from the output of avg()
:
SELECT substring(application, 1, 3)
, avg(responseTime)
FROM transactions
The results are:
SJC, 600
LAX, 300
Notice how ADQL uses substring()
to govern the groupings to which the second function, avg()
, is applied.
Funnel Functions
You can create funnel widgets from an ADQL query using the funnel function. This query example creates a funnel widget of normal transactions:
SELECT funnel(transactionName, responseTime < 90, showHealth=true, health="NORMAL") FROM transactions
To see the funnel widget, enter your query and select Search. After you see the search results, select Basic at the top right of the query:
Data Type Conversion Functions
Data type conversion functions are only available for SaaS deployments.
Data type conversion functions can be used in the SELECT or WHERE clauses. Below are the available data type conversion functions. For formatString
requirements, see Joda's DateTimeFormat
documentation in the Classes section.
Function | Returns | ADQL Query Example |
---|---|---|
toDate(string value, [string formatString]) | A string value formatted as a datetime. | SELECT Returns 2019-01-15T00:00:00.000Z for a |
toDate(int value) | An integer value formatted as a datetime. | SELECT Returns Dec 31 1969 19:25:41 GMT-0800 |
toString(datetime value, [string formatString]) | A datetime value formatted as a string. | SELECT Returns "Jan 15, 2019" for a |
toString(float/int/bool value) | A value formatted as a string. | SELECT Returns "123.123" |
toInt(datetime/float/string/bool value) | An integer value, formatted as follows:
| SELECT Returns 123 |
toFloat(int/string value) | An integer or string value formatted as a float. | SELECT Returns 123123.0 |
round(float value, int decimalPlaces) | A value rounded off to decimalPlaces . decimalPlaces must be a non-negative integer. | SELECT Returns 123.123 |
ifNull(object value, object replacementValue) | A value if the value is non-null. If the value is null, returns replacementValue . | SELECT Returns 10 if the value of Returns Value of |
ifNull)
, if the value of field_name
returns null, the converted data type will also return null.Use mathematical and now()
functions on datetimes
The now()
function is only supported on SaaS Controllers. It is not supported on on-premise deployments.
You can perform addition and subtraction mathematical functions on datetime and long values, for example:
SELECT toDate(field_name, "MMM, dd, yyyy") - toDate(field_name,"MMM, dd, yyyy") FROM transactions |
The function will return a datetime value. Multiplication, division, and modulus mathematical functions are not supported. Addition and subtraction functions cannot be combined with floats.
You can use the now()
function to add or subtract the server's current time from a value, for example:
SELECT toInt(now() - field_name) FROM transactions |
The above function determines how long ago field_name
was created and converts the value to an integer.
Functions That Modify Other Functions
Function | Returns | Valid Field Type(s) | ADQL Query Examples |
---|---|---|---|
filter(metric_function (field_expression), ...) [WHERE] condition_expression) | Aggregations for specific subsets of data, computed by applying a filter to the input of a single metric function. | The metric function must be one of the following that returns a single value. { The second argument has the same syntax as a WHERE clause and optionally begins with the WHERE keyword, for example: SELECT filter( | filter( SELECT 100.0 * filter( |
Combining Different Types of Functions
Here are some special cases to consider when combining functions of different types:
- The maximum accuracy of distinctcount() is reduced if it follows a bucketing function. Nested
distinctcount()
queries trade accuracy for memory savings, because the previous function establishes buckets, and distinctcount() creates sets of buckets within each of the previous function's buckets, and Elasticsearch automatically makes that tradeoff when that happens- For example, the first of the two example queries below returns less accurate results than the second:
SELECT transactionName, distinctcount(userId) FROM transactions
SELECT distinctcount(userId) FROM transactions
- For example, the first of the two example queries below returns less accurate results than the second:
- The return value of
stdev(numeric_field_name)
cannot be used to sort or filter results.