Finding Large Files Stored in Harvest Repositories

Products

• CA Harvest Software Change Manager
• CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

Is there a report that shows a detailed breakdown of what is taking up space in each repository?

Environment

Harvest Software Change Manager all versions

Resolution

To get a list of which files are taking the most space, this query might help:

SELECT DISTINCT HARENVIRONMENT.ENVIRONMENTNAME,
  HARREPOSITORY.REPOSITNAME,
  HARPACKAGE.PACKAGENAME,
  HARVERSIONS.VERSIONOBJID,
  HARPATHFULLNAME.PATHFULLNAME,
  HARITEMNAME.ITEMNAME,
  HARVERSIONS.MAPPEDVERSION,
  HARVERSIONDATA.DATASIZE
FROM HARENVIRONMENT
INNER JOIN HARVIEW ON HARENVIRONMENT.ENVOBJID = HARVIEW.ENVOBJID
INNER JOIN HARVERSIONINVIEW ON HARVIEW.VIEWOBJID = HARVERSIONINVIEW.VIEWOBJID
INNER JOIN HARVERSIONS ON HARVERSIONS.VERSIONOBJID = HARVERSIONINVIEW.VERSIONOBJID
INNER JOIN HARVERSIONDATA ON HARVERSIONS.VERSIONDATAOBJID = HARVERSIONDATA.VERSIONDATAOBJID
INNER JOIN HARPATHFULLNAME ON HARVERSIONS.PATHVERSIONID = HARPATHFULLNAME.VERSIONOBJID
INNER JOIN HARITEMNAME ON HARITEMNAME.NAMEOBJID = HARVERSIONS.ITEMNAMEID
INNER JOIN HARPACKAGE ON HARPACKAGE.PACKAGEOBJID = HARVERSIONS.PACKAGEOBJID
INNER JOIN HARITEMS ON HARVERSIONS.ITEMOBJID = HARITEMS.ITEMOBJID
INNER JOIN HARREPOSITORY ON HARITEMS.REPOSITOBJID = HARREPOSITORY.REPOSITOBJID
WHERE HARENVIRONMENT.ENVOBJID > 0
AND HARVERSIONDATA.DATASIZE > 1000000  /* <--- threshold data size in bytes */
ORDER BY HARVERSIONDATA.DATASIZE DESC,
  HARREPOSITORY.REPOSITNAME,
  HARENVIRONMENT.ENVIRONMENTNAME,
  HARPACKAGE.PACKAGENAME,
  HARPATHFULLNAME.PATHFULLNAME,
  HARITEMNAME.ITEMNAME

NOTE: This query is offered as an example of how this task might be accomplished and should be used as a starting point in creating your own SQL queries for Harvest.