Applies To:
HP ALM/QC 11.52, Oracle Database
Issue:
The user wants to report on old projects and archive projects that have not been logged into for some time.
Solution:
A. Find the total number of projects in the database:
select * from QCSITEADMIN_DB.projects;
B. Find the number of projects that HAVE been logged into within the past year:
select distinct h.project_name, domain_name from qCSITEADMIN_DB.SESSIONS_HISTORY h;
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 * from QCSITEADMIN_DB.projects p where p.PROJECT_NAME not in (select distinct h.project_name, h.domain_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.