Sample UBI SQL queries

You can run sample User Behavior Insights (UBI) SQL queries through the OpenSearch Dashboards Query Workbench.

Queries with zero results

Queries can be executed on events on either the queries index (ubi_queries) or events index (ubi_events).

Server-side queries

A UBI-enabled search server logs the queries and their results as they are made, so in order to find all queries with no results, search for empty query_response_hit_ids:

select count(*) from ubi_queries where query_response_hit_ids is null 

Client-side events

Although it’s relatively straightforward to find queries with no results on the server side, you can also get the same result by querying the event attributes that were logged on the client side. Both client- and server-side queries return the same results. Use the following query to search for queries with no results:

select count(0) from ubi_events where event_attributes.result_count > 0 

Trending queries

Trending queries can be found by using either of the following queries.

Server-side

select user_query, count(0) Total from ubi_queries group by user_query order by Total desc 

Client-side

select message, count(0) Total from ubi_events where action_name='on_search' group by message order by Total desc 

Both queries return the distribution of search strings, as shown in the following table.

Event type distribution counts

To create a pie chart widget visualizing the most common events, run the following query:

select action_name, count(0) Total from ubi_events group by action_name order by Total desc 

The results include a distribution across actions, as shown in the following table.

The following query shows the distribution of margins across user actions:

select action_name, count(0) total, AVG( event_attributes.object.object_detail.cost ) average_cost, AVG( event_attributes.object.object_detail.margin ) average_margin from ubi_events group by action_name order by average_cost desc 

The results include actions and the distribution across average costs and margins, as shown in the following table.

Sample search journey

To find a search in the query log, run the following query:

select client_id, query_id, user_query, query_response_hit_ids, query_response_id, timestamp from ubi_queries where query_id = '7ae52966-4fd4-4ab1-8152-0fd0b52bdadf' 

The following table shows the results of the preceding query.

The query field in query_id has the following nested structure:

{ "query": { "size": 25, "query": { "query_string": { "query": "(title:\"notebook\" OR attr_t_device_type:\"notebook\" OR name:\"notebook\")", "fields": [], "type": "best_fields", "default_operator": "or", "max_determinized_states": 10000, "enable_position_increments": true, "fuzziness": "AUTO", "fuzzy_prefix_length": 0, "fuzzy_max_expansions": 50, "phrase_slop": 0, "analyze_wildcard": false, "escape": false, "auto_generate_synonyms_phrase_query": true, "fuzzy_transpositions": true, "boost": 1.0 } }, "ext": { "query_id": "7ae52966-4fd4-4ab1-8152-0fd0b52bdadf", "user_query": "notebook", "client_id": "a15f1ef3-6bc6-4959-9b83-6699a4d29845", "object_id_field": "primary_ean", "query_attributes": { "application": "ubi-demo" } } } } 

In the event log, ubi_events, search for the events that correspond to the preceding query (whose query ID is 7ae52966-4fd4-4ab1-8152-0fd0b52bdadf):

select application, query_id, action_name, message_type, message, client_id, timestamp from ubi_events where query_id = '7ae52966-4fd4-4ab1-8152-0fd0b52bdadf' order by timestamp 

The results include all events associated with the user’s query, as shown in the following table.

User sessions

To find more of the same user’s sessions (with the client ID a15f1ef3-6bc6-4959-9b83-6699a4d29845), run the following query:

select application, event_attributes.session_id, query_id, action_name, message_type, event_attributes.dwell_time, event_attributes.object.object_id, event_attributes.object.description, timestamp from ubi_events where client_id = 'a15f1ef3-6bc6-4959-9b83-6699a4d29845' order by query_id, timestamp 

The results are truncated to show a sample of sessions, as shown in the following table.

List user sessions for users who logged out without submitting any queries

The following query searches for users who don’t have an associated query_id. Note that this may happen if the client side does not pass the returned query to other events.

select client_id, session_id, count(0) EventTotal from ubi_events where action_name='logout' and query_id is null group by client_id, session_id order by EventTotal desc 

The following table shows the client ID, session ID, and that there was 1 event,logout.

You may want to identify users who logged out multiple times without submitting a query. The following query lets you see which users do this the most:

select client_id, count(0) EventTotal from ubi_events where action_name='logout' and query_id is null group by client_id order by EventTotal desc 

The following table shows user client IDs and the number of logouts without any queries.