Sample UBI SQL queries

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

To query a demo workbench with synthetic data, see http://chorus-opensearch-edition.dev.o19s.com:5601/app/opensearch-query-workbench.

Queries with zero results

Queries can be executed on events on either the server (ubi_queries) or client (ubi_events) side.

Server-side queries

The UBI-activated search server logs the queries and their results, 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.