How can I create custom queries, segmentations, or aggregations based on user session data?

Dynatrace captures user session data, which includes customer behavior and high level performance data. The User Session Query Language (USQL) provides an easy way to query analytics and performance data captured by Dynatrace and run powerful queries, segmentations, and aggregations on top of this user session data.

This topic covers the basics of using USQL for this purpose, including keywords and functions, syntax, working with the RUM tables for USQL, and using queries in automation.

The User Session Query Language (USQL) is not SQL, as Dynatrace does not store the data in a relational database. The User Session Query Language does use many of the concepts of SQL and the syntax is similar to make it easy to get started with, but it's a Dynatrace specific query language.

The User Session Query language is part of the Dynatrace Environment REST API. In your Dynatrace environment, click Settings > Integration > Dynatrace API, then click the Dynatrace API Explorer text link to see the REST API documentation.

Access your Dynatrace monitoring data using the REST API

You must do the following to access your monitoring data using the REST API:

  1. After creating the access token in Dynatrace API settings, find the token name in the My Dynatrace API tokens list and click Edit. This displays the definition information for the selected token.
  2. Click Copy in the Generated token field to copy the token to your clipboard.
  3. Click the Dynatrace API explorer text link at the top of the Dynatrace API page to access the API documentation. In the Select a spec field, make sure Environment API* is selected.
  4. Click Authorize and in the Available authorizations window, paste the copied token into the Value field of the desired UserSession authorizations, which include UserSessionQueryToken, UserSessionAnonymizationToken, and DataExportToken. Then click the corresponding Authorize buttons and close the Available authorizations window.

Use timeframes with USQL

User session data should always be accessed with a timeframe. This is similar to using the timeframe selector in Dynatrace. It can be costly to access large timeframes because of the high number of potential single matches to queries.

Because of this, USQL is designed to always receive the timeframe as separate input to the framework outside of the actual query. Therefore the timeframe is usually not part of the query itself.

You can however use the time fields like starttime and endtime to select a timeframe. You can also use these fields in functions, for example to find out what time during the day most user sessions start, as in HOUR(starttime).

USQL keywords and functions

The following keywords are defined as part of the query language:

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

The following functions are defined as part of the query language:

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.

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

USQL keywords

This section describes and shows examples of 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 and useraction.

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.

ORDER BY

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

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

USQL Functions

This section describes and shows examples for the more 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 how many rows 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.

Advanced USQL syntax constructs

Query IP ranges in USQL

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

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

SELECT * FROM table

JSON export

Available user session data tables and fields

For user session data, the usersession and useraction tables are available. Secondary tables for usersession/useraction includes a description of how data in one of those two 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.

Limitations

  • With multiple aggregation functions, the multiplied number of possible results is limited.
  • The number of results is currently limited to a maximum of 5000.
  • The number of potential maximum bucketed results is limited to a maximum of 100,000. The default is 10,000.
  • Joins are not 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.
  • The User Session Query Language can't compare two different fields. For example WHERE field1 = field2 doesn't work.
  • WHERE conditions only work on fields, so neither WHERE true nor WHERE COUNT(*) > 3 is supported.
  • The User Session Query Language works only for completed user sessions. Active user sessions aren't taken into account.