Query To Find The Latest Job Run Per Trigger And Find The Next Trigger Time

[system]

Query to find the latest Job run per trigger and find the next trigger time.

Disclaimer: First, try to achieve the requirement by the available Orchestrator Rest API endpoints (https://ORCHESTRATOR_HOSTNAME/swagger/index.html). It is suggested to get the help of a Database expert as UiPath never recommends any DB interaction.


Query to find the latest Job run per trigger and find the next trigger time

SELECT j.Id as JobId, p.Id as CronId, p.TenantId , r.[Name] as ProcessName,j.ReleaseId, j.StartTime, j.EndTime , j.CreationTime , p.StartProcessCron , P.Name as Triggername ,
r.[Key] , DATEADD(MINUTE,(DATEDIFF(MINUTE, 0, CAST((qz.PREV_FIRE_TIME - 599266080000000000) / 10000000 / 24 / 60 / 60 AS DATETIME)) ), 0) AS PREVIOUS_SCHEDULE , DATEADD(MINUTE,(DATEDIFF(MINUTE, 0, CAST((qz.NEXT_FIRE_TIME - 599266080000000000) / 10000000 / 24 / 60 / 60 AS DATETIME)) ), 0) AS NEXT_SCHEDULE , p.Enabled
FROM [uipath].[dbo].[ProcessSchedules] p , [uipath].[dbo].[Releases] r , [uipath].[quartz].[QRTZ_TRIGGERS] qz , [uipath].[dbo].[Jobs] j
where r.Id = p.ReleaseId
and p.ExternalJobKey = qz.JOB_NAME
and j.ReleaseId = p.ReleaseId
and p.Enabled =1 and p.IsDeleted=0
and j.id in (select max(id) from [uipath].[dbo].[jobs] where ReleaseId = p.ReleaseId and StartingScheduleId=p.id)


Example: