On this page:

ADQL Reference:

Your Rating:
Results:
PatheticBadOKGoodOutstanding!
12 rates

Overview

Functions serve as arguments in the SELECT clause. Function types in ADQL include:

  • Metric functions 
  • Bucketing functions
  • String 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): 

 

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

 

To get 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, for example:

  • '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[, field_name1, ... field_name4], interval_size)

series(field_name[, field_name2, ... field_name4], 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

No more than five field_names are allowed.

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

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.

Prior to AppDynamics 4.5.2, fixing incorrectly-collected data required 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 kind of implicit aggregation is the ADQL equivalent of GROUP BY in SQL

    • see the Implicit Aggregation Example below

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 the following 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, 2)avg(responseTime) FROM transactions

The results are:

SJC, 600

LAX, 300

Notice how ADQL uses substring() the to govern the groupings to which the second function, avg(), is applied.

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.

 

  • No labels