This has been a busy, busy week. I’ve never posted an article a day before. I kicked off the week with my first post on Spotfire logging. I explained how to set up and configure logging, as well as install logging information links and a DXP with logging data. My second post explained how to interpret the data in the ACTIONLOG information link. The third post explained log categories and actions so that in the fourth post you could understand how to use all the other logging views. This is the final post of the week, which wraps everything up. It will explain how to join logging data sets to get exactly the logging data you need. Read on to find out more.
Join Logging Views
Joining logging views is very simple. All of the logging views contain a SESSION_ID column. Users generate a SESSION_ID anytime they open Spotfire, whether they open the desktop client or the web player. Session IDs are also generated by Spotfire services like Automation Services. Below, I have shown the SQL to create a join between AUTH_LOGIN and AUTH_LOGOUT in the information designer. AUTH_LOGIN and AUTH_LOGOUT will have data anytime a user hits the Spotfire server. I was particularly interested in this data because it also contains the CLIENTTYPE and CLIENTVER.
Notice the use of a left outer join rather than an inner join or full outer join. A full outer join would be okay, but I wouldn’t use an inner join. I’ve observed in my data that sessions frequently have a login but no logout. I assume this happens when a user opens the web player and their session times out or perhaps the desktop application crashes and shuts down. If an inner join was used, you would lose the login anytime a logout wasn’t present.
Additionally, I suggest investigating each table before you make the join. One of the first things I tried to do was to join a table to the ACTIONLOG, but the ACTIONLOG has several records per SESSION_ID. Spotfire spun for half an hour trying to make it happen before I killed it. Joining to ACTIONLOG wasn’t appropriate, but joining login and logout data was.
Who is using the Web Player vs the Desktop Client?
When I first started investigating this data set, I hoped to use the CLIENTTYPE to help delineate web player and desktop usage. However, a quick cross table reveal a bit of a problem.
As you can see in the data table, where the CLIENTTYPE = TIBCO Spotfire Web Player, the USER_NAME and other identifying data were not populated. I had to reach out to TIBCO support for this. This happens because web player activity is logged on the server as the web player service contacting the server, not the individual user contacting the server.
TIBCO did help me come up with a workaround, and then I later found my own workaround as well, which I’ll show you below.
TIBCO Workaround
Instead of using AUTH_LOGIN_LOGUT, TIBCO simply routed me to the AUTH_WP_LOGIN information link. This information link is limited to web player activity, and individual user actions are recorded here. I don’t need logout activity, but if I did, I would just join to AUTH_WP_LOGOUT. I created a cross table showing the max([LOGGED_TIME]) or the last login. Now, I can count how many users are active in the web player.
My Workaround
I also came up with my own workaround for this problem – Relations. When I was still new to working with this data, I attempted to join ACTIONLOT to AUTH_LOGIN_LOGOUT, and as I mentioned before Spotfire spun for half an hour trying to make the join, I killed it. Instead, use relations to work with the data in separate tables. Relate the ACTIONLOG to AUTH_LOGIN_LOGOUT in the Data Table Properties menu and then integrate filtering in the Filter Panel. If you are unsure of how to do this, check out this post.
So, what I have done here is filtered down to the records where there is no display name (or usename or email) in AUTH_LOGIN_LOGOUT. Then, I pick one of the session IDs and filter to just that one session. Because my tables are related, I can see the ACTIONLOG data for that session. Based on the ACTIONLOG, I can see who the user was even though his name wasn’t captured.
I took this one step farther and linked up all three of the tables. You can see the data in each table for the same session ID.
So, that wraps up my 5 post series on Spotfire logging. You should now know the SESSION_ID is the key to join logging data sets. I hope you found this series helpful and informative. I will be releasing similar content on my YouTube channel soon.
Spotfire Version
Content created with Spotfire 10.2.
Check Out These Posts
- How to Set Up Spotfire Logging (release Monday, March 16)
- How to Use the Spotfire ACTIONLOG (release Tuesday, March 17)
- Understanding Spotfire Logging Categories and Actions (release Wednesday, March 18)
- Understanding Spotfire Logging Views (release Thursday, March 19)
Pingback: Understanding Spotfire Logging Views » The Analytics Corner