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,
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:
TO_CHAR(HARPKGHISTORY.EXECDTIME, 'mm/dd/yyyy hh:mi:ss') EXECDTIME,
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'