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.

Bucketing Functions

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.  

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:
  1. 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(): 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 

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.

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:

concat(inputString1, string inputString2, ... inputStringN)

concat('foo', 'bar')

indexOf(inputString, substring, [occurrence])

length(inputString)

length(' foo bar ')

substring(inputString, startIndex, numChars)

substring('https://example.com/home.htm', 9, indexOf('https://example.com/home.htm','/',3) - 9)

'example.com'

trim(inputString)

trim(' foo bar ')

'foo bar'

Implicit Aggregation Example 

Consider this data:

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: 

See AppDynamics Community. 

Data Type Conversion Functions

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. 

Use mathematical and now() functions on datetimes

You can perform addition and subtraction mathematical functions on datetime and long values, for example:

You can use the now() function to add or subtract the server's current time from a value, for example:

Functions That Modify Other Functions

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.

.confluenceTable, .table-wrap {
    margin: 10px 0 0 0;
    overflow-x: auto;
    width: 100%;
}