Talend TAC Job Conductor Trigger details from database

Below is a simple query to retrieve “Job Conductor – Task – Trigger” details from the database tables associated to Talend Admin Center (TAC).

select   et.id as executiontask_id --,et.dtype ,et."label" as task_name ,et.idquartzjob ,t.trigger_name ,t.job_group ,tt.dtype ,tt."label" as trigger_label ,tt.description as trigger_desc ,tt.active as trigger_active ,to_timestamp(t.next_fire_time/1000) as next_fire_time ,to_timestamp(t.prev_fire_time/1000) as prev_fire_time ,to_timestamp(t.start_time/1000) as start_time ,t.start_time / 1000 as start_time_seconds ,t.start_time as start_time_unix_milliseconds ,t.trigger_state ,ct.cron_expression ,tt.listminutes ,tt.listhours ,tt.listdaysofweek ,tt.listdaysofmonth ,tt.listyears ,et.jobid ,et.idremotejob ,et.jobname ,et.generatedprojectname ,et.generatedjobname ,et.generatedjobversion ,et.generatedsvnrevision ,et.artifactgroupid ,et.artifactid ,et.runasuser ,et.status ,et.errorstatus ,et.jobversion ,et.context ,et.branch ,et.active ,et.lastscriptgenerationdate ,et.lastdeploymentdate ,et.lastrundate ,et.lastendedrundate ,et.lasttriggeringdate ,et.jobscriptarchivefilename from executiontask et left join qrtz_triggers t on t.job_name = et.idquartzjob::varchar left join qrtz_cron_triggers ct on ct.trigger_name = t.trigger_name left join talendtrigger tt on ct.trigger_name = tt.idquartztrigger::varchar ;

NOTE

Within the qrtz_cron_triggers table, the cron expression could look similar to

0 1,2,3 4,5,6 ? 4 2,3,4,5,6 2020

Note that there’s a space to help separate the difference parts of the cron expression

seconds:0 –this is always zero as the cron-scheduler within TALEND doesn’t have the functionality to set to the second.

minutes:1,2,3
hours:4,5,6
days of month:? –this means it’ll run for every day in the month
month:4 –April = 4th month in the year
days of week (1 = Sunday):2,3,4,5,6 –this is for Monday, Tuesday, Wednesday, Thursday,  Friday
year:2020

EXECUTION PLAN

When Job Tasks are set up as a sequence, or have inter-relationships (parent child), or setup as some form of hierarchy within the EXECUTION PLAN CONSOLE, then these details can be viewed using the following query:

select   etp."label"  as parent_task_name ,et."label" as child_task_name ,case when e.executionplanpart_parent_id is null then 'Header' else '' end as Header ,e.id as executionplan_id ,e.executionplanpart_parent_id ,e.executionplan_executionplan_id ,e.executiontask_task_id ,e.status ,e.startdate ,e.enddate --,e.* --,et.* FROM public.executionplanpart e left join executiontask et on et.id = e.executiontask_task_id left join executiontask etp on etp.id = e.executionplan_executionplan_id order by e.executionplan_executionplan_id , e.id ;