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

Map or join users table with user_logins table


#1

Hi there,

I am trying to lookup a User from the “users” table and then find information about that user in the “user_logins” table.

The problem is that these two tables store different users names in their Name fields. I.e., “users” stores full/real name whereas “user_logins” stores Linux user name. This means that I cannot join these two tables on the “name” field.

Is there any known solution for joining these two tables?

thanks,
-reagan


#2

I think the real question for you is - what information do you need to know by joining the tables? One of the weak spots in TR is getting information about when users logged in or how many active users there are.

The User login table really does not tell you much - it is only 5 fields. It does not track what the user did when they logged in.

This thread was started by me when I needed to find out about user activity in TR - maybe it will help you?


#3

Hello and thanks for the response.

I am trying to determine a couple of different things.

  1. I want to be able to tell which of our users have NEVER logged into the system. If I compare my total users count against successful login attempts I see successful logins < total users. This I assume tells me that a number of my users have NEVER logged into the system.

I would like to be able to determine who these users are i.e., map the user_logins with users as well as determine when their account was created.

It seems to me that this information should be readily available within the TR DB however, I cannot easily find it.

I can map between user_logins and users by finding all usernames from user_logins who have a successful login and then I their Real name on a NON TR system at which point I can locate them in the USERS table. The assumption here is that anyone left in the USRES table has never logged in before.

Seems quirky and this still does not tell me when an account was created.

cheers,
-reagan


#4

It is the one thing that is really missing from the system. No record will get inserted into the User_Logins table till they actually log in and then the CreateDate on that table will be the date they first logged in. The Users table really needs a CreateDate for when that record is first inserted.

In our db (we use MS SQL for the DB) we can probably use the Users.Email and the User_Logins.Name (they are both email addresses) - you would have to do a sub-query for where the Email not in User_Logins.Name or something like that.

Outside of that - dumping the data to an Excel spreadsheet (one column for Users and another for the User_Login table) and then do a comparison.

No matter what - I don’t know of a good and elegant way to get the data with the current User data structure.