Retrieving Quality Center user login statistics

In the Quality Center Site Administration console there is a Site Analysis tab which gives you a break-down of the QC license usage over time. This is useful if you want to understand the overall usage patterns of Quality Center but it does not give detailed information about the individual users or their login statistics (such as the projects each user has logged in to, total login count or individual login times).

This information is recorded but to get it we need to dig around in the QC Site Admin database. The table that contains this information is called SESSIONS_HISTORY and contains the following fields:

1. Session ID (int) - Unique ID (Primary key).
2. Start_Time (DateTime) - Date and time that the user logged in.
3. End_Time (DateTime) - Date and time that the user logged out. If null it means the session is active.
4. Domain_Name (Varchar 255) - Domain of the project which the user logged in to.
5. Project Name (Varchar 255) – Project that the user logged in to.
6. User_Name (Varchar 60) - User name.
7. Client_Type (Varchar 50) - Client type (either a normal user or a QTP test execution client).

A simple query to extract session information from this table from a given date, would be:

You may want to know when the last login was for each user, together with the total number of times that user logged in to Quality Center since a given date, ordered by user. To get this information your query should look like this:

It would be very useful if we could, for example, have each user’s email address included in this report. All user-related information is contained in the USERS table, so to do this we need to join the USERS table with the SESSIONS_HISTORY table. The query will now look like this:

You may not always have direct access to the database through a query tool such as Query Analyzer. In that case you can use the Quality Center Site Administration console. Open the console and expand the project tree until you are able to select a table. When you select a table an edit box will appear where you can paste a query. Click the Execute SQL button to execute the query.

The query above will give an error when executed in this way because the tables that we refer to in the query reside in the QC Site Admin database and not the project database that we are connected to. To get around this problem you need to prefix the Site Admin database before each table referenced in the query, like this:

We can now see who has logged in to Quality Center since a given date, as well as the projects that they logged in to, the last date and time that they logged in to each project, the total number of logins to each project since the given date and their email address.