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.

FunctionReturnsValid Field Type(s)ADQL Query Examples
avg(numeric_field_name)

Average value for a numeric field.

integer, floating point, datetime

SELECT avg(btdata.estimatedtime) FROM browser_records WHERE appkey = 'your_appkey'

count(field_name)

A count of events.

all

The form count(*) is also valid.

SELECT count(orderID) AS 'Sales' FROM logs WHERE method='GET'


The following query returns the top 10 IP addresses sending HTTP requests to an application.

SELECT ip, count(*) FROM web_session_records WHERE appkey = "yourApp" LIMIT 10

distinctcount(field_name)

A count of the number of unique values recorded for a field.

all

SELECT distinctcount(field_name) FROM logs

max(numeric_field_name)

The maximum recorded value of a numeric field.

integer, floating point, datetime

Specify one field only.

SELECT max(numeric_field_name) FROM transactions

SELECT nodeName, max(eventtimestamp) FROM logs

min(numeric_field_name)

The minimum recorded value of a numeric field.

integer, floating point, datetime

Specify one field only.

SELECT min(numeric_field_name) FROM transactions

stdev(numeric_field_name)

The population standard deviation of a numeric field.

integer, floating pointSELECT 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 sum(numeric_field_name) FROM transactions

percentile(numeric_field_name, percent)

The specified percentile values between 0 and 100.

integer, floating point, datetime

Specify one or more percentiles.

SELECT percentile(responseTime, 50, 75, 90, 95) FROM transactions

stats(numeric_field_name)

Statistics for a field: count, min, max, average, and sum.

integer, floating point, datetime

SELECT stats(numeric_fieldName) FROM transactions WHERE application='yourApp'

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 transactionName, count ( * ) / totalResultCount() FROM transactions WHERE application = 'ECommerce' AND userExperience = 'ERROR'

Bucketing Functions

Bucketing functions operate on a field, and group data into buckets. Often, the data in the buckets are further aggregated by another function. For example, this query creates buckets that group an app's transactions by the hour, and then count how many transactions are in each bucket (hour): 


This page contains macros or features from a plugin which requires a valid license.

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.  

FunctionReturnsValid 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 x or pairs of values (x,y).

See below for explanation of how to define buckets.

This range query:

SELECT range(segments.transactionTime, 0, 10, 20) FROM transactions

Results in the following buckets:

[-Inf, 0], [0, 10], [10, 20], [20, +Inf]


series(field_name, interval_size)

series(field_name, interval_size, lower_extended_bound, upper_extended_bound, [offset],[rangeStyleBucketKeys],[strictEndpoints])


If you specify bounds, then all buckets are returned, empty or not.

If you do not specify bounds, then:

  • If you are using the UI, the UI inserts default bounds.
  • If you are using the API, then only non-empty buckets are returned.

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 offset, rangeStyleBucketKeys, and strictEndpoints.

SELECT series(metrics.`Application Server Time (ms)`,10), count(*) FROM browser_records WHERE appkey='yourAppKey'

SELECT series(eventTimestamp, '1h'), count(*) FROM transactions WHERE application='yourAppName'

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 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:
      1. Immediately after the field (that is, as the second argument): The bucket starts at negative infinity.
      2. After another single value: The bucket starts at the previous argument, inclusive, and extends to x, exclusive.
      3. After a pair of values, and before another single value: The bucket starts at x, inclusive, and extends to the following argument, exclusive.
      4. 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.
      5. 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()offsetrangeStyleBucketKeys, 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.

SELECT 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
    SELECT  series(eventTimestamp, '1m'), range(responseTime, 0, 60, 120), transactionName, ...
  • Include a HAVING clause
    SELECT  series(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

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 string length() and/or the indexOf() 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 fields

  • Pair the output of the substring() function, item by item, with the output of another function

    • This implicit aggregation is the ADQL equivalent of GROUP BY in SQL

    • See the Implicit Aggregation Example:

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

'foobar'

indexOf(inputString, substring, [occurrence])

1-based start index of substring in string.

If substring is not found, returns 0.

If occurrence is supplied as n, returns index of nth occurrence of substring.

If occurrence is supplied as -n, returns index of nth occurrence of substring counting backwards from the end of inputString.

string for inputString, substring


positive or negative int for occurrence

Function:

indexOf('www.wikipedia.org', '.', 1)

Result:

4


Function:

indexOf('www.wikipedia.org', '.', -1)

Result:

14


Function (example of chaining and combining functions):

SELECT substring('www.wikipedia.org', indexOf('www.wikipedia.org', '.', 1)+1, indexOf('www.wikipedia.org', '.', -1) - indexOf('www.wikipedia.org', '.', 1) -1) FROM transactions

Result:

'wikipedia'

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 inputString starting at (1-based) startIndex.

If startIndex is negative, starts that number of characters from the end of the string.

If numChars is supplied, returns substring of that length; if numChars runs past end of string, returns only up to end of string.

If startIndex or numChars is invalid, returns an empty string.

string for inputString


positive or negative int for startIndex

positive int for numChars

 

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 IDresponseTime (ms)
SJC-001500
SJC-002600
SJC-003700
LAX-001200
LAX-002300
LAX-003400

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
CODE

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: 

See AppDynamics Community

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 toDate(field_name, "MM/dd/yyyy") FROM transactions

Returns

2019-01-15T00:00:00.000Z for a field_name with string value "01/15/2019"

toDate(int value)An integer value formatted as a datetime.

SELECT toDate(12341234) FROMtransactions

Returns

Dec 31 1969 19:25:41 GMT-0800

toString(datetime value, [string formatString])A datetime value formatted as a string.

SELECT toString(field_name, "MMM dd, yyyy") FROM transactions

Returns

"Jan 15, 2019" for a field_name with datetime value 2019-01-15T00:00:00.000Z

toString(float/int/bool value)A value formatted as a string.

SELECT toString(123.123) FROMtransactions

Returns

"123.123"

toInt(datetime/float/string/bool value)

An integer value, formatted as follows:

  • datetime- converts to milliseconds,
  • float- truncates
  • string- parses to floats and truncates to an integer
  • bool- converts to 0/1


SELECT toInt(123.4) FROMtransactions

Returns

123

toFloat(int/string value)An integer or string value formatted as a float.

SELECT toFloat(123123) FROMtransactions

Returns

123123.0

round(float value, int decimalPlaces)A value rounded off to decimalPlacesdecimalPlaces must be a non-negative integer.

SELECT round(123.123123, 3) FROMtransactions

Returns

123.123

ifNull(object value, object replacementValue)A value if the value is non-null. If the value is null, returns replacementValue.

SELECT ifNull(field_name, 10)FROM transactions

Returns

10 if the value of field_name is null

Returns

Value of field_name if the value is not null

For data type conversion functions (except 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

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

{count | avg | max | min | sum | distinctcount}

The second argument has the same syntax as a WHERE clause and optionally begins with the WHERE keyword, for example:

SELECT filter(avg(responseTime), WHERE responseTime > 1) FROM transactions WHERE application = "Travel"

filter(sum(numeric_field_name), numeric_field_name > 100)

SELECT 100.0 * filter(count(*), field_name = "value") / count(*) as "%" FROM transactions WHERE application = "yourApp" AND transactionName = "yourValue"


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
  • The return value of stdev(numeric_field_name) cannot be used to sort or filter results.
<!-- style macro --> <!-- style macro -->