Scope

Provide technical information on the standard data model that is available for reports.
Users can use this information to create their own reports from the data model, see JobScheduler Reporting Interface (1.11.x) - Technical Information - Data Model Customization
SQL scripts in this article are provided for PostgreSQL. The SQL scrpts for the respective DBMS are provided by the JobScheduler installer.

Standard Data Model

Standard Tables

Inventory Tables

The following tables are managed by reporting inventory jobs that create an inventory of existing jobs, job chains and orders.
Records are overwritten by each run of the reporting inventory jobs.
SQL Scripts: inventory.sql
- SQL scripts in this article are provided for PostgreSQL. The SQL scrpts for the respective DBMS are provided by the JobScheduler installer.

INVENTORY_AGENT_CLUSTERMEMBERS

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key	X	references `INVENTORY_INSTANCES.ID`
AGENT_CLUSTER_ID	NOT NULL	NUMERIC	Foreign Key	X	references `INVENTORY_AGENT_CLUSTERS.ID`
AGENT_INSTANCE_ID	NOT NULL	NUMERIC	Foreign Key	X	references `INVENTORY_AGENT_INSTANCES.ID`
URL	NOT NULL	VARCHAR			agent url references `INVENTORY_AGENT_INSTANCES.URL`
ORDERING	NOT NULL	NUMERIC			Order of agents in remote_schedulers element
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

INVENTORY_AGENT_CLUSTERS

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key		references `INVENTORY_INSTANCES.ID`
PROCESS_CLASS_ID	NOT NULL	NUMERIC	Foreign Key	X	references `INVENTORY_PROCESS_CLASSES.ID`
SCHEDULING_TYPE	NOT NULL	VARCHAR			type of scheduling configured for the agent cluster `first` Fixed Priority Scheduling `next` Round Robin Scheduling `single`
NUMBER_OF_AGENTS	NOT NULL	NUMERIC
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

INVENTORY_APPLIED_LOCKS

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`JOB_ID`	NOT NULL	NUMERIC	Foreign Key	X	references `INVENTORY_JOBS.ID`
LOCK_ID	NOT NULL	NUMERIC	Foreign Key	X	references `INVENTORY_LOCKS.ID`
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

INVENTORY_FILES

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key	X	references `INVENTORY_INSTANCES.ID`
`FILE_TYPE`	NOT NULL	VARCHAR			file types in use: `job` `job_chain` `order` file types not used: `process_class` `config` `lock` `schedule` `...`
`FILE_NAME`	NOT NULL	VARCHAR		X	path from live directory e.g. `/test/my_job.job.xml`
`FILE_BASENAME`	NOT NULL	VARCHAR			file base name with extension e.g. `my_job.job.xml`
`FILE_DIRECTORY`	NOT NULL	VARCHAR			path calculated from the `live` directory e.g. `test`
`FILE_CREATED`	NULL	DATETIME			UTC timestamp of the creation date of the file
`FILE_MODIFIED`	NULL	DATETIME			UTC timestamp of the modification date of the file
`FILE_LOCAL_CREATED`	NULL	DATETIME			local timestamp of the creation date of the file
`FILE_LOCAL_MODIFIED`	NULL	DATETIME			local timestamp of the modification date of the file
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

INVENTORY_INSTANCES

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
OS_ID	NOT NULL	NUMERIC	Foreign Key		references `INVENTORY_OPERATING_SYSTEMS.ID`
`SCHEDULER_ID`	NOT NULL	VARCHAR		X	corresponds to the `SCHEDULER_ID` that is specified during installation of the JobScheduler instance
`HOSTNAME`	NOT NULL	VARCHAR		X	host on which the JobScheduler instance has been installed
`PORT`	NOT NULL	NUMERIC		X	HTTP port that the JobScheduler instance is listening to.
`LIVE_DIRECTORY`	NOT NULL	VARCHAR			path of the `live` directory
VERSION	NOT NULL	VARCHAR			JobScheduler version e.g. 1.11.0
COMMAND_URL	NOT NULL	VARCHAR			HTTP URL for XML request response
URL	NOT NULL	VARCHAR			TCP URL still used by supervisor
TIMEZONE	NOT NULL	VARCHAR
CLUSTER_TYPE	NOT NULL	VARCHAR			possible values standalone active passive
PRECEDENCE		NUMERIC			precendence of JobScheduler in a passive cluster e.g. 0 (primary) , 1 (backup no. 1), 2 (backup no. 2) etc
DBMS_NAME	NOT NULL	VARCHAR			name of the database vendor e.g. MySql , Oracle, etc
DBMS_VERSION		VARCHAR			release number of database system
STARTED_AT		DATETIME
SUPERVISOR_ID		NUMERIC			reference `INVENTORY_INSTANCES.ID`
AUTH		VARCHAR
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

INVENTORY_JOB_CHAIN_NODES

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key	references `INVENTORY_INSTANCES.ID`
JOB_ID	NOT NULL	NUMERIC	Foreign Key	references `INVENTORY_JOBS.ID`
`JOB_CHAIN_ID`	NOT NULL	NUMERIC	Foreign Key	references `INVENTORY_JOB_CHAINS.ID`
NESTED_JOB_CHAIN_ID	NOT NULL	NUMERIC	Foreign Key	references `INVENTORY_JOB_CHAINS.ID`
`NAME`	NOT NULL	VARCHAR		job node name: `file_order_source` `job_chain_node` `file_order_sink`
`ORDERING`	NOT NULL	NUMERIC		ordering of node in job chain
`STATE`		VARCHAR		job node state as specified in the configuration
`NEXT_STATE`		VARCHAR		next state for an order in case of successful execution
`ERROR_STATE`		VARCHAR		error state for an order in case of unsuccessful execution
`JOB`		VARCHAR		job name as specified in the job node configuration e.g. `../my_job`
`JOB_NAME`	NOT NULL	VARCHAR		corresponds to `INVENTORY_JOBS.NAME.` Paths are resolved to absolute values starting from the `live` directory e.g. `/test/my_job`
NESTED_JOB_CHAIN		VARCHAR		job_chain attribute of the node
NESTED_JOB_CHAIN_NAME	NOT NULL	VARCHAR		corresponds to `INVENTORY_JOB_CHAINS.NAME`
NODE_TYPE	NOT NULL	NUMERIC		specifies the node type: `1: job` `2: job chain` `3: file order source` `4: file order sink` `5: end node`
ON_ERROR		VARCHAR		on_error attribute of a job_chain_node
DELAY		NUMERIC		delay attribute of a job_chain_node
DIRECTORY		VARCHAR		directory attribute of a file_order_source
REGEX		VARCHAR		regex attribute of a file_order_source
FILE_SINK_OP		NUMERIC		specifies the operation in case of file order sink : `1: move` `2: remove`
MOVE_PATH		VARCHAR		in case of file order sink and operation move this column will have the directory path `e.g. /data/input/archive`
`CREATED`	NOT NULL	DATETIME		UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		UTC timestamp of record modification

INVENTORY_JOB_CHAINS

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key		references `INVENTORY_INSTANCES.ID`
`FILE_ID`	NOT NULL	NUMERIC	Foreign Key	X	references `INVENTORY_FILES.ID`
PROCESS_CLASS_ID	NOT NULL	NUMERIC	Foreign Key		references `INVENTORY_PROCESS_CLASSES.ID`
FW_PROCESS_CLASS_ID	NOT NULL	NUMERIC	Foreign Key		references `INVENTORY_PROCESS_CLASSES.ID`
`START_CAUSE`	NOT NULL	VARCHAR			the following start causes are available: `file_trigger` `order`
`NAME`	NOT NULL	VARCHAR			job chain name e.g. `/test/my_jobchain` for a given job chain path`/test/my_jobchain.job_chain.xml`
`BASENAME`	NOT NULL	VARCHAR			job chain base name e.g. `my_jobchain` for a given job chain path `/test/my_jobchain.job_chain.xml`
`TITLE`	NULL	VARCHAR			job chain title
MAX_ORDERS		NUMERIC			max_orders allowed in the job chain, default is unlimited
DISTRIBUTED	NOT NULL	NUMERIC			specifies if job chain is configured for cluster operation : `1: yes` `0: no`
PROCESS_CLASS		VARCHAR			process_class attribute of job chain
PROCESS_CLASS_NAME	NOT NULL	VARCHR			corresponds to `INVENTORY_PROCESS_CLASSES.NAME`
FW_PROCESS_CLASS		VARCHAR			file_watching_process_class attribute of job chain
FW_PROCESS_CLASS_NAME	NOT NULL	VARCHAR			corresponds to `INVENTORY_PROCESS_CLASSES.NAME`
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

INVENTORY_JOBS

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key		references `INVENTORY_INSTANCES.ID`
`FILE_ID`	NOT NULL	NUMERIC	Foreign Key	X	references `INVENTORY_FILES.ID`
PROCESS_CLASS_ID	NOT NULL	NUMERIC	Foreign Key		references `INVENTORY_PROCESS_CLASSES.ID`
SCHEDULE_ID	NOT NULL	NUMERIC	Foreign Key		references `INVENTORY_SCHEDULES.ID`
`NAME`	NOT NULL	VARCHAR			job name e.g. `/test/my_job` for a given job path`/test/my_job.job.xml`
`BASENAME`	NOT NULL	VARCHAR			job base name e.g. `my_job` for a given job path `/test/my_job.job.xml`
`TITLE`	NULL	VARCHAR			job title
`IS_ORDER_JOB`	NOT NULL	NUMERIC			specifies if this job is part of a job chain: `1: yes` `0: no`
`IS_RUNTIME_DEFINED`	NOT NULL	NUMERIC			specifies if a start time has been configured: `1: yes` `0: no`
USED_IN_JOB_CHAINS		NUMERIC			count of how many job chain use this job
PROCESS_CLASS		VARCHAR			process_class attribute of job
PROCESS_CLASS_NAME	NOT NULL	VARCHAR			corresponds to `INVENTORY_PROCESS_CLASSES.NAME`
SCHEDULE		VARCHAR			schedule attribute of job's runtime
SCHEDULE_NAME	NOT NULL	VARCHAR			corresponds to `INVENTORY_SCHEDULES.NAME`
`MAX_TASKS`	NOT NULL	NUMERIC			max_task configured for the job, default is 1
`HAS_DESCRIPTION`		NUMERIC			indicates if a job has associated documentation e.g. JITL job documentation or custom documentation from user `1: yes` `0: no`
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

INVENTORY_LOCKS

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key		references `INVENTORY_INSTANCES.ID`
FILE_ID	NOT NULL	NUMERIC	Foreign Key	X	references `INVENTORY_FILES.ID`
NAME	NOT NULL	VARCHAR			lock file name
BASENAME	NOT NULL	VARCHAR			lock base name
MAX_NON_EXCLUSIVE		NUMERIC			if `max_non_exclusive` is null `max_non_exclusive` is set to unlimited
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

INVENTORY_OPERATING_SYSTEMS

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`HOSTNAME`	NOT NULL	VARCHAR		the host name where JobScheduler is installed
NAME		VARCHAR		name of the operating system `e.g. Windows, Linux, AIX, Solaris, other`
ARCHITECTURE		VARCHAR		x64/x32
DISTRIBUTION		VARCHAR		OS distribution name `e.g. Windows 2012, CentOS Linux release 7.2.1511 (Core)`
`CREATED`	NOT NULL	DATETIME		UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		UTC timestamp of record modification

INVENTORY_ORDERS

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key		references `INVENTORY_INSTANCES.ID`
`FILE_ID`	NOT NULL	NUMERIC	Foreign Key	X	references `INVENTORY_FILES.ID`
JOB_CHAIN_ID	NOT_NULL	NUMERIC	Foreign Key		references `INVENTORY_JOB_CHAINS.ID`
SCHEDULE_ID	NOT_NULL	NUMERIC	Foreign Key		references `INVENTORY_SCHEDULES.ID`
`NAME`	NOT NULL	VARCHAR			order name e.g. `/test/my_jobchain,my_order` for a given order path /`test/my_jobchain,my_order.order.xml`
`BASENAME`	NOT NULL	VARCHAR			order base name e.g. `my_jobchain,my_order` for a given job chain path `/test/my_jobchain,my_order.order.xml`
`TITLE`	NULL	VARCHAR			order title
`ORDER_ID`	NOT NULL	VARCHAR			order identification (unique per job chain) e.g. `my_order` for a given order path`/test/my_jobchain,my_order.order.xml`
`JOB_CHAIN_NAME`	NOT NULL	VARCHAR			corresponds to `INVENTORY_JOB_CHAINS.NAME` e.g. `/test/my_jobchain`
SCHEDULE		VARCHAR			schedule attribute of order's runtime
SCHEDULE_NAME	NOT_NULL	VARCHAR			corresponds to `INVENTORY_SCHEDULES.NAME`
INITIAL_STATE		VARCHAR			the name of the start node
END_STATE		VARCHAR			the name of the end node
PRIORITY		NUMERIC			priority of the order
`IS_RUNTIME_DEFINED`	NOT NULL	NUMERIC			specifies if a start time has been configured: `1: yes` `0: no`
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

INVENTORY_PROCESS_CLASSES

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key	X	references `INVENTORY_INSTANCES.ID`
FILE_ID	NOT NULL	NUMERIC	Foreign Key	X	references `INVENTORY_FILES.ID`
NAME	NOT NULL	VARCHAR			process class file name
BASENAME	NOT NULL	VARCHAR			process class base name
MAX_PROCESSES		NUMERIC			max parallel execution of the task
HAS_AGENTS	NOT NULL	NUMERIC			specifies if an agent has been configured in the process class: 0: no `1: yes`
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

INVENTORY_SCHEDULES

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key		references `INVENTORY_INSTANCES.ID`
FILE_ID	NOT NULL	NUMERIC	Foreign Key	X	references `INVENTORY_FILES.ID`
SUBSTITUTE_ID	NOT NULL	NUMERIC	Foreign Key		references `INVENTORY_SCHEDULES.ID`
NAME	NOT NULL	VARCHAR			schedule file name
BASENAME	NOT NULL	VARCHAR			schedule base name
TITLE		VARCHAR			schedule's title
SUBSTITUTE		NUMERIC			substitute attribute
SUBSTITUTE_NAME	NOT NULL	NUMERIC			full path of the substituted schedule, references `INVENTORY_SCHEDULES.NAME`
SUBSTITUTE_VALID_FROM		DATETIME			timestamp "from" when SUBSTITUTE will be valid
SUBSTITUTE_VALID_TO		DATETIME			timestamp "to" until SUBSTITUTE will be valid
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

Audit_log Table

the following table is used to store information if a user takes actions with a specific Job Scheduler Object, e.g. starting/stopping of a JobScheduler instance, pausing a Job, modifying an order, etc..
This table can be used to create individual reports on user interaction as well as security related actions as login/logout.

AUDIT_LOG

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`SCHEDULER_ID`	NOT NULL	VARCHAR		X	corresponds to the `SCHEDULER_ID` that is specified during installation of the JobScheduler instance
`ACCOUNT`	NOT NULL	VARCHAR			user account the audit was stored for
`REQUEST`	NOT NULL	VARCHAR			REST API request the audit was stored for
`PARAMETERS`		LONGTEXT			given parameters from the request
`JOB`		VARCHAR			the job that was started, modified, etc. references `INVENTORY_JOBS.NAME`
`JOB_CHAIN`		VARCHAR			the job chain that was started, modified, etc. references `INVENTORY_JOB_CHAINS.NAME`
`ORDER_ID`		VARCHAR			the order that was started, modified, etc. references `INVENTORY_ORDERS.ORDER_ID`
`FOLDER`		VARCHAR			the path of the folder where the job, job chain, etc. resides
`COMMENT`		VARCHAR			a user comment on the action taken
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`TICKET_LINK`		VARCHAR			the link to a ticket, issue, bugtracker, etc. describing the problem the action was taken for
`TIME_SPENT`		NUMERIC			time spent on the action taken

Fact Tables

The following tables are used to collect facts from the JobScheduler history.
These tables can be used to create individual reports.
SQL Scripts: reporting.sql

SQL scripts in this article are provided for PostgreSQL. The SQL scrpts for the respective DBMS are provided by the JobScheduler installer.

REPORTING_TRIGGERS

Contains the informations about executed job chains and orders.

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`SCHEDULER_ID`	NOT NULL	VARCHAR		X	corresponds to `SCHEDULER_ORDER_HISTORY.SPOOLER_ID`
`HISTORY_ID`	NOT NULL	NUMERIC		X	corresponds to `SCHEDULER_ORDER_HISTORY.HISTORY_ID`
`NAME`	NOT NULL	VARCHAR			corresponds to `SCHEDULER_ORDER_HISTORY.ORDER_ID` e.g. `my_order` for a given order path `/test/my_jobchain,my_order.order.xml`
`TITLE`		VARCHAR			order title corresponds to `INVENTORY_JOB_CHAIN_ORDERS.TITLE`
PARENT_FOLDER	NOT NULL	VARCHAR			job chain folder e.g. `/test` for a given job chain path `/test/my_jobchain.job_chain.xml`
`PARENT_NAME`	NOT NULL	VARCHAR			job chain name corresponds to `SCHEDULER_ORDER_HISTORY.JOB_CHAIN` e.g. `/test/my_jobchain` for a given job chain path `/test/my_jobchain.job_chain.xml`
`PARENT_BASENAME`		VARCHAR			job chain base name e.g. `my_jobchain` for a given job chain path `/test/my_jobchain.job_chain.xml`
`PARENT_TITLE`		VARCHAR			job chain title corresponds to `INVENTORY_JOB_CHAINS.TITLE`
STATE		VARCHAR			state of the order inside the job chain corresponds to `SCHEDULER_ORDER_HISTORY.STATE`
STATE_TEXT		VARCHAR			state text of the order corresponds to `SCHEDULER_ORDER_HISTORY.STATE_TEXT`
`START_TIME`	NOT NULL	DATETIME			UTC timestamp of the start of the order corresponds to `SCHEDULER_ORDER_HISTORY.START_TIME`
`END_TIME`		DATETIME			UTC timestamp of the end of the order corresponds to `SCHEDULER_ORDER_HISTORY.END_TIME`
IS_RUNTIME_DEFINED	NOT NULL	NUMERIC			specifies if a start time has been configured: `1: yes` `0: no` corresponds to `INVENTORY_ORDERS.IS_RUNTIME_DEFINED`
RESULT_START_CAUSE	NOT NULL	VARCHAR			cause of the order start order file_trigger ... corresponds to `INVENTORY_JOB_CHAINS.START_CAUSE`
RESULT_STEPS	NOT NULL	NUMERIC			number of executed order steps
RESULT_ERROR	NOT NULL	NUMERIC			specifies if an error occurred in the last executed order step: `0: no error` `1: error`
RESULT_ERROR_CODE		VARCHAR			exception-code of the last executed order step error
RESULT_ERROR_TEXT		VARCHAR			exception-message of the last executed order step error
`SYNC_COMPLETED`	NOT NULL	NUMERIC			for use by the JobScheduler Reporting Interface only: specifies if a synchronization has been completed: `1: yes` `0: no`
`RESULTS_COMPLETED`	NOT NULL	NUMERIC			for use by the JobScheduler Reporting Interface only: specifies if a aggregation has been completed: `1: yes` `0: no`
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

REPORTING_EXECUTIONS

Contains the informations about executed order steps.

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`SCHEDULER_ID`	NOT NULL	NUMERIC		X	corresponds to `SCHEDULER_ORDER_HISTORY.SPOOLER_ID`
`HISTORY_ID`	NOT NULL	NUMERIC		X	corresponds to `SCHEDULER_ORDER_HISTORY.HISTORY_ID`
`TRIGGER_ID`	NOT NULL	NUMERIC	Foreign Key	X	references `REPORT_TRIGGERS.ID`
TASK_ID	NOT NULL	NUMERIC	Foreign Key		references `REPORT_TASKS.ID`
CLUSTER_MEMBER_ID		VARCHAR			corresponds to `SCHEDULER_HISTORY.CLUSTER_MEMBER_ID`
`STEP`	NOT NULL	NUMERIC		X	consecutive number of the order step corresponds to `SCHEDULER_ORDER_STEP_HISTORY.STEP`
FOLDER	NOT NULL	VARCHAR			folder of the order step e.g. `/test` for a given job chain path `/test/my_jobchain.job_chain.xml`
`NAME`	NOT NULL	VARCHAR			job name of the order step corresponds to `SCHEDULER_HISTORY.JOB_NAME` e.g. `/test/my_job`
`BASENAME`	NOT NULL	VARCHAR			job base name of the order step e.g. `my_job`, for a given job path `/test/my_job.job.xml`
`TITLE`	NULL	VARCHAR			job title of the order step corresponds to `INVENTORY_JOBS.TITLE`
`START_TIME`	NOT NULL	DATETIME			UTC timestamp of the start of the order step corresponds to `SCHEDULER_ORDER_STEP_HISTORY.START_TIME`
`END_TIME`	NULL	DATETIME			UTC timestamp of the end of the order step corresponds to `SCHEDULER_ORDER_STEP_HISTORY.END_TIME`
`STATE`	NOT NULL	VARCHAR			state of the order inside the job chain corresponds to `SCHEDULER_ORDER_STEP_HISTORY.STATE`
`CAUSE`	NOT NULL	VARCHAR			start cause for execution, can be mapped to an individual start cause by use of table `REPORTING_MAP_CAUSES` corresponds to `SCHEDULER_HISTORY.CAUSE`
EXIT_CODE	NOT NULL	NUMERIC			corresponds to `SCHEDULER_HISTORY.EXIT_CODE`
`ERROR`	NOT NULL	NUMERIC			specifies if an error occurred: `0: no error` `1: error` corresponds to `SCHEDULER_ORDER_STEP_HISTORY.ERROR`
`ERROR_CODE`	NULL	VARCHAR			exception-code of the order step error corresponds to `SCHEDULER_ORDER_STEP_HISTORY.ERROR_CODE`
`ERROR_TEXT`	NULL	VARCHAR			exception-message of the order step error corresponds to `SCHEDULER_ORDER_STEP_HISTORY.ERROR_TEXT`
AGENT_URL		VARCHAR			agent url corresponds to `SCHEDULER_HISTORY.AGENT_URL`
`IS_RUNTIME_DEFINED`	NOT NULL	NUMERIC			specifies if a start time has been configured: `1: yes` `0: no` corresponds to `INVENTORY_JOBS.IS_RUNTIME_DEFINED`
SYNC_COMPLETED	NOT NULL	NUMERIC			for use by the JobScheduler Reporting Interface only: specifies if a synchronization has been completed: `1: yes` `0: no`
RESULTS_COMPLETED	NOT NULL	NUMERIC			for use by the JobScheduler Reporting Interface only: specifies if a aggregation has been completed: `1: yes` `0: no`
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

REPORTING_TASKS

Contains the informations about executed job tasks.

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`SCHEDULER_ID`	NOT NULL	NUMERIC		X	corresponds to `SCHEDULER_HISTORY.SPOOLER_ID`
`HISTORY_ID`	NOT NULL	NUMERIC		X	corresponds to `SCHEDULER_HISTORY.ID`
IS_ORDER	NOT NULL	NUMERIC			cause for execution of the task: `1: order` task `0: standalone` task
CLUSTER_MEMBER_ID		VARCHAR			corresponds to `SCHEDULER_HISTORY.CLUSTER_MEMBER_ID`
`STEPS`	NOT NULL	NUMERIC			number of steps --> calls to spooler_process() corresponds to `SCHEDULER_HISTORY.STEPS`
FOLDER	NOT NULL	VARCHAR			job folder e.g. `/test`, for a given job path `/test/my_job.job.xml`
`NAME`	NOT NULL	VARCHAR			job name corresponds to `SCHEDULER_HISTORY.JOB_NAME` e.g. `/test/my_job`, for a given job path `/test/my_job.job.xml`
`BASENAME`	NOT NULL	VARCHAR			job base name e.g. `my_job`, for a given job path `/test/my_job.job.xml`
`TITLE`	NULL	VARCHAR			job title corresponds to `INVENTORY_JOBS.TITLE`
`START_TIME`	NOT NULL	DATETIME			UTC timestamp of the task start corresponds to `SCHEDULER_HISTORY.START_TIME`
`END_TIME`	NULL	DATETIME			UTC timestamp of the task end corresponds to `SCHEDULER_HISTORY.END_TIME`
`CAUSE`	NOT NULL	VARCHAR			cause of the task start e.g. : none period_once period_single period_repeat job_repeat queue queue_at directory signal delay_after_error corresponds to `SCHEDULER_HISTORY.CAUSE`
EXIT_CODE	NOT NULL	NUMERIC			corresponds to `SCHEDULER_HISTORY.EXIT_CODE`
`ERROR`	NOT NULL	NUMERIC			specifies if an error occurred: `0: no error` `1: error`
`ERROR_CODE`	NULL	VARCHAR			exception-code of the task error
`ERROR_TEXT`	NULL	VARCHAR			exception-message of the task error
AGENT_URL		VARCHAR			agen url corresponds to `SCHEDULER_HISTORY.AGENT_URL`
`IS_RUNTIME_DEFINED`	NOT NULL	NUMERIC			specifies if a start time has been configured: `1: yes` `0: no` corresponds to `INVENTORY_JOBS.IS_RUNTIME_DEFINED`
SYNC_COMPLETED	NOT NULL	NUMERIC			for use by the JobScheduler Reporting Interface only: specifies if a synchronization has been completed: `1: yes` `0: no`
RESULTS_COMPLETED	NOT NULL	NUMERIC			for use by the JobScheduler Reporting Interface only: specifies if a aggregation has been completed: `1: yes` `0: no`
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

Aggregation Tables

The following tables contain aggregations for

triggers, executions and tasks per day, week, month, quarter, year

REPORTING_EXECUTION_DATES

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`SCHEDULER_ID`	NOT NULL	VARCHAR		corresponds to `SCHEDULER_ORDER_HISTORY.SCHEDULER_ID`
`HISTORY_ID`	NOT NULL	NUMERIC		corresponds to `SCHEDULER_ORDER_HISTORY.HISTORY_ID` (`triggers, executions`) or `SCHEDULER_HISTORY.ID` (`tasks`)
`REFERENCE_ID`	NOT NULL	NUMERIC	Foreign Key	references `REPORTING_TRIGGERS.ID` or `REPORTING_EXECUTIONS.ID` or `REPORTING_TASKS.ID`
`REFERENCE_TYPE`	NOT NULL	NUMERIC		type of reference: `0:` references `REPORTING_TRIGGERS` `1:` references `REPORTING_EXECUTIONS` `2:` references `REPORTING_TASKS`
`START_DAY`	NOT NULL	NUMERIC		calendar day e.g. 1
`START_WEEK`	NOT NULL	NUMERIC		calendar week e.g. 52
`START_MONTH`	NOT NULL	NUMERIC		calendar month e.g. 12
`START_QUARTER`	NOT NULL	NUMERIC		quarter e.g. 1
`START_YEAR`	NOT NULL	NUMERIC		year e.g. 2015
`END_DAY`	NOT NULL	NUMERIC		calendar day e.g. 1
`END_WEEK`	NOT NULL	NUMERIC		calendar week e.g. 52
`END_MONTH`	NOT NULL	NUMERIC		calendar month e.g. 12
`END_QUARTER`	NOT NULL	NUMERIC		quarter e.g. 1
`END_YEAR`	NOT NULL	NUMERIC		calendar year e.g. 2015
`CREATED`	NOT NULL	DATETIME		UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		UTC timestamp of record modification

Mapping Tables

The following tables are used for the mapping of report data. Users can add individual records that are used for the mapping of results in individual reports:
- Mapping of start causes for jobs
Contents in these tables is optional, however, their existence is required.
SQL Scripts

Sample Data: reporting_map_insert.sql
- SQL scripts in this article are provided for PostgreSQL. The SQL scrpts for the respective DBMS are provided by the JobScheduler installer.

REPORTING_MAP_CAUSES

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`CAUSE`	NOT NULL	VARCHAR		start cause as used by JobScheduler: possible values include `order`: job start triggered by manual order `min_tasks`: job start triggered by forced minimum number of tasks `delay_after_error`: job start due to a setback event `period_single`: job start triggered by order start time `period_repeat`: job start triggered by order repeat interval `queue_at`: job start scheduled manually or by task congestion additional values provided by the JobScheduler Reporting Interface include `file_trigger`: job start due to an incoming file
`MAPPED_CAUSE`	NOT NULL	VARCHAR		individual mapping of start cause
`CREATED`	NOT NULL	DATETIME		UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		UTC timestamp of record modification

Standard Procedures

Cleanup

SQL Scripts: reporting_cleanup_procedure.sql
- SQL scripts in this article are provided for PostgreSQL. The SQL scrpts for the respective DBMS are provided by the JobScheduler installer.

Cleanup all entries

cleanup all entries (reporting, dailyplan, yade)

SELECT REPORT_CLEANUP('all',0);
- following tables will be truncated:
  - REPORTING_TRIGGERS
  - REPORTING_EXECUTIONS
  - REPORTING_TASKS
  - REPORTING_EXECUTION_DATES
  - DAILY_PLAN
  - JADE_FILES
  - JADE_FILES_HISTORY
  - JADE_FILES_POSITIONS

cleanup only reporting entries
- SELECT REPORT_CLEANUP('reporting',0);
  - following tables will be truncated:
    - REPORTING_TRIGGERS
    - REPORTING_EXECUTIONS
    - REPORTING_TASKS
    - REPORTING_EXECUTION_DATES
cleanup only dailyplan entries
- SELECT REPORT_CLEANUP('dailyplan',0);
  - following tables will be truncated
    - DAILY_PLAN
cleanup only yade entries
- SELECT REPORT_CLEANUP('yade',0);
  - following tables will be truncated:
    - JADE_FILES
    - JADE_FILES_HISTORY
    - JADE_FILES_POSITIONS

Cleanup entries older as n days

Example - cleanup entries older as 14 days

cleanup all entries (reporting, dailyplan, yade)
- SELECT REPORT_CLEANUP('all',14);
cleanup reporting entries
- SELECT REPORT_CLEANUP('reporting',14);
cleanup reporting entries
- SELECT REPORT_CLEANUP('reporting',14);
cleanup reporting entries
- SELECT REPORT_CLEANUP('reporting',14)

Report

For most DBMS it is recommended to use views or procedures in order to speed up queries by pre-compiled statements.
The following procedures implement the use of the standard tables for reporting results.
SQL Scripts: reporting_procedure.sql
- SQL scripts in this article are provided for PostgreSQL. The SQL scrpts for the respective DBMS are provided by the JobScheduler installer.

Report about Installed Objects

Report about jobs, job chains and orders as collected from disk.

SELECT <fileds> FROM REPORT_INSTALLED_JOB_OBJECTS('2017-05-01','2017-06-01');
- following fields can be selected:
  - ID
  - SCHEDULER_ID
  - HOSTNAME
  - JOB_CHAIN
  - JOB_CHAIN_BASENAME
  - JOB_CHAIN_TITLE
  - JOB_NAME
  - JOB_BASENAME
  - JOB_TITLE
  - CAUSE
  - IS_ACTIVE_CALENDAR
  - IS_ORDER_JOB
  - FILE_CREATED
  - FILE_MODIFIED
SELECT <fields> FROM REPORT_INSTALLED_ORDER_OBJECTS('2017-05-01','2014-06-01');

following fields can be selected:

ID
SCHEDULER_ID
HOSTNAME
JOB_CHAIN
JOB_CHAIN_BASENAME
JOB_CHAIN_TITLE
ORDER_ID
ORDER_NAME
ORDER_BASENAME
ORDER_TITLE
CAUSE
IS_ACTIVE_CALENDAR
FILE_CREATED
FILE_MODIFIED

Report about Execution Summary

Report about executions of order jobs and job chains.

SELECT <fields> FROM REPORT_EXECUTION_SUMMARY('2017-05-01','2017-06-01');

following fields can be selected:
- ID
- SCHEDULER_ID
- HOSTNAME
- AGENT_URL
- JOB_CHAIN
- JOB_CHAIN_BASENAME
- JOB_CHAIN_TITLE
- JOB_NAME
- JOB_BASENAME
- JOB_TITLE
- CAUSE
- IS_ACTIVE_CALENDAR
- START_TIME
- END_TIME
- START_DATE
- END_DATE

Space shortcuts

Page tree

Scope

Standard Data Model

Standard Tables

Inventory Tables

Audit_log Table

Fact Tables

Aggregation Tables

Mapping Tables

Standard Procedures

Cleanup

Cleanup all entries

Cleanup entries older as n days

Report

Report about Installed Objects

Report about Execution Summary

Space shortcuts

Page tree

JobScheduler Reporting Interface (1.11.x) - Technical Information - Standard Data Model

Scope

Standard Data Model

Standard Tables

Inventory Tables

Audit_log Table

Fact Tables

Aggregation Tables

Mapping Tables

Standard Procedures

Cleanup

Cleanup all entries

Cleanup entries older as n days

Report

Report about Installed Objects

Report about Execution Summary