Tables or query for job executions

[Vijay Chaganti]

Hi,
 Can I get the query or the tables that have the right information to pull all the job executions for recurring as well as JSCH.
 Similar to the Automic UI that displays all the job executions based on the object name we specify.



The AH table timestamp values do not match the execution of each job, they look more like scheduler reset every day, not each of the job execution time and duration that looks similar to the UI.

I want the query or tables that the Automic team might be used to display the executions.

 This is required as we need to pull these to Data Lake for further analysis.

Thanks

[Pete Wirfs]

AH is the correct table.  The timestamps are all stored as GMT values, so the product can easily support multiple timezones.

Ours is on SQLServer and I use this sort of logic to translate the AH timestamps to local time;

     , dateadd(hour, datediff(hour, getutcdate(), getdate()), ah_timestamp2) as start_time
     , dateadd(hour, datediff(hour, getutcdate(), getdate()), ah_timestamp4) as End_time​

Further descriptions of AH columns can be found here;
https://docs.automic.com/documentation/WEBHELP/English/all/components/AE/latest/DB%20Schema/db/_structure/HTML/index.htm

------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------

[Vijay Chaganti]

Hi Pete,
 Sounds good, Still not convinced with the time for each of the scheduled WF.
 Those times are within seconds as if the scheduler object was reset.

select oh_name as SCHEDULE,
ah_idnr,
oh_idnr,
ah_timestamp1 as act_time,
ah_timestamp2 as start_time,
ah_timestamp3 as end_time1,
ah_timestamp4 as End_time2
, jpp_object as OBJECT
, JPP_ErlstStTime as STARTTIME
, COALESCE(jppc_calekeyname , 'EVERYDAY') as CALENDAR
, DECODE(oh_inactive, 1, 'In-Active', 0,'Active') as Status
from jsuser.oh
left join jsuser.ah on ah.ah_oh_idnr = oh_idnr
inner join jsuser.jpp
on oh_idnr = jpp_oh_idnr
left outer join jsuser.jppc
on jppc_jpp_lnr = jpp_lnr and jppc_oh_idnr = oh_idnr
where oh_otype = 'JSCH' and jsuser.oh.oh_name like 'SCHD_NAME%'
order by start_time desc;

I want to pull all the executions of the workflow or jobs that are scheduled / recurring starting with e.g ABC%
I found some time in AJPP if we add that GMT those sound more like realistic times as they closely match to the execution times that show in the UI.

Do we have a close enough query that is used for the atomic to display in the process monitoring view based on the search?

Thanks
Vijay

[Pete Wirfs]

I don't think I understand what you are trying to accomplish.

This is the query I like to use to extract all JSCH rules;

select oh_client as client
      , oh_name           as SCHEDULE
      , jpp_object       as OBJECT
      , JPP_ErlstStTime  as STARTTIME
      , jppc_calekeyname as CALENDAR
   from oh
      inner join jpp 
         on oh_idnr = jpp_oh_idnr
      left outer join jppc 
         on jppc_jpp_lnr = jpp_lnr and jppc_oh_idnr = oh_idnr
  where oh_otype = 'JSCH' and oh_deleteflag = 0 and oh_client = 100
  order by 4,3,5;​

------------------------------
Pete Wirfs
SAIF Corporation
Salem Oregon USA
------------------------------

[Vijay Chaganti]

Pete,
 What I found is the Table AH will give the executions for the Recurring Jobs.
 For the JSCH jobs, we need to get it from AJPP, AH is not giving the executions rather gives the top-level JSCH object reset which is not very useful.
 Here is what I found:
Recurring:
select OH.OH_NAME WF_NAME,
OH.OH_IDNR JOB_ID,
AH.AH_IDNR EXECUTION_ID,
AH.AH_OTYPE,
AH.AH_Timestamp1 Activation_Time,
AH.AH_Timestamp2 Start_Time,
AH.AH_Timestamp3 End_Time_WithoutPostProcessing,
AH.AH_Timestamp4 End_Time
from JSUSER.AH AH
LEFT JOIN JSUSER.OH OH
ON AH.AH_OH_IDNR = OH.OH_IDNR
where AH_STYPE = '<PERIOD>' AND
AH_TIMESTAMP2 is not null and OH.OH_NAME = 'ABCD%' ORDER BY AH_TIMESTAMP2 DESC;

JSCH jobs we can directly query AJPP

SELECT AJPP_OBJECT AS WF_NAME, ajpp_status, ajpp_starttime, ajpp_endtime
from jsuser.ajpp
where ajpp_starttime is not null and AJPP_OBJECT LIKE 'ABCD%' order by ajpp_starttime desc;

Thanks
Vijay