Custom queries, segmentation, and aggregation of session data

Dynatrace captures detailed user session data each time a user interacts with your monitored application. This data includes all user actions and high level performance data. Using either the Dynatrace API or Dynatrace User Sessions Query Language (USQL), you can easily run powerful queries, segmentations, and aggregations on this captured data. To assist you, this topic provides detail about keywords and functions, syntax, working with Real User Monitoring tables, automated queries, and more.

User Sessions Query Language isn't SQL and Dynatrace doesn't store user session data in a relational database. User Sessions Query Language is a Dynatrace-specific query language, though it does rely on some SQL concepts and the syntax is similar, which makes it easy to get started.

Select your preferred approach:

Keywords and functions

The following keywords have been defined to access user session data:

AND, AS, ASC, BETWEEN, BY, DESC, DISTINCT, FALSE, FROM, GROUP, IN, IS, JSON, LIMIT, NOT, NULL, OR, ORDER, SELECT, STARTSWITH, TRUE, WHERE

The following functions have been defined to access user session data:

SUM, MAX, MIN, AVG, MEDIAN, COUNT, YEAR, MONTH, DAY, HOUR, MINUTE, DATETIME, TOP

Keywords, functions, and column names are case-insensitive. String-matches in WHERE conditions are case-sensitive.

Syntax

A typical query is built from the following keywords:

SELECT <columns> FROM <table> WHERE <condition> GROUP BY <grouping> ORDER BY <ordering>

However, the only mandatory elements are SELECT <columns> and FROM <table>.

Frequently used keywords

SELECT

Selects one or more columns from the specified data table, or performs aggregation functions from the set of supported functions.

columns: [DISTINCT] <column>, <column>, ... | function(<parameter>) | <column> AS <alias> | JSON

FROM

You can only specify one table. Tables for user session data are usersession, useraction, and userevent.

WHERE

You can combine multiple conditions using Boolean logic and parentheses within the WHERE clause, such as WHERE (city='Barcelona' AND country='Spain') to exclude cities named Barcelona that are not in Spain. For example:

condition: (condition AND condition) | (condition OR condition) | field IN(...) | field IS <value> | field IS NULL | field = <value> | field > <value> | field < <value> | field <> <value> | field IS NOT <value> | field BETWEEN <value> AND <value> | ...

However, only the right-hand side of conditions can contain a value, so you can't compare between two fields.

GROUP BY

Whenever fields are aggregated, you must specify corresponding GROUP BY keywords to indicate how the aggregation is to be performed.

grouping: <column>, ...

LIMIT

Allows you to limit the number of returned results. For example, you can select only the top 10 results when it is combined with ordering.

The framework always applies an upper limit to prevent system overload. If LIMIT is not used, 50 results are returned by default.

LIMIT can also be used to increase the number of results in cases where the LIMIT clause is missing, because then a default limit is applied.

ORDER BY

Allows you to order the results by columns, in either ascending or descending order. The order is ascending if not specified.

The ordering is done by frequency. For example, the top 5 returned cities are the most frequently occurring ones. By specifying a field in the order by clause, you can add a sort-by value for strings, dates, and numbers.

Ordering by enums or by function values such as AVG and SUM orders the returned results but you may not get the top items. For example, if you request the top 5 results by AVG(duration), requesting only 10 may add results even at the top.

ordering: <column> ASC | <column> DESC | <column>, ...

Frequently used functions

MIN(field)

Queries the minimum value of a numeric or date field.

MAX(field)

Queries the maximum value of a numeric or date field.

AVG(field)

Queries the average value of a numeric or date field. May be NaN if the field is always null.

MEDIAN(field)

Queries the median value of a numeric or date field.

SUM(field)

Computes the sum of a numerical field.

COUNT(field), COUNT(*), COUNT(DISTINCT field)

Counts the number of rows that match.

COUNT(*): Counts the number of matching items.

COUNT(<field>): Counts the number of matching items where \<field> is not null.

COUNT(DISTINCT <field>): Counts the number of different values for \<field> within the selected items.

TOP(field, n)

Returns the top \<n> results from a field. The default is 1 (the top value) if n is not specified.

YEAR(datefield), MONTH(datefield), DAY(datefield), HOUR(datefield), MINUTE(datefield)

Returns the given element extracted from a date field.

YEAR: The 4-digit year. MONTH: The month number between 1 and 12 DAY: The day of the month between 1 and 31. HOUR: The hour value between 0 and 23. MINUTE: The minute value between 0 and 59.

DATETIME(datefield [, format [, interval]])

Format the selected date field with the given format string. The default format is yyyy-MM-dd HH:mm.

The allowed letters within the format string are:

  • y: year
  • M: month
  • d: day of month
  • H: hour (0-23)
  • h: hour (1-12)
  • m: minute
  • s: second
  • E: day of week (Mon-Sun)

The year|month|week intervals are for a single interval. For d (days), h (hours), m (minutes) or s (seconds), you can use a number followed by the letter for the format string, such as 5m. For example, SELECT DISTINCT DATETIME(starttime, 'HH:mm',5m), count(*) FROM usersession counts sessions in five minute time blocks.

Conditions

All conditions must start with an identifier, such as a field name, and must be compared against a value. Two fields cannot be compared against each other. Note: Quoted text is always case-sensitive.

Basic operators

The basic operators for comparison are =, !=, <>, <, >, <=, >=, IS, and IS NOT.

To check if the value of a field is present, compare the field against NULL.

Ranges

Ranges are handled by keywords such as BETWEEN, <lowerLimit>, and <upperLimit>.

Sets

The IN keyword can be used shorten "WHERE" <field> = val1 OR <field> = val2 OR <field> = val3.

String conditions

The "STARTSWITH" string condition checks whether a string or an enum field starts with the specified text.

Datetime values

When conditions are run on a datetime field, the following value formats are supported:

  • Unix time stamp as a number in milliseconds. For example: 1514152800000
  • "yyyy-MM-dd'T'HH:mm:ssZ": ISO datetime with the timezone. For example: 2017-12-24T21:00:00+01:00
  • "yyyy-MM-dd HH:mm:ss": Date with optional time. For example: "2017/12/24 21:00"
  • "yyyy/MM/dd HH:mm:ss": Date with optional time. For example: "2017/12/24 21"
  • "MM/dd/yyyy HH:mm:ss": Date with optional time. For example: "12/24/2017"
  • "dd.MM.yyyy HH:mm:ss": Date with optional time. For example: "24.12.2017 21:00:00"

For the formats where time is optional, the following time formats are supported:

  • HH:mm:ss (For example: "08:20:59")
  • H:mm:ss (For example: "8:20:59")
  • HH:mm (For example: "08:20")
  • H:mm (For example: "8:20")
  • HH (For example: "08")
  • H (For example: "8")

Note: If the time is missing, the default 00:00:00 is assumed.

Advanced syntax constructs

Query IP ranges

The IP field can be queried for ranges of addresses. Both BETWEEN ip > <lower ipaddress range> AND ip < <upper ipaddress range> or BETWEEN <lower ipaddress range> AND <lower ipaddress range> work.

Secondary tables for usersession, useraction, and userevent

When using SELECT with usersession, useraction, or userevent, columns from the other table can be accessed and included in the results by prefixing column names with the table name.

Applied conditions differ in meaning depending on the table. For example, consider that you want to list all usersessions that contain useractions a and b:

SELECT * FROM usersession WHERE useraction.name = "a" and useraction.name = "b"

This means, the session must contain a useraction "a" and a useraction "b". Running the same query on the useraction table will return an empty result as the same useraction cannot have two different values for the same name.

If you want to select usersession data for a specific useraction that matches several criteria, run the following query:

SELECT usersession.*, * FROM useraction WHERE useraction.name = "a" and useraction.duration > 1000

In this case, each useraction in the result satisfies both conditions.

Filters for primary tables

Filters can be applied only to primary tables. Consider the following example:

SELECT * FROM table

JSON export

Escaping strings

String literals can be put within single or double quotes. However, if you want to use the same quotation mark inside the string, simply double it.

Available user session data tables and fields

For user session data, the usersession, useraction, and userevent tables are available. Secondary tables for usersession, useraction, and userevent includes a description of how data in one of those tables is available in the other.

The fields are described in the UserSession document structure in the Dynatrace Environment API. In your Dynatrace environment, click Settings > Integration > Dynatrace API, then click the Dynatrace API Explorer text link to see the Rest API documentation. Click Models > UserSession on that page to see fields and descriptions.

Run USQL queries for custom reports

A REST interface allows you to get results for your custom queries. All you need is to create a unique API token with the User session query language privilege. The ability to query user session data this way is useful in automated testing, data verification, and other automated functions. It includes the following endpoints:

/table: Returns the data as a flat table, even when grouping by various items and performing hierarchical aggregations against the user session data.

/tree: Returns the data as a full hierarchical tree based on the input.

The following query-parameters are available:

query: Needs to be encoded when put into a URL, for example, %20 instead of spaces. startTimestamp/endTimestamp: Allows you to define points in time, passed as the number of milliseconds since the Unix epoch. If not specified, this defaults to the last two hours.

Learn more about user sessions API.

Limitations

  • Dynatrace stores and retains Real User Monitoring (user actions and user sessions) for a limited period of time. See Data retention periods for details.
  • The default result set is 50 but the number of results can be increased to a maximum of 5000 by using the LIMIT keyword.
  • The number of potential maximum bucketed results is limited to a maximum of 100,000. The default is 10,000.
    This affects how TOP() is applied when DISTINCT or GROUP BY is used. If no TOP() is specified, 10,000 possible results are spread evenly across the specified columns. These default values can be overwritten by specifying a TOP() for each column. The multiplied TOP()- values cannot exceed 100,000 results.
  • Joins aren't allowed.
  • Only one table is allowed per SELECT.
  • Searches for string-values are currently always "equals". Matchers like regular expressions or LIKE in SQL are currently not supported.
  • Two different fields can't be compared. For example WHERE field1 = field2 doesn't work.
  • WHERE conditions only work on fields, so neither WHERE true nor WHERE COUNT(*) > 3 is supported.
  • Only closed user sessions can be queried. Live user sessions aren't taken into account.
  • Ordering is partially supported.
  • Functions aren't allowed in the GROUP BY clause. Therefore, if you want to group by month, specify an alias.