AppDynamics switched from Semantic Versioning to Calendar Versioning starting in February 2020 for some agents and March 2020 for the entire product suite.


    Skip to end of metadata
    Go to start of metadata

    Overview

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


    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, 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 

    Top n results is available for SaaS deployments only.


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

    SELECT series(eventTimestamp, '1m'), transactionName, count(*) FROM transaction LIMIT 1, 12

    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.

    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, 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. The example query below 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: 

    You can learn more about funnel functions in the 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.

    Using mathematical and now() functions on datetimes

    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.


    • No labels