Database queries for the most checked out package and its projects for last two months

Products

• CA Harvest Software Change Manager

Issue/Introduction

We would like database queries so we can find out what is the most checked out package and its project for last two month.

Environment

CA Harvest Software Change Manager all versions

Resolution

Run this query first

SELECT
    PACKAGEOBJID,
    COUNT(PACKAGEOBJID) AS pid
FROM
    HARVERSIONS
Where MODIFIEDTIME > '2025-08-30 00:00:00.000' 
GROUP BY
    PACKAGEOBJID
ORDER BY
    pid DESC;

SELECT
    PACKAGEOBJID,
    COUNT(PACKAGEOBJID) AS pid
FROM
    HARVERSIONS
Where MODIFIEDTIME > '2025-08-30 00:00:00.000' 
GROUP BY
    PACKAGEOBJID
ORDER BY
    pid DESC;

(notice that here we assume today is 2025-10-30 so that timestamp counts back to 2 months)

This is an example of the output

Once you have the PACKAGEOBJID from above query(in this case it is 6), you can run another query to get its name and the project it belongs:

select a.PACKAGENAME, b.ENVIRONMENTNAME  

from HARPACKAGE a, HARENVIRONMENT b 

where a.PACKAGEOBJID=n and a.ENVOBJID=b.ENVOBJID

(where n is the output of the previous example for the top PACKAGEOBJID and in this example it is 6)

And this is an example of the output

(in the example, the most checked out package is "CR-2" and it belongs to project "Tutorial 1.0")