GerryJandeska609472

SQL Query Pulling Execution User:  Auto Processing or Manual Execute

Discussion created by GerryJandeska609472 on Jun 13, 2018
Latest reply on Jul 23, 2018 by Pete Wirfs

We have a requirement to develop a SQL query that will select Objects executed over a certain time period, display their statuses, and display the Users who executed those Objects.

The Query below works fine, except for one point.  The User field represents the User who manually executed the Object, but it also lists Objects that were automatically executed by the Schedule.  in the latter case, the User is the last person who saved a change to the Schedule Object.  So, this is not very helpful.

How could the query be modified to differentiate between Users who manually executed the Object, as apposed to those Objects that were automatically executed by a Schedule? 

 

Thanks,

Gerry

-------------------------------------------------------

SELECT UNIQUE ah_name as "Object Name", ah_RetCode as "Return Code",

CAST((FROM_TZ(CAST(ah_timestamp2 AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Central') AS DATE) as "Start Date Time",

CAST((FROM_TZ(CAST(ah_timestamp4 AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Central') AS DATE) as "End Date Time",

usr_FirstName as "First Name", usr_LastName as "Last Name"

FROM uc4.ah, uc4.oh, uc4.usr

WHERE USR_OH_Idnr = AH_USR_Idnr

          AND AH_OH_Idnr =  OH_Idnr

          AND ah_timestamp2 < SYSDATE -1

          AND ah_timestamp2 > SYSDATE -2

          AND ah_oh_idnr IN (SELECT oh_idnr

                    FROM uc4.oh

          WHERE oh_otype in ('JOBP','JOBS','EVNT','SCRI','JOBF'))

ORDER BY ah_name ;

-------------------------------------------------------

Outcomes