Join 34,000+ subscribers and receive articles from our
blog about software quality, testing, QA and security.

Sessions table


#1

Hi,

If I wanted to get a list of users and their last activity in TR - can I use the Sessions table to do this? Is the sessions table a list of users currently logged into TR and as soon as they log out - the users records are removed from the table?

For as long as we have had TR - the sessions table has less than 100 records so there must be adding in and then removing records from the table. Maybe as long as you do not click on logout and just close the browser the session record does not get removed?

The end all of the question is - can we determine who is active in TR via SQL and the tables or who has not logged in in the past 30+ days so we can make then inactive.

We did find the User_Logins table - will the updated_on field work for what we want to do?

Thanks,

Brian


#2

Hello Brian,

Thanks for your posting. Yes, the sessions table would be your best option for this. This contains the current user sessions and is updated regularly (for each user). Old, expired sessions (no activity for more than 30 days) are automatically removed. The user_logins table captures login attempts on the login form but this would not apply for users who have a current session or remember-me cookie (only for actual login attempts on the standard login form).

Regards,
Tobias


#3

Hello,

Can you please provide an example, how I can distinguish users in Sessions table? It looks quite cryptic for me.


#4

You have to parse out the user_id:XX in the user_data field and match it to the Users table.


#5

Thank you for reply, another one then, it is said in Tobias answer that “Old, expired sessions (no activity for more than 30 days) are automatically removed.”. Can I see somewhere age of the session? What numbers in the “last_activity” column mean?


#6

Last Activity is the date in Unix/Epoch time - the number of milliseconds since Jan 1, 1970

There are web sites that can convert the string and you can also get the date in SQL:

SELECT
DATEADD(ss, created_on , ‘1/1/1970’) AS CreateDate,
DATEADD(ss, updated_on , ‘1/1/1970’) AS Update_date
from cases

The above in SQL will convert the time - most date fields in Test Rail use Unix/Epoch. You would just change the field in the above to last_activity.


#7

Thank you so much for your help. All this troubles with queries looks way too complicated for one who just want to know who is still active and who isn’t.


Map or join users table with user_logins table
#8

I agree - not the easiest to try and figure out how many licenses we needed and to justify them. Took me a bit of time to figure out what I could do to try and see who was and was not using the system.