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.
...
- 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.
...
| Expand |
|---|
| title | 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 |
|
| Expand |
|---|
| title | 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 Schedulingnext Round Robin Schedulingsingle
|
| NUMBER_OF_AGENTS | NOT NULL | NUMERIC |
| | UTC timestamp | | | UTC timestamp of record creation | MODIFIED | NOT NULL | DATETIME |
| |
|
| UTC timestamp of record modification |
|
| Expand |
|---|
| title | 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 |
|
| Expand |
|---|
|
| 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: file types not used: process_classconfiglockschedule...
| 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 |
|
| Expand |
|---|
|
| 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 |
| TCP HTTP port that the JobScheduler instance is listening to. |
Should not TCP port be specified then the UDP port is provided.LIVE_DIRECTORY |
|
| 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 |
| NUMERIC | | | |
| 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 |
| | |
|
| release number of database system | STARTED_AT |
| | | | |
|
| reference INVENTORY_INSTANCES.ID | AUTH |
AUTH | | | CREATED | NOT NULL | DATETIME | | |
|
| UTC timestamp of record creation | MODIFIED | NOT NULL | DATETIME |
| |
|
| UTC timestamp of record modification |
|
| Expand |
|---|
| title | INVENTORY_JOB_CHAIN_NODES |
|---|
|
| 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 | 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_sourcejob_chain_nodefile_order_sink
| ORDERING | NOT NULL | NUMERIC |
| |
|
| ordering of node in job chain | STATE
|
| |
|
| job node state as specified in the configuration | NEXT_STATE |
| | |
|
| next state for an order in case of successful execution | ERROR_STATE |
| | |
|
| error state for an order in case of unsuccessful execution | JOB |
| | |
|
| 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 |
| | |
|
| 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: job2: job chain3: file order source4: file order sink5: end node
|
| VARCHAR | | |
| VARCHAR |
|
| on_error attribute of a job_chain_node |
| | | | delay attribute of a job_chain_node | DIRECTORY |
| | | directory attribute of a file_order_source | REGEX |
| | | regex attribute of a file_order_source | FILE_SINK_OP |
| |
|
| specifies the operation in case of file order sink : |
| | |
|
| in case of file order sink and operation move this column will have the directory path e.g. /data/input/archive
|
| |
|
| UTC timestamp of record creation | MODIFIED | NOT NULL | DATETIME |
| |
|
| UTC timestamp of record modification |
|
| Expand |
|---|
| title | 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 |
the following |
| references INVENTORY_PROCESS_CLASSES.ID | START_CAUSE | NOT NULL | VARCHAR |
| | the following start causes are available: | 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 |
| | | |
|
| max_orders allowed in the job chain, default is unlimited | DISTRIBUTED | NOT NULL | NUMERIC |
| |
|
| specifies if job chain is configured for cluster operation : |
| | |
|
| process_class attribute of job chain | PROCESS_CLASS_NAME | NOT NULL | VARCHR |
| |
|
| corresponds to INVENTORY_PROCESS_CLASSES.NAME | FW_PROCESS_CLASS |
| | | | FW_PROCESS_
|
| 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 |
|
| Expand |
|---|
|
| 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: | IS_RUNTIME_DEFINED
|
| |
|
| specifies if a start time has been configured: | USED_IN_JOB_CHAINS |
| | | |
|
| count of how many job chain use this job | PROCESS_CLASS |
| | | |
|
| process_class attribute of job | PROCESS_CLASS_NAME | NOT NULL | VARCHAR |
| |
|
| corresponds to INVENTORY_PROCESS_CLASSES.NAME | SCHEDULE |
| | | |
|
| 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 | CREATED | NOT NULL | DATETIME |
| |
|
| UTC timestamp of record creation | MODIFIED | NOT NULL | DATETIME |
| |
|
| UTC timestamp of record modification |
|
| Expand |
|---|
|
| 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 | | | |
| 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 |
|
| Expand |
|---|
| title | INVENTORY_OPERATING_SYSTEMS |
|---|
|
| Field | Nullable | Data type | Constraint | Unique Constraint | Description |
|---|
ID | NOT NULL | NUMERIC | Primary Key |
| | |
|
| the host name where JobScheduler is installed | NAME |
| | |
| ARCHITECTURE | | VARCHAR | | | | DISTRIBUTION | | 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)
|
| | | |
|
| UTC timestamp of record creation | MODIFIED | NOT NULL | DATETIME |
| |
|
| UTC timestamp of record modification |
|
| Expand |
|---|
|
| Field | Nullable | Data type | Constraint | Unique Constraint | Description |
|---|
ID | NOT NULL | NUMERIC | Primary Key |
| |
|
| INSTANCE_ID | NOT NULL | NUMERIC | Foreign Key |
|
| references | 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
|
| corresponds to INVENTORY_JOB_CHAINS.NAME e.g. /test/my_jobchain | SCHEDULE |
| | | |
|
| schedule attribute of order's runtime | SCHEDULE_NAME | NOT_NULL | VARCHAR |
| |
|
| corresponds to INVENTORY_SCHEDULES.NAME | INITIAL_STATE |
| | |
|
| the name of the start node | END_STATE |
| | |
|
| the name of the end node | PRIORITY |
| | | | priority of the order | IS_RUNTIME_DEFINED
|
| |
|
| specifies if a start time has been configured: | CREATED | NOT NULL | DATETIME |
|
|
| UTC timestamp of record creation | MODIFIED | NOT NULL | DATETIME |
| |
|
| UTC timestamp of record modification |
|
| Expand |
|---|
| title | INVENTORY_PROCESS_CLASSES |
|---|
|
| Field | Nullable | Data type | Constraint | Unique Constraint | Description |
|---|
ID | NOT NULL | NUMERIC | Primary Key |
| | BASENAME |
|
| 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 |
| |
|
| process class base name | MAX_PROCESSES |
| | |
|
| max parallel execution of the task | HAS_AGENTS | NOT NULL | NUMERIC |
| |
|
| specifies if an agent has been configured in the process class: |
| |
|
| UTC timestamp of record creation | MODIFIED | NOT NULL | DATETIME |
| |
|
| UTC timestamp of record modification |
|
| Expand |
|---|
|
| 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 |
| TITLE | | VARCHAR | | | | | | |
|
| substitute attribute | SUBSTITUTE_NAME | NOT NULL | NUMERIC |
| | |
|
| full path of the substituted schedule, references INVENTORY_SCHEDULES.NAME | SUBSTITUTE_VALID_FROM |
| | | | timestamp "from" when SUBSTITUTE will be valid | SUBSTITUTE_VALID_TO |
| | |
|
| 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 |
|
Fact Tables
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
- The following tables are used to collect facts from the JobScheduler history.
- These tables can be used to create individual reports on user interaction as well as security related actions as login/logout.
- 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.
| Expand |
|---|
|
| Expand |
|---|
|
| Field | Nullable | Data type | Constraint | Unique |
|---|
Constraint| Constraint | Description |
|---|
ID | NOT NULL | NUMERIC | Primary Key |
| |
|
| SCHEDULER_ID | NOT NULL | VARCHAR |
ORDER_HISTORY.SPOOLER_IDID that is specified during installation of the JobScheduler instance | ACCOUNT |
HISTORY_IDNUMERIC | | X | corresponds to SCHEDULER_ORDER_HISTORY.HISTORY_ID
| | 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.
| Expand |
|---|
|
Contains the informations about executed job chains and orders. 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 | 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. |
STATESTATETEXT VARCHAR | | | state text of the order | corresponds to SCHEDULER_ORDER_HISTORY. |
STATETEXT START_TIMEUTC timestamp of the start of the order DATETIME | | | corresponds to SCHEDULER_ORDER_HISTORY. |
STARTTIMEEND_TIME | | DATETIME | | | ID
e.g. my_order for a given order path /test/my_jobchain,my_order.order.xml | TITLE |
| VARCHAR |
|
| order title |
UTC timestamp of the end of the orderSCHEDULERORDERHISTORYEND_TIMEISRUNTIME_DEFINEDNUMERIC | | specifies if a start time has been configured: corresponds to INVENTORY_ORDERS.IS_RUNTIME_DEFINED |
|
| job chain folder e.g. /test for a given job chain path /test/my_jobchain.job_chain.xml | PARENT_NAME |
RESULT_START_CAUSE | 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 |
cause of the order start orderfile_trigger... corresponds to INVENTORY_JOB_CHAINS. |
START_CAUSERESULT_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: | RESULT_ERROR_CODE | | VARCHAR | | | 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: corresponds to INVENTORY_ORDERS.IS_RUNTIME_DEFINED | RESULT_START_CAUSE | NOT NULL | VARCHAR |
|
| cause of the order start 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: | RESULT_ERROR_CODE |
| VARCHAR |
|
| exception |
exception| -code of the last executed order step error | RESULT_ERROR_TEXT |
| | for
|
| 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: | RESULTS_COMPLETED | NOT NULL | NUMERIC |
| |
|
| for use by the JobScheduler Reporting Interface only:
specifies if a aggregation has been completed: | CREATED | NOT NULL | DATETIME |
| |
|
| UTC timestamp of record creation | MODIFIED | NOT NULL | DATETIME |
| |
|
| UTC timestamp of record modification |
|
| Expand |
|---|
| title | 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 |
|
|
| 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 |
| exception
|
| specifies if an error occurred: 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 |
| |
|
| agent url corresponds to SCHEDULER_HISTORY.AGENT_URL | IS_RUNTIME_DEFINED | NOT NULL | NUMERIC |
| |
|
| specifies if a start time has been configured: 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: | RESULTS_COMPLETED | NOT NULL | NUMERIC |
| |
|
| for use by the JobScheduler Reporting Interface only:
specifies if a aggregation has been completed: | CREATED | NOT NULL | DATETIME |
| |
|
| UTC timestamp of record creation | MODIFIED | NOT NULL | DATETIME |
| |
|
| UTC timestamp of record modification |
|
| Expand |
|---|
|
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 task0: standalone task
| CLUSTER_MEMBER_ID |
| | |
|
| 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: | ERROR_CODE | NULL | VARCHAR |
| |
|
| exception-code of the task error | ERROR_TEXT | NULL | VARCHAR |
| |
|
| exception-message of the task error | AGENT_URL |
| |
|
| agen url corresponds to SCHEDULER_HISTORY.AGENT_URL | IS_RUNTIME_DEFINED | NOT NULL | NUMERIC |
| |
|
| specifies if a start time has been configured: 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: | RESULTS_COMPLETED | NOT NULL | NUMERIC |
|
|
| for use by the JobScheduler Reporting Interface only:
specifies if a aggregation has been completed: | 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
| Expand |
|---|
| title | 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 |
corresponds to SCHEDULERSCHEDULER_ORDER_HISTORY.HISTORY_ID (triggers, executions) orSCHEDULER_HISTORY.ID (tasks)
| REFERENCE_ID | NOT NULL | NUMERIC | Foreign Key | references REPORTING_TRIGGERS.ID or -
REPORTING_EXECUTIONS.ID or
|
REPORTIN | REFERENCE_TYPE | NOT NULL | NUMERIC |
|
| type of reference: 0: references REPORTING_TRIGGERS1: references REPORTING_EXECUTIONS2: 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.
| Expand |
|---|
| title | REPORTING_MAP_CAUSES |
|---|
|
| Field | Nullable | Data type | Constraint | Description |
|---|
ID | NOT NULL | NUMERIC | Primary Key |
| |
| start cause as used by JobScheduler: possible values include order: job start triggered by manual ordermin_tasks: job start triggered by forced minimum number of tasksdelay_after_error: job start due to a setback eventperiod_single: job start triggered by order start timeperiod_repeat: job start triggered by order repeat intervalqueue_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
...
Report about Installed Objects
Report about jobs, job chains and orders as collected from disk.
Report about Execution Summary
- Report about executions of jobs and job chains.
...
SELECT <fileds> FROM REPORT_INSTALLED_JOB_OBJECTS('2017-05-01','2017-06-01'); - following fields can be selected:
IDSCHEDULER_IDHOSTNAMEJOB_CHAINJOB_CHAIN_BASENAMEJOB_CHAIN_TITLEJOB_NAMEJOB_BASENAMEJOB_TITLECAUSEIS_ACTIVE_CALENDARIS_ORDER_JOBFILE_CREATEDFILE_MODIFIED
SELECT <fields> FROM REPORT_INSTALLED_ORDER_OBJECTS('2017-05-01','2014-06-01');- following fields can be selected:
IDSCHEDULER_IDHOSTNAMEJOB_CHAINJOB_CHAIN_BASENAMEJOB_CHAIN_TITLEORDER_IDORDER_NAMEORDER_BASENAMEORDER_TITLECAUSEIS_ACTIVE_CALENDARFILE_CREATEDFILE_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:
IDSCHEDULER_IDHOSTNAMEAGENT_URLJOB_CHAINJOB_CHAIN_BASENAMEJOB_CHAIN_TITLEJOB_NAMEJOB_BASENAMEJOB_TITLECAUSEIS_ACTIVE_CALENDARSTART_TIMEEND_TIMESTART_DATEEND_DATE