Visitor Database Schema

Tealeaf publishes a set of views in the Visitor Reporting database to facilitate custom reports and queries against the data. These views will be maintained in future releases regardless of underlying schema changes.

Note: It is strongly recommended that any custom reports or queries reference the views and avoid directly referencing the underlying tables.
Table 1. Visitor Database Schema
View Name Description Columns
VW_DATASET Lists the current segments in the database along with their sessions and a count of how many times each session matched the search terms. ID, VISITOR_ID, SESSION_ID, MATCH_1, MATCH_2, MATCH_3, MATCH_4
VW_EVENT Returns the list of all regular event occurrences for sessions currently in the database. ID, TIMESTAMP, VISITOR_ID, SESSION_ID, UNIQUE_ID, URL, PREV_URL, SERVER, HOST, APP, HIT_NUMBER, VALUE
Note: Beginning in Release 8.0, the structure of Tealeaf events changed. To maintain consistency with previous versions of IBM Tealeaf cxResults, the columns in VW_EVENT remain unchanged. In Release 8.0 and later, however, the following columns contain null values:
UNIQUE_ID, PREV_URL, SERVER, HOST, and APP.
VW_NUMERIC_EVENT Returns the list of all numeric event occurrences for sessions currently in the database. ID, TIMESTAMP, VISITOR_ID, SESSION_ID, UNIQUE_ID, URL, PREV_URL, SERVER, HOST, APP, HIT_NUMBER, VALUE
VW_REPORTS Returns the list of defined visitor report types. Any report added using the steps for creating a custom report should appear in this view. ID, IDENTIFIER, DISPLAY_NAME, CUSTOM, FORMAT, STORED_PROCEDURE
VW_SESSION Returns the list of sessions currently contained in the Visitor database. CANISTER_SESSION_ID, TLTSID, START, END, LENGTH, REPEAT_VISIT, PAGE_COUNT, REQUEST_SIZE, RESPONSE_SIZE, IP, LOGIN. USERDEF1, USERDEF2, USERDEF3, USERDEF4, FIRST_PAGE, LAST_PAGE, REFERRER, BROWSER, HOSTNAME, FILE
VW_SESSION_ATTRIBUTE Returns session attribute data.
  • SESSION_ID - Database session ID for joining to session and event data
  • CANISTER_SESSION_ID - The session ID from the Canister
  • TLTSID - the session's TLTSID
  • ATTRIBUTE_ID - The database ID for the session attribute
  • ATTRIBUTE_TYPE - T for text attributes; N for numeric attributes
  • ATTRIBUTE_VALUE - The value represented as a sql_variant. Based on the ATTRIBUTE_TYPE column, it can be cast to either NVARCHAR or FLOAT.
  • HOSTNAME - The server name of the Canister
  • FILENAME - The Canister file containing the session
VW_SESSION_EVENT Returns the same list of sessions as VW_SESSION with the addition of a comma separated list of event UNIQUE_ID values. This view is less efficient than VW_SESSION and should only be used when the event list is required. CANISTER_SESSION_ID, TLTSID, START, END, LENGTH, REPEAT_VISIT, PAGE_COUNT, REQUEST_SIZE, RESPONSE_SIZE, IP, LOGIN, USERDEF1, USERDEF2, USERDEF3, USERDEF4, FIRST_PAGE, LAST_PAGE, REFERRER, BROWSER, HOSTNAME, FILE, EVENT_UNIQUEIDS
VW_VISITOR Returns data on the list of visitors in the database. ID, VISITOR_ID, FIRST_VISIT, LAST_VISIT, LOOKUP
VW_VISITOR_STATISTICS Returns database wide statistics grouped in hour intervals. UNIQUE_VISITORS, TOTAL_VISITS, ONE_HIT_VISITS, NEW_VISITS, REPEAT_VISITS
Parent topic: cxResults databases