CA Harvest Software Change Manager - OpenMake Meister
For auditing purposes, we occasionally have to provide Production deployment information. I currently use the SQL below to pull the information we need via HarWeb, but I am looking for help to update this. The following SQL attaches the User ID of the person that created the package.. I need to be able to pull the ID that promotes/deploys to Production....which is usually not the package creator. Is it possible to get the ID from the history when going to Prod, and if so, what change would the SQL need ? Thanks much for any info you can provide ! ( see SQL below ) :
SELECT distinct a2.Packagename, a1.Environmentname, a3.Viewname, a2.Creationtime, a4.Username from HARENVIRONMENT a1, HARPACKAGE a2, HARVIEW a3, HARALLUSERS a4 where (a3.viewname = 'Production' and a2.viewobjid = a3.viewobjid and a2.creationtime between '01-jan-17'AND '17-aug-17' ) and a1.envobjid = a2.envobjid and a2.creatorid = a4.usrobjid order by a1.environmentname;
CA Harvest SCM all versions and platforms. Query was written for an Oracle database
Actually, the information you need is in the HARPKGHISTORY table, which records package creation, promotes, demotes and switch-package processes. This query will come closer to what you need:
SELECT HARPACKAGE.PACKAGEOBJID, HARPACKAGE.PACKAGENAME, HARPKGHISTORY.ENVIRONMENTNAME, HARPKGHISTORY.STATENAME, HARPKGHISTORY.ACTION, TO_CHAR(HARPKGHISTORY.EXECDTIME, 'mm/dd/yyyy hh:mi:ss') EXECDTIME, HARUSER.USERNAME FROM HARPACKAGE INNER JOIN HARPKGHISTORY ON HARPACKAGE.PACKAGEOBJID = HARPKGHISTORY.PACKAGEOBJID INNER JOIN HARUSER ON HARUSER.USROBJID = HARPKGHISTORY.USROBJID WHERE UPPER(HARPKGHISTORY.ACTION) = 'PROMOTE' AND HARPKGHISTORY.STATENAME = 'Production' AND HARPKGHISTORY.EXECDTIME BETWEEN '01-jan-17'AND '17-aug-17'