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


#1

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).

Thanks,

Brian


#2

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.

Regards,
Tobias


#3

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.