|Data provider||Read||Finbourne||Provided with LUSID|
Scheduler.Job.History provider enables you to write a Luminesce SQL query that retrieves job run history from Scheduler. Note to retrieve console output logs for job runs, use Scheduler.Job.HistoryWithLogs.
Note: The LUSID user running the query must have sufficient access control permissions to both use the provider and read Scheduler job run history data. This should automatically be the case if you are the domain owner.
select * from Scheduler.Job.History where <filter-expression>;
Scheduler.Job.History has parameters that enable you to filter or refine a query.
To list available parameters, their data types, default values, and an explanation for each, run the following query using a suitable tool:
select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Scheduler.Job.History' and FieldType = 'Parameter';
Scheduler.Job.History returns a table of data populated with particular fields (columns). You can return a subset of these fields.
To list fields available to return, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a suitable tool:
select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Scheduler.Job.History' and FieldType = 'Column';
Note: Fields marked 'main' are returned by queries that include a caret character, for example
select ^ from Scheduler.Job.History.
Note: For more example Luminesce SQL queries, visit our Github repo.
Example 1: Retrieve run history for the 10 most recent job runs
select * from Scheduler.Job.History limit 10
Example 2: Retrieve run history for a particular job
select * from Scheduler.Job.History where JobScope = 'transactions' and JobCode = 'upsert-transactions'
Example 3: Retrieve failed runs for a particular schedule
select * from Scheduler.Job.History where JobStatus = 'Failure' and ScheduleScope = 'transactions' and ScheduleCode = 'upsert-transactions-schedule'
Example 4: Retrieve the arguments each job ran with on a particular day
select Arguments from Scheduler.Job.History where StartDate = #2023-03-05#
Example 5: Retrieve schedule run history for a particular schedule name
In this example,
Scheduler.Job.History is joined with the Scheduler.Schedule provider to retrieve the history of schedule runs for a specified schedule name.
select * from Scheduler.Job.History h join Scheduler.Schedule s on h.ScheduleScope = s.ScheduleScope and h.ScheduleCode = s.ScheduleCode where s.Name = 'Automate upsert transactions'