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.
Be aware that with Dynatrace version 1.204, we’ve introduced new error counts in USQL and Session export for every user action, which are fully consistent across Dynatrace. In turn, we'll deprecate the following error counts with Dynatrace version 1.217:
useraction.errorCount
— Only includes JavaScript errors.useraction.httpRequestsWithErrors
— Only considers server-side errors.useraction.failedXHRRequests
— Only considers failed XHR calls made in your end user's browser.useraction.failedImages
— Will be included with the newrequestErrorCount
. Read more about how to best replace existing error counts with the new ones.
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
, LIKE
, FILTER
The following functions have been defined to access user session data:
SUM
, MAX
, MIN
, AVG
, MEDIAN
, COUNT
, YEAR
, MONTH
, DAY
, HOUR
, MINUTE
, DATETIME
, TOP
, PERCENTILE
, KEYS
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 <columns>
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
FUNNEL
Allows you to use a predefined funnel format for a query. Can be used to chart the flow of specific user actions. Can also be combined with custom session properties and other conditions.
It changes the syntax of any query to the following:
SELECT FUNNEL (<condition> AS <alias>, <condition>, ...) FROM <table> WHERE <condition>
FROM <table>
You can only specify one table. Tables for user session data are usersession
, useraction
, and userevent
.
WHERE <condition>
You can combine multiple conditions using Boolean logic and parentheses within the WHERE
clause, such as WHERE (city = 'Barcelona' AND country = 'Spain')
to include only cities named Barcelona that are in Spain.
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 <grouping>
Whenever fields are aggregated, you must specify corresponding GROUP BY
keywords to indicate how the aggregation is to be performed.
grouping: <column>, ...
LIMIT <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 <ordering>
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>, ...
LIKE
Allows you to compare data with an expression by using wildcard characters to match the specified pattern. You can use the following characters:
%
or*
: Matches any string of 0 or more characters?
: Matches any single character
String values are case-sensitive. For example, SELECT city FROM usersession WHERE userId LIKE "*dynatrace*"
matches me@dynatrace.com
but not me@dynaTrace.com
. To avoid this, use the ?
wildcard character as in this example: SELECT city FROM usersession WHERE userId LIKE "*dyna?race*"
LIKE
conditions with non-trailing wildcards are rejectedUSQL queries that have 11 or more LIKE
conditions with *
or %
at the beginning or inside the search pattern (but not at the end) are rejected from execution.
FILTER
Allows you to filter for functions that have numeric values, thereby displaying only specific results from aggregations.
The WHERE
and FILTER
functions aren't interchangeable. While you can use the WHERE
clause only on absolute values, the FILTER
function works on aggregated values as well.
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.
Dynatrace rejects and doesn't execute the queries with COUNT(DISTINCT <field>)
that might consume a lot of memory. This happens for all extremely high-cardinality fields, for example, for the dateTime
fields like usersession.startTime
, usersession.endTime
, or useraction.networkTime
.
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]])
Formats 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
: yearM
: monthd
: day of monthH
: hour (0-23)h
: hour (1-12)m
: minutes
: secondE
: 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.
CONDITION(function, condition)
Allows you to combine multiple functions with various conditions.
The allowed functions within the format string are as follows:
MIN()
MAX()
AVG()
SUM()
PERCENTILE()
MEDIAN()
COUNT()
You can combine multiple conditions using Boolean logic and parentheses with the CONDITION
function, such as CONDITION(COUNT(*), WHERE city = 'Barcelona' AND country = 'Spain')
to include only cities named Barcelona that are in Spain.
CONDITION(function, condition)
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> | ...
You can also use the FILTER
clause filter for functions that have numeric values, thereby displaying only specific results from aggregations.
SELECT CONDITION(COUNT(usersessionId), WHERE userActionCount > 2 AND useraction.name = "search.jsp") FILTER > 1000, city FROM usersession GROUP BY city
PERCENTILE
Represents a value below which there's a percentage of data points lower in value. Useful in detecting the speed of your application for customers who receive the slowest response time.
KEYS(customProperty)
Returns keys of user action or user session properties according to the property data type defined in the argument.
Check the table below to understand whether keys of user action properties or keys of user session properties are returned.
KEYS(customProperty) | Table | Action properties | Session properties |
---|---|---|---|
KEYS(<dataType>Properties) | useraction | ✔️ | |
KEYS(<dataType>Properties) | usersession | ✔️ | |
KEYS(useraction.<dataType>Properties) | useraction | ✔️ | |
KEYS(useraction.<dataType>Properties) | usersession | ✔️ | |
KEYS(usersession.<dataType>Properties) | useraction | ✔️ | |
KEYS(usersession.<dataType>Properties) | usersession | ✔️ |
The <dataType>
part of the function can take the following values:
string
long
double
date
For fetching distinct keys of action or session properties, use DISTINCT KEYS(customProperty)
.
Mathematical operations
The following operations are supported as part of queries:
- operations on numbers
- operations on numeric and dateTime fields
- operations on certain functions such as
YEAR
,MONTH
,DAY
,HOUR
,MINUTE
- operations on numeric values and display them in different uses of measurement
Syntax
Number/NumericField/DateTimeField/Function OPERATOR Number/NumericField/DateTimeField/Function
Function: YEAR
, MONTH
, DAY
, HOUR
or MINUTE
Operator: +
, -
, *
, /
, %
, or MOD
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:
Format | Description | Example |
---|---|---|
— | Unix timestamp as a number in milliseconds | 1514152800000 |
yyyy-MM-dd'T'HH:mm:ssZ | ISO datetime with the time zone | 2017-12-24T21:00:00+01:00 |
yyyy-MM-dd HH:mm:ss | Date with optional time | 2017-12-24 21:00 |
yyyy/MM/dd HH:mm:ss | Date with optional time | 2017/12/24 21 |
MM/dd/yyyy HH:mm:ss | Date with optional time | 12/24/2017 |
dd.MM.yyyy HH:mm:ss | Date with optional time | 24.12.2017 21:00:00 |
For the formats where time is optional, the following time formats are supported:
Format | Example |
---|---|
HH:mm:ss | 08:20:59 |
H:mm:ss | 8:20:59 |
HH:mm | 08:20 |
H:mm | 8:20 |
HH | 08 |
H | 8 |
If the time is missing, the default 00:00:00
is assumed.
Sometimes, queries with datetime values might yield incorrect results due to the Daylight Saving Time conversion. If the date in the query is before the Daylight Saving Time end date (for example, before November 7, 2021 02:00), try using the ISO datetime with a time offset, for example, 2021-10-05T17:30:00+03:00
.
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.
Query timeframe selector
You can use the following keywords to select the start time and end time as defined in the timeframe selector.
TIME_FRAME_START
TIME_FRAME_END
Query relative timeframe
You can select a timeframe relative to the time when the query was run. The current time is expressed with the $NOW
variable.
$NOW [+/-] DURATION("[number]TIME_UNIT")
The following time units are supported to express the duration:
y
: yearq
: quarterM
: monthd
: dayw
: weekh
: hourm
: minutes
: second
The timeframe selected in the Dynatrace web UI or Dynatrace API still applies to the results, even if timestamp-based filtering is used as part of your query.
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.
Funnel charting
Funnel charting allows you to track steps through your digital service and investigate the areas of struggle faced by your users. In conjunction with Session Replay, this functionality allows you to see at which point your user is struggling in your application.
You can also filter for a specific segment. An example of this is using Session properties to extract the list of high priority customers.
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 User sessions API - User session structure.
You can also check the UserSession object in the API Explorer.
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.
Convert queries into USQL custom metrics
You can convert some queries into USQL custom metrics for your web, mobile, and custom applications.
USQL custom metrics are available as user session custom metrics (USCMs) and user action custom metrics (UACMs). User action custom metrics are supported since Dynatrace version 1.260.
- In the Dynatrace menu, go to Query user sessions.
- Enter the query, and then select Run query.
For a list of supported fields, see the detailed guides for your web, mobile, and custom applications. - Select Create custom metric.
- Enter the metric name, and then review the proposed settings.
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 howTOP()
is applied whenDISTINCT
orGROUP BY
is used. If noTOP()
is specified, 10,000 possible results are spread evenly across the specified columns. These default values can be overwritten by specifying aTOP()
for each column. The multipliedTOP()-
values cannot exceed 100,000 results. -
Joins aren't allowed.
-
Only one table is allowed per
SELECT
. -
Searches for string-values with regular expressions aren't supported.
-
Two different fields can't be compared. For example
WHERE field1 = field2
doesn't work. -
WHERE
conditions only work on fields, so neitherWHERE true
norWHERE COUNT(*) > 3
is supported. -
Only closed user sessions can be queried. Live user sessions aren't taken into account.
-
Ordering is partially supported.
For example, ordering by mathematical operation is not yet supported:
SELECT endTime - startTime AS duration FROM usersession ORDER BY duration
-
Functions aren't allowed in the
GROUP BY
clause. Therefore, if you want to group by month, specify an alias. -
FUNNEL
cannot be used with theSELECT *
functions, keywords such asJSON
, and theGROUP BY
,ORDER
andLIMIT
statements. -
For mathematical operations, support for
GROUP BY
,ORDER BY
, and other operations on functions is not available. -
A maximum of 10 conditions can be applied to
FUNNEL
.