Looking for updates HarWeb SQL to pull data

Products

• CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

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;

Environment

CA Harvest SCM all versions and platforms.
Query was written for an Oracle database

Resolution

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'