Custom queries, segmentation, and aggregation of session data

1. In the Dynatrace menu, go to Query user sessions.
2. Enter the query, and select Run query.

Timeframe selector with USQL

With the timeframe selector, user session data can be filtered based on a selected analysis timeframe. Select a new timeframe.

Timeframe selector controls

The global timeframe selector serves as a time filter that, in most cases, enables you to select a specific analysis timeframe that persists across all product pages and views as you navigate through your analysis.

• The Presets tab lists all standard timeframes available. Select one to change your timeframe to that preset.

• The Custom tab displays a calendar. Click a start day, click an end day, and then click Apply to select that range of days as your timeframe.

  • Selected calendar intervals are set to end on start of the next day (with the time set to 00:00). For example, if you select September 3 to September 4 on the calendar, the timeframe starts on September 3 at time 00:00 and ends on September 5 at time 00:00, so you never miss the last minute of the time range. You can edit these displayed times.

• The Recent tab displays recently used timeframes. Select one to revert to that timeframe.

• The < and > controls shift the timerange forward or backward in time. The increment is the length of the original timerange. For example, if the current timerange is Last 2 hours (the two-hour range ending now), click < to shift the timerange two hours back, to -4h to -2h (the two-hour range ending two hours ago).

• Hover over the timeframe to see the start time, duration, and end time.

  

Timeframe selector expressions

If you select the current timeframe in the menu bar, an editable timeframe expression is displayed.

• Reading from left to right, a timeframe expression has a start time, a to operator, and an end time.
• If there is no explicit end time, the to and now are implied. For example, -2h is the same -2h to now.
• Supported units: s, m, h, d, w, M, q, y (you can also use whole words such as minutes and quarter)

Autocomplete with USQL

Auto-completion is available for query statements based on the currently entered text. Auto-complete is context-sensitive and based on the current cursor context. It intelligently anticipates what text you may want to type next.

Autocomplete information returned from the API contains a list of suggestions, with the most likely values sorted to the top of the list, along with the necessary adjustment to the query text and the resulting cursor position after the adjustment. This allows you to place the cursor at a more useful position once text has been added, such as inside brackets when you select a function.

The fields list only contains valid fields or functions for the selected table, and its context-sensitivity shows only numeric fields when the cursor is placed inside a function-call that only works on numerical data, such as SUM(), or AVG().

Field-values are only suggested for enumeration based fields. No actual query to Elasticsearch is done for auto-completion.

You can execute API calls using your preferred client. Find the related API documentation in User sessions API. Alternatively, you can use the API Explorer. To access it, select Environment API v1 from the User menu in the upper-right corner.

To execute these calls, you need the User sessions (DTAQLAccess) permission assigned to your API token. To learn how to obtain and use your token, see Dynatrace API - Tokens and authentication.

Timeframes in Environment API

User session data should always be accessed with a timeframe. It can be costly to access large timeframes because of the high number of potential single matches to queries.

The timeframe is usually not part of the query itself but is sent in separate parameters of the API call. You can find details in the API documentation.

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

sqlcopydownload
SELECT browserType, userId, city, AVG(userActionCount) AS "Average user action count", AVG(duration) AS "Average duration", count(*) AS "Sessions", SUM(totalErrorCount) AS "Errors" FROM usersession WHERE ip between '52.179.11.1' and '52.179.11.255' GROUP BY browserType, userId, city

sqlcopydownload
SELECT country, city, browserfamily FROM usersession
SELECT DISTINCT country, city, useractioncount FROM usersession
SELECT country, city, avg(duration) AS average FROM usersession GROUP BY country, city

Instead of running the following three queries:

sqlcopydownload
SELECT COUNT(*) FROM usersession where useraction.name = "AppStart"
SELECT COUNT(*) FROM usersession where useraction.name = "AppStart" AND useraction.name = "searchJourney"
SELECT COUNT(*) FROM usersession where useraction.name = "AppStart" AND useraction.name = "searchJourney"  AND useraction.name = "bookJourney"

The following single query returns the same result:

sqlcopydownload
SELECT FUNNEL (useraction.name = "AppStart", useraction.name = "searchJourney", useraction.name = "bookJourney")
FROM usersession

To list the number of users that successfully booked the journey:

sqlcopydownload
SELECT FUNNEL (useraction.name="login", useraction.name = "searchJourney", useraction.name = "bookJourney")
FROM usersession

sqlcopydownload
SELECT country, city, browserfamily FROM usersession
SELECT name, starttime, endtime, duration FROM useraction ORDER BY duration DESC

sqlcopydownload
SELECT country, city, browserfamily FROM usersession WHERE country = 'Albania' AND screenWidth > 1000
SELECT top(country, 20), top(city, 20), TOP(duration, 10), avg(duration) AS average
FROM usersession **WHERE** duration BETWEEN 1000 AND 2000 GROUP BY top(country, 20), top(city, 20), TOP (duration, 10)

sqlcopydownload
SELECT city, count(*) FROM usersession GROUP BY city
SELECT MONTH(starttime) as month, count (*) FROM usersession
GROUP BY month

sqlcopydownload
SELECT city, starttime FROM usersession ORDER BY starttime DESC LIMIT 10

sqlcopydownload
SELECT useraction.name, starttime FROM usersession ORDER BY starttime DESC

You can achieve ordering of counts by adding the DISTINCT keyword.

sqlcopydownload
SELECT DISTINCT city, COUNT(*) FROM usersession ORDER BY COUNT(*) DESC

You can achieve ordering of functions in the select list by stating the column name only or the alias defined in the SELECT.

sqlcopydownload
SELECT avg(duration) AS average, count(*) as number, day(startTime) as startDay
FROM usersession where duration < 2000
GROUP BY startTime
ORDER BY average

or

sqlcopydownload
SELECT avg(duration) AS average, count(*) as number, day(startTime) as startDay
FROM usersession where duration < 2000
GROUP BY startTime
ORDER BY number DESC, average ASC

To escape a wildcard character, add a backslash \ before it. For example, \%, \*, and \? are treated as standard string literals %, *, and ?.

To escape a backslash \ itself, add another backslash \ before it. The resulting \\ entry is treated as a single backslash \.

If you add two backslashes \\ before a wildcard character (resulting in entries like \\%, \\*, or \\?), such an entry is treated as one escaped backslash \ and one wildcard character. For example, the \\* entry matches \abc, \123ABC, or \.

Summary of how to escape wildcard characters:

Examples of how to escape wildcard characters:

sqlcopydownload
SELECT userId FROM usersession WHERE userId LIKE "AU\%40KWM"

sqlcopydownload
SELECT userId FROM usersession WHERE userId like "AU\*40KWM"

sqlcopydownload
SELECT userId FROM usersession WHERE userId LIKE "AU\?40KWM"

sqlcopydownload
SELECT userId FROM usersession WHERE userId LIKE "AU\\%40KWM"

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

Usually, we simply count the number of LIKE conditions used in the query. For example, there are five LIKE conditions in the query below—we count each occurrence of LIKE in the WHERE clause and the CONDITION function.

sqlcopydownload
SELECT CONDITION(COUNT(userSessionId), WHERE useraction.name LIKE '*search.html'),
       CONDITION(COUNT(userSessionId), WHERE useraction.name LIKE '*booking-payment1.html')
FROM usersession
WHERE city LIKE "%York"
    OR city LIKE "S*Francisco"
    AND city LIKE "L*inz"

However, when the FUNNEL function is used, the calculation becomes more complicated. For this function, we internally transform one query into several queries. After that transformation, we count the number of LIKE conditions in those internally transformed queries.

For example, the following query:

sqlcopydownload
SELECT FUNNEL (useraction.name LIKE "*start", useraction.name LIKE "Jou%rney", useraction.name LIKE "bookJourn*ey") FROM usersession

is changed to the following three queries:

sqlcopydownload
SELECT COUNT(*) FROM usersession where useraction.name LIKE "*start"
SELECT COUNT(*) FROM usersession where useraction.name LIKE "*start" AND useraction.name LIKE "Jou%rney"
SELECT COUNT(*) FROM usersession where useraction.name LIKE "*start" AND useraction.name LIKE "Jou%rney" AND useraction.name LIKE "bookJourn*ey"

This means there are actually six LIKE conditions in the FUNNEL query above.

sqlcopydownload
SELECT useraction.application,
AVG(usersession.doubleProperties.bookings)
FILTER > 1500
FROM usersession
WHERE usersession.doubleProperties.bookings IS NOT NULL
GROUP BY useraction.application

sqlcopydownload
SELECT MIN(duration), MAX(duration), AVG(duration), MEDIAN(duration)
FROM usersession

sqlcopydownload
SELECT MIN(duration), MAX(duration), AVG(duration), MEDIAN(duration)
FROM usersession

sqlcopydownload
SELECT MIN(duration), MAX(duration), AVG(duration), MEDIAN(duration)
FROM usersession

sqlcopydownload
SELECT MIN(duration), MAX(duration), AVG(duration), MEDIAN(duration)
FROM usersession

sqlcopydownload
SELECT TOP(name, 20), SUM(duration) FROM useraction
GROUP BY name

sqlcopydownload
SELECT country, COUNT(*), COUNT(city), COUNT(DISTINCT city)
FROM usersession
GROUP BY country

sqlcopydownload
SELECT TOP(name, 20), SUM(duration)
FROM useraction
GROUP BY name

If TOP(<field>, n) is selected and the results are grouped, but <field> is not part of the grouping, the top-n elements return as a list within a single field.

sqlcopydownload
SELECT TOP(country, 20), TOP(city, 3), COUNT(*)
FROM usersession
GROUP BY country

sqlcopydownload
SELECT starttime,
DATETIME(starttime), YEAR(starttime), MONTH(starttime), DAY(starttime), HOUR(starttime), MINUTE(starttime)
FROM usersession
ORDER BY starttime
DESC

sqlcopydownload
SELECT DATETIME(starttime, 'yyyy-MM') FROM usersession
SELECT DISTINCT DATETIME(starttime, 'HH:mm', '5m'), count(*) FROM usersession

sqlcopydownload
SELECT CONDITION(COUNT(usersessionId), WHERE userActionCount > 2 AND useraction.name = "search.jsp") FROM usersession

SELECT CONDITION(SUM(usersession.duration), WHERE name = "index.jsp") AS c1, CONDITION(SUM(usersession.duration), WHERE name = "search.jsp") AS c2, CONDITION(SUM(usersession.duration), WHERE name IS NOT "index.jsp" AND name IS NOT "search.jsp") AS c3 FROM useraction WHERE (duration > 1000 OR usersession.userActionCount > 4)

SELECT CONDITION(SUM(usersession.duration), WHERE name = "index.jsp") AS c1 FROM useraction WHERE (duration > 1000 OR usersession.userActionCount > 4) ORDER BY c1

SELECT DATETIME(CONDITION(MIN(startTime ), WHERE useraction.application = "RUM Default Application" ), "yyyy-MM-dd" ) FROM usersession

sqlcopydownload
SELECT name, usersession.country, usersession.browserFamily,
AVG(duration),
MEDIAN(duration),
PERCENTILE(duration, 99)
FROM useraction
WHERE useraction.name = "easytravel/rest/login"
GROUP BY usersession.country, usersession.browserFamily, name
ORDER BY usersession.continent

sqlcopydownload
SELECT KEYS(stringProperties) FROM useraction WHERE application = "easyTravel demo application"
SELECT KEYS(useraction.longProperties) FROM usersession WHERE applicationType="WEB_APPLICATION" ORDER BY keys(useraction.longProperties)
SELECT KEYS(usersession.stringProperties) FROM useraction WHERE usersession.city ="Berlin"

sqlcopydownload
SELECT DISTINCT KEYS(stringProperties) FROM useraction WHERE useraction.application = "easyTravel demo application" ORDER BY keys(stringProperties)
SELECT DISTINCT KEYS(doubleProperties) FROM usersession

sqlcopydownload
SELECT 7 + 80 * 100, duration + startTime, MONTH(startTime) - 1
FROM usersession

sqlcopydownload
SELECT userId FROM usersession WHERE userActionCount > 3

sqlcopydownload
SELECT DISTINCT ip FROM usersession
WHERE ip BETWEEN "192.168.0.0" AND "192.168.255.255"

sqlcopydownload
SELECT startTime FROM useraction WHERE NOT startTime BETWEEN $NOW - DURATION("2h") AND $NOW

sqlcopydownload
SELECT ip, browserType, userId, city
FROM usersession
WHERE NOT ip BETWEEN '52.179.11.1' AND '52.179.11.255'

sqlcopydownload
SELECT userId FROM usersession WHERE city IN ("NEW YORK", "San Francisco")

sqlcopydownload
SELECT city FROM usersession WHERE userId STARTSWITH "dynatrace"

sqlcopydownload
SELECT starttime FROM usersession WHERE starttime > "8.8.2018 8:00"

sqlcopydownload
SELECT * FROM usersession WHERE ip > '211.44.94.0' AND ip < '212.113.5.0'
SELECT * FROM usersession WHERE ip BETWEEN '211.44.94.0' AND '212.113.5.0'

sqlcopydownload
SELECT * FROM usersession WHERE startTime >= $TIME_FRAME_START AND endTime < $TIME_FRAME_END

sqlcopydownload
SELECT * FROM usersession WHERE startTime >= $NOW - DURATION("1q") AND endTime <= $NOW
SELECT * FROM useraction WHERE startTime BETWEEN $NOW - DURATION("2h") AND $NOW
SELECT * FROM useraction WHERE usersession.startTime >= $TIME_FRAME_START - DURATION("2h") AND $NOW - DURATION("1h")

Selecting a logical view of the usersession or the useractions table. Multiple values concatenate in the resulting column when adding information from useraction into a query on usersession.

sqlcopydownload
SELECT city, useraction.name FROM usersession
SELECT usersession.city, name FROM useraction

Listing all usersessions that contain a userevent or a useraction from the same application.

sqlcopydownload
SELECT * FROM usersession
WHERE userevent.application = "a" OR useraction.application="a"

sqlcopydownload
SELECT useraction.name FROM usersession WHERE useraction.name=”abc”

This query returns a list of all user actions from only those user sessions that contain user actions called abc. This happens because the query is applied on all user sessions that match your filter (which is the primary table) instead of the user actions (which form the secondary table).

sqlcopydownload
SELECT name FROM useraction WHERE name=”abc”

This query retrieves the list of all user actions called “abc” from all user sessions (which form the primary table).

sqlcopydownload
SELECT * FROM usersession
SELECT useraction.* FROM usersession
SELECT city, useraction.* FROM usersession
SELECT *, useraction.* FROM usersession

sqlcopydownload
SELECT usersessionId, browserFamily, useraction.name, useraction.duration, JSON
FROM usersession LIMIT 5

sqlcopydownload
SELECT * FROM usersession WHERE userId = "some 'custom' name for ""my user"""
SELECT * FROM usersession WHERE userId = 'some ''custom'' name for "my user"'

sqlcopydownload
SELECT FUNNEL(
useraction.name = "AppStart (easyTravel)" AS "Open easytravel",
useraction.name = "searchJourney" AS "Search journey",
useraction.name = "bookJourney" AS "Book journey",
useraction.matchingConversionGoals = "Payment" OR useraction.matchingConversionGoals = "booking-finished" AS "Booked")
FROM usersession

sqlcopydownload
SELECT FUNNEL(
useraction.name = "AppStart (easyTravel)" AS "Open easytravel",
useraction.name = "searchJourney" AS "Search journey",
useraction.name = "bookJourney" AS "Book journey",
useraction.matchingConversionGoals = "Payment" OR useraction.matchingConversionGoals = "booking-finished" AS "Booked")
FROM usersession
WHERE stringProperties.memberstatus="GOLD"

This code:

sqlcopydownload
curl --location --insecure -H "Content-Type: application/json" -H "Authorization: Api-Token _token_" \
-XGET "https://{your-environment-id}.live.dynatrace.com/api/v1/userSessionQueryLanguage/table?query=select%20city,count(*)%20from%20usersession%20group%20by%20city"

provides the following result:

sqlcopydownload
{
    "columnNames": ["city",    "count(*)"],
    "values": [
        ["Dublin",    23],
        ["N. Virginia (Amazon)",    80],
        ["Portland",    56]
    ]
}

This code:

plaintextcopydownload
curl --location --insecure -H "Content-Type: application/json" -H "Authorization: Api-Token _token_" \
-XGET "https://{your-environment-id}.live.dynatrace.com/api/v1/userSessionQueryLanguage/tree?query=select%20country,city,count(*)%20from%20usersession%20group%20by%20country,city"

provides the following result:

sqlcopydownload
{
    "branchNames": ["country",    "city"],
    "leafNames": ["count(*)"],
    "values": {
        "United States": {
            "Portland": [56],
            "N. Virginia (Amazon)": [83]
        },
        "Ireland": {
            "Dublin": [24]
        }
    }
}