Skip to main content

Native queries to fetch data explorer data/columns

The existing filters in the data explorer only allow filtering rows. By using SQL queries, we can filter and display the desired columns.

Follow these steps to create a SQL query in Data explorer:

  1. Open Data Explorer > Click Create Report > Select Native Query.
  2. In Create Report section, write the queries based on your requirements.

Here are some SQL queries you can use:

  • To get all the columns from the messages database:
SELECT * FROM messages;

  • To get specified columns (date, UID, and session ID):
SELECT __time AS CreatedDate, uid AS UID, sessionId AS SessionID FROM messages;

  • To create new columns with static values (e.g., bot ID and bot name):
SELECT "x1689236272568" AS BOTID, "Bhavana" AS BOTNAME, uid AS UID FROM messages;

  • To get the count of each user in the database based on the UID:
SELECT COUNT(*) AS total_users, uid AS UID FROM messages GROUP BY uid;

  • To get chat details through the messages database:
SELECT __time AS CreatedDate, "x1689236272568" AS BOTID, uid AS UID, messageType AS MessageType, sessionId AS SessionID, journey AS Journey, step AS Step FROM messages;

  1. Save the query and create dashboards to filter the data based on the timestamp for further use cases.

  1. Click Create Dashboard > select New Dashboard.

  1. Go to Custom Dashboards to view the newly created dashboard.