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

Best way to report on users not creating/assigned to cases/runs


We have a limited amount of licenses and we need more but… management.

Anyway - I need to find a good way via SQL to report on users that have not been assigned a run or creating cases in the last XX days so we can inactivate users that are not currently testing. I am going to assume that the API does not have anything to really help with this as well.

I know I can’t use the Sessions table as records from that table are deleted when 30 days old… Other than that - am I correct that the only other way to do this is to use the Changed_on/Changed_by and Updated_on/Update_by on several tables (cases/runs etc…) and find the max date per user (most recent create/update), calculate how many days in the past for each one and filter for those XX days old.

Any other way/suggestions? We are on 4.1 right now with self hosted TR. Naturally will not update anything in the DB - just straight up query.

Would Gurock consider adding a setting in the Administration to determine how many days to save the Sessions data with a default of 30? That would help a ton if we could set it to 120 days - give us a lot clearer idea of who has not logged in recently (naturally we would have to add in a rule for users to log out after testing).




Hello Brian,

Thanks for your posting. The session purge is currently set to 30 days but it might be good idea to make this more flexible/configurable. Checking the created_on/by and updated_on/by fields wouldn’t take into account read-only users so there’s currently no perfect solution for this. Using this approach would certainly be a good indicator but those users might still use TestRail even though they don’t actively add results or cases, for example.



Yea - it would give a list to start with and I could not include the admins because the QA team are the admins so it would limit the results to the users/tester/UAT folks.

Thanks for the information.