How could we get a report on the last login time for all users

  • Document ID:KM01450384
  • Creation Date:24-Mar-2015
  • Modified Date:24-Mar-2015
    • Micro Focus Products:
      quality center 11.52

This document has not been formally reviewed for accuracy and is provided "as is" for your convenience.

Summary

In general, the information about the user sessions is stored in the Site Administration database/schema - qcsiteadmin_db. The tables that store this information are SESSIONS_HISTORY and USERS. Details of when users have logged into Quality Center is stored in the SESSIONS_HISTORY table in qcsiteadmin_db. This table will have a record of all the sessions for all the users. Therefore, if the user has logged in recently, a record for this session would be located in this table. Users who are listed in the Site Administration -> Site Users tab would be located in the USERS table.

Question

1) For example, to get a list of all users listed in Site Users but have never logged into a project before, you have to query both the SESSIONS_HISTORY and USERS tables.

SELECT USER_NAME FROM USERS WHERE USER_NAME NOT IN (SELECT DISTINCT USER_NAME FROM SESSIONS_HISTORY);

This query will return a list of inactive users or users who have not logged into Quality Center.

START_TIME column in SESSIONS_HISTORY table stores the time stamp for the start of the session. This can be used to get a list of users in USERS table who have not logged in recently.

2) For example, to retrieve a list of users who have logged in the past but have not done so since May 1 2009, you can run the following query in Oracle:

select distinct USER_NAME from td.users where USER_NAME not in(SELECT DISTINCT USER_NAME FROM td.SESSIONS_HISTORY WHERE START_TIME >=(select cast('2009-05-01' as datetime)))