3 Part Series: Using SQL to Help Manage Your Tealeaf Environment

Tealeaf provides an enormous amount of data about your customer’s web experience. It also provides a great level of detail about itself including: processing and storage statistics, user activity, and all configuration files with their respective change histories.  As a Tealeaf administrator, sometimes you have simple questions with no clear way to answer them.  
 
Unfortunately, in many cases, there isn’t an easy way to answer these requests via the portal. This is where some basic SQL queries can save the day (or at least a good couple of hours). 
 
 
There has to be a better way- addressing the most common challenges.
 
When it comes to Tealeaf, SQL can be your best friend. IBM Tealeaf provides insight into digital customer experience management and customer behavior analysis. Combined with the expertise of Stratigent’s consultants within our Customer Experience Practice, Tealeaf will give clients exceptional value from the data collected within the application.  
 
Tealeaf uses SQL Server to store all information about the portal, including the users, events, and reports.  This is the back-end database that is rarely touched except by the Tealeaf services running on the portal and reporting server.  The installed databases depend on which Tealeaf products you are licensed for, but the base ones include:
 
  • TL_SYSTEM - the base system store for the cxImpact Portal and Reporting services.
  • TL_REPORTS - aggregated statistics for the Tealeaf Portal reports.
  • TL_STATISTICS - performances data on the Tealeaf components, for health monitoring and troubleshooting.

These databases are NOT built in a user-friendly fashion and are NOT designed to be queried directly.  But, for some simple administrative questions, we’re going to dip into the TL_SYSTEM database.  This leads to a very important disclaimer:

WARNING – This is the Tealeaf system database, which does not forget or forgive mistakes.  Do NOT perform any update or delete processes to any Tealeaf databases unless you are following instructions from Tealeaf Client Support.  Remember, we’re just doing SELECTs to gather information here.  
 
In this 3 part series, I will highlight a few SQL tips i've gathered from years in the industry, to help you manage your Tealeaf Environment. I'll be answering these 4 common requests received by a Tealeaf admin: 
 
  1. ”I need a list of all Tealeaf users and when they last logged in”
  2. ”Can I just get a list of all Tealeaf events?”
  3. “Show me a list of all Hit Attributes and their associated Events”
  4. “Is there a way for me to use SQL to access Tealeaf data for a period of time and drop it into a data warehouse for use in an OLAP cube for analysis?” 
 
“I need a list of all Tealeaf users and when they last logged in.”
 
One of your jobs as Tealeaf administrator is managing user licenses.  Perhaps you the number of people who want access outweigh the number of licenses you have available. You might then come to realize that some of these users haven’t been active in a while, or might not even work there anymore.  
 
Tealeaf User Activity Reports will show who logged in on what day and give an exhaustive breakdown of what they did.  You can also go to Portal Management to see a list of all users; however, there is no way to extract that information into a nice spreadsheet for coworkers making this request.
 
More effectively, you can sign into SQL Management studio (using an account that at least has SELECT privileges on the TL_SYSTEM database) and run the following query:
 
/****** Show all Tealeaf users  ******/
SELECT [MC_USER_ID]
      ,[MC_USER_NAME]
      ,[PASSWORD]
      ,[EMAIL]
      ,[EMAIL_FLAG]
      ,[TZ_OFFSET]
      ,[TZ_TIMEZONE]
      ,[TZ_DAYLIGHT]
      ,[NT_DOMAIN]
      ,[NT_USER]
      ,[STARTPAGE]
      ,[DATE_FORMAT]
      ,[ACTIVE]
      ,[LAST_LOGIN]
      ,[DELETED]
  FROM [TL_SYSTEM].[dbo].[MC_USER]
 
This will display a list of all users, including system accounts and deleted accounts.  The important pieces for this inquiry are as follows:
 
  • MC_USER_NAME - Tealeaf user name.
  • PASSWORD - Not actually important, but wanted to point out that this field is encrypted so this does NOT display the actual password value.  It is only populated if NT authentication is not being used.
  • NT_DOMAIN - The Active Directory domain (if applicable).
  • NT_USER - The Active Directory user name (if applicable).
  • ACTIVE – Is 1 if user is active, 0 if not.
  • LAST_LOGIN – The date/time value that the user last logged into Tealeaf.
  • DELETED – Is 0 if the user has not been deleted.  1 if the account has been deleted.
Useful note:  If the LAST_LOGIN value is “1970-01-01 00:00:00.000” then the user has never logged into Tealeaf before.
 
 
“Can I just get a list of all the Tealeaf events?” 
 
This is such a simple question – with a seemingly simple answer. The supposed solution might be: go to Event Activity, check “Include Zeroes”, and then export it to Excel. Unfortunately, this will not provide the appropriate detail, such as any inactive or deleted events.  Tealeaf stores event data across several tables, which adds a layer of complexity to fulfilling this ask. Luckily, there is a view that contains the basic details which you might find helpful. Try this:
 
/****** This view shows all Tealeaf Events, both Active and Inactive  ******/
SELECT [ID]
      ,[CONTAINED_BY_ID]
      ,[MODEL_ID]
      ,[CREATED]
      ,[UPDATED]
      ,[INTERNAL_NAME]
      ,[MM_TYPE]
      ,[DISPLAY_NAME]
      ,[NOTES]
      ,[DELETED]
      ,[CONDITION_GROUP_ID]
      ,[TRIGGERED_BY_ID]
      ,[ACTIVE]
      ,[ADVANCED]
      ,[TRACK_DEVIATION]
      ,[RESULTS_SESSION_QUALIFIER_ID]
      ,[JAVASCRIPT]
      ,[LEGACY]
  FROM [TL_SYSTEM].[dbo].[TM_EVENT]

 
This command will list every event that is or ever was.  Useful fields include the following:
 
  • ID - This is the Tealeaf ID of the event.  You’ll find this if you’ve ever seen an error entry in the Windows Application log complaining about an event id.  You also see this value in the hover-over text in Event Editor, but there is no way to search for it in the portal.
  • CREATED – Date/time event was created.
  • UPDATED – Date/time event was last updated.
  • INTERNAL_NAME – The name Tealeaf uses internally for this event.
  • DISPLAY_NAME – The actual displayed name provided in Event Editor.
  • NOTES – The Description field in Event Editor.  It’s a great practice to actually use this to describe the event!
  • DELETED – If 0, the event is active. If 1, it was deleted.
  • JAVASCRIPT – The full JavaScript text of the event definition.  This is what you’d see in Advanced Mode in Event Editor.  If you are using this result set to import into a database, you may want to exclude this column as it is a VARCHAR(MAX) and can be enormous.
  • LEGACY – If 1, this event was imported from a Tealeaf 7.x environment.
 
This has been pretty easy so far, right?  We’ve basically done a SELECT ALL on a table and a view that are sitting in plain sight.  
 
Once you’ve got your hands on a list of all users with log in information as well as the list of all events and their status, you’d probably still like to address a couple more common requests such as:
 
“Show me a list of all Hit Attributes and their associated Events”

“Is there a way for me to use SQL to access Tealeaf data for a period of time and drop it into a data warehouse for use in an OLAP cube for analysis?” 

 
Stay tuned for my two upcoming posts which will address these remaining requests.  In the meantime, I’d love to answer any questions you might have via the comments below! 
 
I can also be reached at info@stratigent.com
 
 
 
 
 
 
 
 
By Customer Experience Team
About the Author:

Written by the Stratigent Customer Experience Team.

Contact Us Now