Self-Solve Knowledge Search

Minimize Maximize
How could we get a report on the last login time for all users
This document has not been formally reviewed for accuracy and is provided as is
Title :
How could we get a report on the last login time for all users
Document ID :
KM01450384
Product - Version:
quality center 11.52 ;
OS :
Updated :
Tue Mar 24 07:29:40 GMT 2015
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.
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)))