Applies To:
HP ALM/QC 11.52, Oracle Database
Issue:
The user wants to report on old users. Old users who they could potentially delete or deactivate.
Solution:
A. Find the total number of users in the database (Note, this includes _SYSTEM_USER_ profiles or the performance center system user so the count could be off by a few) :
select * from QCSITEADMIN_DB.users;
B. Find the number of users who HAVE logged in within the past year:
select distinct user_name from QCSITEADMIN_DB.SESSIONS_HISTORY
C. Find the number of users who have not logged in within the past year (note, a bug was discovered where the Username was sometimes lowercase in the Sessions_History table so if using an oracle database, make sure to UCASE or UPPER the usernames).
select distinct u.user_name, u.full_name from QCSITEADMIN_DB.users u
where UPPER(u.user_name) not in (select distinct UPPER(h.user_name) from qCSITEADMIN_DB.SESSIONS_HISTORY h);
D. Validate Results - Take the number of records from step A and subtract the total records from step B. This number should give you the total number of records to expect in step C.