Applies To:
Seeing which projects have been active in the last 3 months
Issue Summary:
Is there a way to get a list of active projects not used in last x months in HP Application Lifecycle Management (ALM)/ Quality Center(QC)?
Fix:
This could be done with simple SQL query over qcsiteadmin_db database on your DB server.
Note:Please ask your Database Administrator(DBA) for assistance on this.
In the following example is used a 3-months period.
For MS SQL:
WITH Not_Useed_Projects
AS (
SELECT sh.DOMAIN_NAME
, sh.PROJECT_NAME
, MAX(END_TIME) AS LAST_USAGE
FROM SESSIONS_HISTORY AS sh
WHERE END_TIME IS NOT NULL
GROUP BY sh.PROJECT_NAME, sh.DOMAIN_NAME
)
SELECT nup.*
FROM Not_Useed_Projects AS nup INNER JOIN
PROJECTS p on p.PROJECT_NAME = nup.PROJECT_NAME AND PR_IS_ACTIVE = 'Y'
WHERE LAST_USAGE <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -3, 0)
For Oracle:
WITH Not_Useed_Projects
AS (
SELECT sh.DOMAIN_NAME
, sh.PROJECT_NAME
, MAX(END_TIME) AS LAST_USAGE
FROM SESSIONS_HISTORY AS sh
WHERE END_TIME IS NOT NULL
GROUP BY sh.PROJECT_NAME, sh.DOMAIN_NAME
)
SELECT nup.*
FROM Not_Useed_Projects AS nup INNER JOIN
PROJECTS p on p.PROJECT_NAME = nup.PROJECT_NAME AND PR_IS_ACTIVE = 'Y'
WHERE LAST_USAGE <= ADD_MONTH(sysdate, -3)