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 ScriptsTables: 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 |
|
| 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 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
|
| 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 | 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 |
| | | 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 |
| | | DBMS_VERSION | | VARCHAR | | |
|
| name of the database vendor e.g. MySql , Oracle, etc | DBMS_VERSION |
| VARCHAR |
|
| release number of database system |
| | | | | | reference INVENTORY_INSTANCES.ID | AUTH |
| | | | |
|
| 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 |
| VARCHAR | | |
| VARCHAR |
|
| 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
|
| | | | DELAY | | NUMERIC | | | | DIRECTORY | | VARCHAR | | | | REGEX | | 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 |
| | | |
|
| 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
| CREATED | NOT NULL | DATETIME |
|
|
| 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 |
|
| 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 |
DISTRIBUTED | | | |
|
| max_orders allowed in the job chain, default is unlimited | DISTRIBUTED |
| |
|
| 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 |
| | | |
|
| 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 |
CREATEDDATETIME | | | 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 |
| | | |
|
| 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 |
SCHEDULERSCHEDULE_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 |
| | |
|
| 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 |
|
Fact Tables
- The following tables are used to collect facts from the JobScheduler history.
- No standalone jobs are given. Fact tables contain job chain starts exclusively.
- These tables can be used to create individual reports.
- SQL Scripts
| Expand |
|---|
| title | INVENTORY_PROCESS_CLASSES |
|---|
|
| Expand |
|---|
|
| Field | Nullable | | Data type | Constraint | Unique Constraint | Description |
|---|
ID | NOT NULL | NUMERIC | Primary Key |
| SCHEDULERVARCHAR | | corresponds to SCHEDULER_ORDER_HISTORY.SCHEDULER_ID
| HISTORY| NUMERIC | Foreign Key | X | references INVENTORY_INSTANCES.ID | FILE_ID | NOT NULL | NUMERIC | Foreign Key |
| | X | references INVENTORY_FILES. |
corresponds to SCHEDULER_ORDER_HISTORY.HISTORY_ | corresponds to SCHEDULER_ORDER_HISTORY.ORDER_ID | TITLE |
|
| process class file name | BASENAME | NOT NULL | VARCHAR |
| order title corresponds to INVENTORY_JOB_CHAIN_ORDERS.TITLE | PARENT_NAME
|
| process class base name | MAX_PROCESSES |
| NUMERIC |
|
| max parallel execution of the task | HAS_AGENTS | NOT NULL |
VARCHAR | | corresponds to SCHEDULER_ORDER_HISTORY.JOB_CHAIN | PARENT_BASENAME | NULL | VARCHAR | | job chain base name | PARENT_TITLE | NULL | VARCHAR | | corresponds to INVENTORY_JOB_CHAINS.TITLE | START_TIME | NOT NULL | DATETIME | | corresponds to SCHEDULER_ORDER_HISTORY.START_TIME | END_TIME | NULL | DATETIME | | corresponds to SCHEDULER_ORDER_HISTORY.END_TIME | | NUMERIC |
|
| specifies if an agent has been configured in the process class: | 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 |
SYNC_COMPLETED | for use by the JobScheduler Reporting Interface jobs only:
specifies if a synchronization has been configured and has been completed successfully: | | Foreign Key | X | references INVENTORY_FILES.ID | SUBSTITUTE_ID |
RESULTS_COMPLETED | for use by the JobScheduler Reporting Interface jobs only:
specifies if all job nodes have been completed successfully: | CREATED| Foreign Key |
| references INVENTORY_SCHEDULES.ID | NAME | NOT NULL |
DATETIME | timestamp of record creation | MODIFIED |
|
| schedule file name | BASENAME | NOT NULL |
DATETIME | timestamp of record modification | |
| Expand |
|---|
|
|
| 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.
| Expand |
|---|
|
| 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 |
| Field | Nullable | Data type | Constraint | Description |
|---|
ID | NOT NULL | NUMERIC | Primary Key | | SCHEDULER_ID | NOT NULL | NUMERIC | | corresponds to SCHEDULER_ORDER_HISTORY.SCHEDULER_ID | HISTORY_ID | NOT NULL | NUMERIC | | corresponds to SCHEDULER_ORDER_HISTORY.HISTORY_ID | TRIGGER_ID | NOT NULL | NUMERIC | Foreign Key | references REPORT_TRIGGERS.ID | STEP | NOT NULL | NUMERIC | | corresponds to SCHEDULER_ORDER_STEP_HISTORY.STEP | NAME | NOT NULL | VARCHAR | | corresponds to SCHEDULER_HISTORY.JOB_NAME e.g. test/my_job | BASENAME | NOT NULL | VARCHAR | | job base name e.g. my_job, for a given job path test/my_job.job.xml | TITLE | NULL | VARCHAR | | corresponds to INVENTORY_JOBS.TITLE | START_TIME | NOT NULL | DATETIME | | corresponds to SCHEDULER_ORDER_STEP_HISTORY.START_TIME | END_TIME | NULL | DATETIME | | corresponds to SCHEDULER_ORDER_STEP_HISTORY.END_TIME | STATE | NOT NULL | VARCHAR | | 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 REPORT_MAP_CAUSES | ERROR | NOT NULL | NUMERIC | | specifies if an error occurred: | ERROR_CODE | NULL | VARCHAR | | JobScheduler error code | ERROR_TEXT | NULL | VARCHAR | | error message | IS_RUNTIME_DEFINED | NOT NULL | NUMERIC | | specifies if a start time has been configured: 1: yes0: no |
|
| UTC timestamp of record creation |
MODIFIED | NOT NULL | DATETIME | | timestamp of record modification | |
...
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 contain aggregations for
- job steps per order
- triggers and executions per day, week, month, quarter, year
- SQL Scripts
- 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. |
| Expand |
|---|
| title | REPORT_TRIGGER_RESULTS| Field | Nullable | Data type | Constraint | Unique Constraint | Description |
|---|
ID | NOT NULL | NUMERIC | Primary Key |
|
|
| SCHEDULER_ID
| NOT NULL | VARCHAR |
SCHEDULERSCHEDULERSPOOLER_ID
| HISTORY_ID | NOT NULL | NUMERIC |
SCHEDULER SCHEDULER_ORDER_HISTORY.HISTORY_ID
|
TRIGGER_IDNUMERIC | Foreign Key | references REPORT_TRIGGERS.ID | START_CAUSE | NOT NULL | VARCHAR | | references SCHEDULER_HISTORY.CAUSE of the first step for the value order check the start cause given in INVENTORY_JOB_CHAINS.START_CAUSE | STEPS | NOT NULL | NUMERIC | | specifies the number of job steps that have been executed | ERROR | NOT NULL | NUMERIC | | specifies if an error occurred | ERROR_CODE | NULL | VARCHAR | | JobScheduler error code | ERROR_TEXT | NULL | VARCHAR | | error message | CREATED | NOT NULL | DATETIME | | timestamp of record creation | MODIFIED | NOT NULL | DATETIME | | timestamp of record modification | |
|---|
| Expand |
|---|
| title | REPORT_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 | REFERENCE_ID | NOT NULL | NUMERIC | Foreign Key | references REPORT_TRIGGERS.ID or REPORT_EXECUTIONS.ID | REFERENCE_TYPE | NOT NULL | NUMERIC | | type of reference: 0: references REPORT_TRIGGERS1: references REPORT_EXECUTIONS
| 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 | | timestamp of record creation | MODIFIED | NOT NULL | DATETIME | | 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
| Expand |
|---|
|
| 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 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 | | timestamp of record creation | MODIFIED | NOT NULL | DATETIME | | timestamp of record modification |
|
Standard Procedures
- 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
Report about Installed Objects
- Report about jobs, job chains and orders as collected from disk.
| Expand |
|---|
| title | REPORT_INSTALLED_OBJECTS |
|---|
|
CREATE OR REPLACE FUNCTION REPORT_INSTALLED_OBJECTS(start_date DATE, end_date DATE) RETURNS TABLE (
"ID" BIGINT,
"SCHEDULER_ID" VARCHAR(100),
"HOSTNAME" VARCHAR(255),
"JOB_CHAIN" VARCHAR(255),
"JOB_NAME" VARCHAR(255),
"JOB_BASENAME" VARCHAR(100),
"JOB_CHAIN_BASENAME" VARCHAR(100),
"JOB_TITLE" VARCHAR(255),
"JOB_CHAIN_TITLE" VARCHAR(255),
"CAUSE" VARCHAR(100),
"IS_ACTIVE_CALENDAR" INT,
"FILE_CREATED" TIMESTAMP,
"FILE_MODIFIED" TIMESTAMP
) |
Report about Execution Summary
- Report about executions of jobs and job chains.
| Expand |
|---|
| title | REPORT_EXECUTION_SUMMARY |
|---|
|
CREATE OR REPLACE FUNCTION REPORT_EXECUTION_SUMMARY(start_date DATE, end_date DATE) RETURNS TABLE (
"ID" BIGINT,
"SCHEDULER_ID" VARCHAR(100),
"HOSTNAME" VARCHAR(255),
"JOB_CHAIN" VARCHAR(255),
"JOB_NAME" VARCHAR(255),
"JOB_BASENAME" VARCHAR(100),
"JOB_CHAIN_BASENAME" VARCHAR(100),
"JOB_TITLE" VARCHAR(255),
"JOB_CHAIN_TITLE" VARCHAR(255),
"CAUSE" VARCHAR(100),
"IS_ACTIVE_CALENDAR" INT,
"START_TIME" TIMESTAMP,
"END_TIME" TIMESTAMP,
"START_DATE" DATE,
"END_DATE" DATE
) |
...
| 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: 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-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: | 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 |
| 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: 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: 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 |
| 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: | 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: 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 SCHEDULER_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 REPORTING_TASKS.ID
| 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 |
| CAUSE | NOT NULL | VARCHAR |
| 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
- 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)
cleanup only reporting entries
cleanup only dailyplan entries
SELECT REPORT_CLEANUP('dailyplan',0);- following tables will be truncated
cleanup only yade entries
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 entriesSELECT REPORT_CLEANUP('reporting',14);
- cleanup
reporting SELECT REPORT_CLEANUP('reporting',14);
- cleanup
reporting 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:
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