Skip to end of metadata
Go to start of metadata

In process

Icon
 

Scope

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
FieldNullableData typeConstraintUnique Constraint Description
IDNOT NULLNUMERICPrimary Key  
INSTANCE_IDNOT NULLNUMERICForeign Key  X

references INVENTORY_INSTANCES.ID

AGENT_CLUSTER_ID
NOT NULLNUMERICForeign Key  X

references INVENTORY_AGENT_CLUSTERS.ID

AGENT_INSTANCE_ID
NOT NULLNUMERICForeign Key  X

references INVENTORY_AGENT_INSTANCES.ID

URL
NOT NULLVARCHAR  

agent url

references INVENTORY_AGENT_INSTANCES.URL

ORDERING 
NOT NULL NUMERIC   Order of agents in remote_schedulers element
CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  UTC timestamp of record modification
 INVENTORY_AGENT_CLUSTERS
FieldNullableData typeConstraintUnique Constraint Description
IDNOT NULLNUMERICPrimary Key  
INSTANCE_IDNOT NULLNUMERICForeign Key 

references INVENTORY_INSTANCES.ID

PROCESS_CLASS_ID
NOT NULLNUMERICForeign Key  X

references INVENTORY_PROCESS_CLASSES.ID

SCHEDULING_TYPE
NOT NULLVARCHAR  

type of scheduling configured for the agent cluster

  • first Fixed Priority Scheduling
  • next Round Robin Scheduling
  • single
NUMBER_OF_AGENTS
NOT NULLNUMERIC   
CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  UTC timestamp of record modification
 INVENTORY_APPLIED_LOCKS
FieldNullableData typeConstraintUnique Constraint Description
IDNOT NULLNUMERICPrimary Key  
JOB_IDNOT NULLNUMERICForeign Key  X

references INVENTORY_JOBS.ID

LOCK_ID
NOT NULLNUMERICForeign Key X

references INVENTORY_LOCKS.ID

CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  UTC timestamp of record modification

  

 INVENTORY_FILES
FieldNullableData typeConstraintUnique ConstraintDescription
IDNOT NULLNUMERICPrimary Key  
INSTANCE_IDNOT NULLNUMERICForeign Key X

references INVENTORY_INSTANCES.ID

FILE_TYPENOT NULLVARCHAR  

file types in use:

  • job
  • job_chain
  • order

file types not used:

  • process_class
  • config
  • lock
  • schedule
  • ...
FILE_NAMENOT NULLVARCHAR  X

path from live directory

e.g. /test/my_job.job.xml

FILE_BASENAMENOT NULLVARCHAR  

file base name with extension

e.g. my_job.job.xml

FILE_DIRECTORYNOT NULLVARCHAR  

path calculated from the live directory

e.g. test

FILE_CREATEDNULLDATETIME  UTC timestamp of the creation date of the file
FILE_MODIFIEDNULLDATETIME  UTC timestamp of the modification date of the file
FILE_LOCAL_CREATEDNULLDATETIME  local timestamp of the creation date of the file
FILE_LOCAL_MODIFIEDNULLDATETIME  local timestamp of the modification date of the file
CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  UTC timestamp of record modification

  

 INVENTORY_INSTANCES
FieldNullableData typeConstraintUnique ConstraintDescription
IDNOT NULLNUMERICPrimary Key  
OS_ID 
NOT NULL NUMERIC Foreign Key  

references INVENTORY_OPERATING_SYSTEMS.ID 

SCHEDULER_IDNOT NULLVARCHAR  Xcorresponds to the SCHEDULER_ID that is specified during installation of the JobScheduler instance
HOSTNAMENOT NULLVARCHAR  Xhost on which the JobScheduler instance has been installed
PORTNOT NULLNUMERIC  X

HTTP port that the JobScheduler instance is listening to.

LIVE_DIRECTORYNOT NULLVARCHAR  path of the live directory
VERSION
NOT NULL VARCHAR   

JobScheduler version e.g. 1.11.0

COMMAND_URL 
NOT NULLVARCHAR  HTTP URL for XML request response
URL 
NOT NULLVARCHAR  TCP URL still used by supervisor
TIMEZONE
NOT NULLVARCHAR   
CLUSTER_TYPE
NOT NULLVARCHAR  

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 NULLVARCHAR  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    
CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  UTC timestamp of record modification
 INVENTORY_JOB_CHAIN_NODES
FieldNullableData typeConstraintUnique ConstraintDescription
IDNOT NULLNUMERICPrimary Key  
INSTANCE_IDNOT NULLNUMERICForeign Key 

references INVENTORY_INSTANCES.ID

JOB_ID 
NOT NULL NUMERIC Foreign Key  references INVENTORY_JOBS.ID 
JOB_CHAIN_IDNOT NULLNUMERICForeign Key references INVENTORY_JOB_CHAINS.ID
NESTED_JOB_CHAIN_ID 
NOT NULL NUMERIC Foreign Key  references INVENTORY_JOB_CHAINS.ID 
NAMENOT NULLVARCHAR  

job node name:

  • file_order_source
  • job_chain_node
  • file_order_sink
ORDERINGNOT NULLNUMERIC  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_NAMENOT NULLVARCHAR  

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

CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  UTC timestamp of record modification
 INVENTORY_JOB_CHAINS
FieldNullableData typeConstraintUnique ConstraintDescription
IDNOT NULLNUMERICPrimary Key  
INSTANCE_IDNOT NULLNUMERICForeign Key 

references INVENTORY_INSTANCES.ID

FILE_IDNOT NULLNUMERICForeign KeyXreferences INVENTORY_FILES.ID
PROCESS_CLASS_ID
NOT NULLNUMERICForeign Key references INVENTORY_PROCESS_CLASSES.ID
FW_PROCESS_CLASS_ID
NOT NULLNUMERICForeign Key references INVENTORY_PROCESS_CLASSES.ID
START_CAUSENOT NULLVARCHAR  

the following start causes are available:

  • file_trigger
  • order
NAMENOT NULLVARCHAR  

job chain name

e.g. /test/my_jobchain for a given job chain path /test/my_jobchain.job_chain.xml

BASENAMENOT NULLVARCHAR  

job chain base name

e.g. my_jobchain for a given job chain path /test/my_jobchain.job_chain.xml
TITLENULLVARCHAR  job chain title
MAX_ORDERS
 NUMERIC  max_orders allowed in the job chain, default is unlimited
DISTRIBUTED
NOT NULLNUMERIC  

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 NULLVARCHR  corresponds to INVENTORY_PROCESS_CLASSES.NAME
FW_PROCESS_CLASS
 VARCHAR  file_watching_process_class attribute of job chain
FW_PROCESS_CLASS_NAME
NOT NULLVARCHAR  corresponds to INVENTORY_PROCESS_CLASSES.NAME
CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  UTC timestamp of record modification
 INVENTORY_JOBS
FieldNullableData typeConstraintUnique ConstraintDescription
IDNOT NULLNUMERICPrimary Key  
INSTANCE_IDNOT NULLNUMERICForeign Key 

references INVENTORY_INSTANCES.ID

FILE_IDNOT NULLNUMERICForeign KeyXreferences INVENTORY_FILES.ID
PROCESS_CLASS_ID
NOT NULLNUMERICForeign Key references INVENTORY_PROCESS_CLASSES.ID
SCHEDULE_ID
NOT NULLNUMERICForeign Key references INVENTORY_SCHEDULES.ID
NAMENOT NULLVARCHAR  

job name

e.g. /test/my_job for a given job path /test/my_job.job.xml

BASENAMENOT NULLVARCHAR  

job base name

e.g. my_job for a given job path /test/my_job.job.xml

TITLENULLVARCHAR  job title

IS_ORDER_JOB

NOT NULLNUMERIC  

specifies if this job is part of a job chain:

  • 1: yes
  • 0: no

IS_RUNTIME_DEFINED

 

NOT NULLNUMERIC  

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 NULLVARCHAR  corresponds to INVENTORY_PROCESS_CLASSES.NAME
SCHEDULE
 VARCHAR  schedule attribute of job's runtime
SCHEDULE_NAME
NOT NULLVARCHAR  corresponds to INVENTORY_SCHEDULES.NAME
MAX_TASKSNOT NULLNUMERIC  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
CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  UTC timestamp of record modification
 INVENTORY_LOCKS
FieldNullableData typeConstraintUnique Constraint Description
IDNOT NULLNUMERICPrimary Key  
INSTANCE_IDNOT NULLNUMERICForeign Key  

references INVENTORY_INSTANCES.ID

FILE_ID
NOT NULLNUMERICForeign KeyX

references INVENTORY_FILES.ID

NAME
NOT NULLVARCHAR  lock file name
BASENAME
NOT NULLVARCHAR  lock base name
MAX_NON_EXCLUSIVE
 NUMERIC  if max_non_exclusive is null max_non_exclusive is set to unlimited
CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  UTC timestamp of record modification
 INVENTORY_OPERATING_SYSTEMS
FieldNullableData typeConstraintUnique Constraint Description
IDNOT NULLNUMERICPrimary Key  
HOSTNAMENOT NULLVARCHAR  

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)

CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  UTC timestamp of record modification
 INVENTORY_ORDERS
FieldNullableData typeConstraintUnique ConstraintDescription
IDNOT NULLNUMERICPrimary Key  
INSTANCE_IDNOT NULLNUMERICForeign Key 

references INVENTORY_INSTANCES.ID

FILE_IDNOT NULLNUMERICForeign KeyXreferences INVENTORY_FILES.ID
JOB_CHAIN_ID
NOT_NULLNUMERICForeign Key references INVENTORY_JOB_CHAINS.ID
SCHEDULE_ID
NOT_NULLNUMERICForeign Key references INVENTORY_SCHEDULES.ID
NAMENOT NULLVARCHAR  

order name

e.g. /test/my_jobchain,my_order for a given order path /test/my_jobchain,my_order.order.xml

BASENAMENOT NULLVARCHAR  

order base name

e.g. my_jobchain,my_order for a given job chain path /test/my_jobchain,my_order.order.xml
TITLENULLVARCHAR  order title
ORDER_IDNOT NULLVARCHAR  

order identification (unique per job chain)

e.g. my_order for a given order path /test/my_jobchain,my_order.order.xml

JOB_CHAIN_NAMENOT NULLVARCHAR  

corresponds to INVENTORY_JOB_CHAINS.NAME

e.g. /test/my_jobchain

SCHEDULE
 VARCHAR  schedule attribute of order's runtime
SCHEDULE_NAME
NOT_NULLVARCHAR  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 NULLNUMERIC  

specifies if a start time has been configured:

  • 1: yes
  • 0: no
CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  UTC timestamp of record modification
 INVENTORY_PROCESS_CLASSES
FieldNullableData typeConstraintUnique Constraint Description
IDNOT NULLNUMERICPrimary Key  
INSTANCE_IDNOT NULLNUMERICForeign Key X

references INVENTORY_INSTANCES.ID

FILE_ID
NOT NULLNUMERICForeign Key Xreferences INVENTORY_FILES.ID
NAME
NOT NULLVARCHAR  process class file name
BASENAME
NOT NULLVARCHAR  process class base name
MAX_PROCESSES
 NUMERIC  max parallel execution of the task
HAS_AGENTS
NOT NULLNUMERIC  

specifies if an agent has been configured in the process class:

  • 0: no
  • 1: yes
CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  UTC timestamp of record modification
 INVENTORY_SCHEDULES
FieldNullableData typeConstraintUnique Constraint Description
IDNOT NULLNUMERICPrimary Key  
INSTANCE_IDNOT NULLNUMERICForeign Key  

references INVENTORY_INSTANCES.ID

FILE_ID
NOT NULLNUMERICForeign Key Xreferences INVENTORY_FILES.ID
SUBSTITUTE_ID
NOT NULLNUMERICForeign Key references INVENTORY_SCHEDULES.ID
NAME
NOT NULLVARCHAR  schedule file name
BASENAME
NOT NULLVARCHAR  schedule base name
TITLE
 VARCHAR  schedule's title
SUBSTITUTE
 NUMERIC  substitute attribute
SUBSTITUTE_NAME
NOT NULLNUMERIC  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
CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  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
FieldNullableData typeConstraintUnique Constraint Description
IDNOT NULLNUMERICPrimary Key  
SCHEDULER_IDNOT NULLVARCHAR Xcorresponds to the SCHEDULER_ID that is specified during installation of the JobScheduler instance
ACCOUNTNOT NULLVARCHAR  user account the audit was stored for
REQUESTNOT NULLVARCHAR  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
CREATEDNOT NULLDATETIME  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. 

FieldNullableData typeConstraintUnique ConstraintDescription
IDNOT NULLNUMERICPrimary Key  

SCHEDULER_ID

NOT NULL

VARCHAR

 

X

corresponds to SCHEDULER_ORDER_HISTORY.SPOOLER_ID

HISTORY_IDNOT NULLNUMERIC X

corresponds to SCHEDULER_ORDER_HISTORY.HISTORY_ID

NAMENOT NULLVARCHAR  

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 NULLVARCHAR  

job chain folder 

e.g. /test for a given job chain path /test/my_jobchain.job_chain.xml

PARENT_NAMENOT NULLVARCHAR  

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_TIMENOT NULLDATETIME  

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 NULLNUMERIC   specifies if a start time has been configured:
  • 1: yes
  • 0: no

corresponds to INVENTORY_ORDERS.IS_RUNTIME_DEFINED

RESULT_START_CAUSE
NOT NULLVARCHAR  

cause of the order start

  • order
  • file_trigger
  • ... 

corresponds to INVENTORY_JOB_CHAINS.START_CAUSE

RESULT_STEPS
NOT NULLNUMERIC  number of executed order steps 
RESULT_ERROR
NOT NULLNUMERIC  

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_COMPLETEDNOT NULLNUMERIC  

for use by the JobScheduler Reporting Interface only:
specifies if a synchronization has been completed:

  • 1: yes
  • 0: no
RESULTS_COMPLETEDNOT NULLNUMERIC  

for use by the JobScheduler Reporting Interface only:
specifies if a aggregation has been completed:

  • 1: yes
  • 0: no
CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  UTC timestamp of record modification
 REPORTING_EXECUTIONS

Contains the informations about executed order steps. 

FieldNullableData typeConstraintUnique ConstraintDescription
IDNOT NULLNUMERICPrimary Key  
SCHEDULER_ID NOT NULLNUMERIC Xcorresponds to SCHEDULER_ORDER_HISTORY.SPOOLER_ID
HISTORY_IDNOT NULLNUMERIC Xcorresponds to SCHEDULER_ORDER_HISTORY.HISTORY_ID
TRIGGER_IDNOT NULLNUMERICForeign KeyXreferences REPORT_TRIGGERS.ID
TASK_ID
NOT NULLNUMERICForeign Key references REPORT_TASKS.ID
CLUSTER_MEMBER_ID
 VARCHAR  corresponds to SCHEDULER_HISTORY.CLUSTER_MEMBER_ID
STEPNOT NULLNUMERIC X

consecutive number of the order step

corresponds to SCHEDULER_ORDER_STEP_HISTORY.STEP

FOLDER
NOT NULLVARCHAR  

folder of the order step 

e.g. /test for a given job chain path /test/my_jobchain.job_chain.xml 

NAMENOT NULLVARCHAR  

job name of the order step

corresponds to SCHEDULER_HISTORY.JOB_NAME

e.g. /test/my_job

BASENAMENOT NULLVARCHAR  

job base name of the order step

e.g. my_job, for a given job path /test/my_job.job.xml

TITLENULLVARCHAR  

job title of the order step

corresponds to INVENTORY_JOBS.TITLE

START_TIMENOT NULLDATETIME  

UTC timestamp of the start of the order step

corresponds to SCHEDULER_ORDER_STEP_HISTORY.START_TIME

END_TIMENULLDATETIME  

UTC timestamp of the end of the order step

corresponds to SCHEDULER_ORDER_STEP_HISTORY.END_TIME

STATENOT NULLVARCHAR  

state of the order inside the job chain

corresponds to SCHEDULER_ORDER_STEP_HISTORY.STATE

CAUSENOT NULLVARCHAR  

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 NULLNUMERIC  corresponds to SCHEDULER_HISTORY.EXIT_CODE
ERRORNOT NULLNUMERIC  

specifies if an error occurred:

  • 0: no error
  • 1: error

corresponds to SCHEDULER_ORDER_STEP_HISTORY.ERROR

ERROR_CODENULLVARCHAR  

exception-code of the order step error

corresponds to SCHEDULER_ORDER_STEP_HISTORY.ERROR_CODE

ERROR_TEXTNULLVARCHAR  

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_DEFINEDNOT NULLNUMERIC  

specifies if a start time has been configured:

  • 1: yes
  • 0: no

corresponds to INVENTORY_JOBS.IS_RUNTIME_DEFINED

SYNC_COMPLETED
NOT NULLNUMERIC  

for use by the JobScheduler Reporting Interface only:
specifies if a synchronization has been completed:

  • 1: yes
  • 0: no
RESULTS_COMPLETED
NOT NULLNUMERIC  

for use by the JobScheduler Reporting Interface only:
specifies if a aggregation has been completed:

  • 1: yes
  • 0: no
CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  UTC timestamp of record modification

 

 REPORTING_TASKS

Contains the informations about executed job tasks.

FieldNullableData typeConstraintUnique ConstraintDescription
IDNOT NULLNUMERICPrimary Key  
SCHEDULER_ID NOT NULLNUMERIC  Xcorresponds to SCHEDULER_HISTORY.SPOOLER_ID
HISTORY_IDNOT NULLNUMERIC  Xcorresponds to SCHEDULER_HISTORY.ID
IS_ORDER
NOT NULLNUMERIC  

cause for execution of the task:

  • 1: order task
  • 0: standalone task
CLUSTER_MEMBER_ID
 VARCHAR  corresponds to SCHEDULER_HISTORY.CLUSTER_MEMBER_ID
STEPSNOT NULLNUMERIC  

number of steps --> calls to spooler_process()

corresponds to SCHEDULER_HISTORY.STEPS

FOLDER
NOT NULLVARCHAR  

job folder 

e.g. /test, for a given job path /test/my_job.job.xml

NAMENOT NULLVARCHAR  

job name

corresponds to SCHEDULER_HISTORY.JOB_NAME

e.g. /test/my_job, for a given job path /test/my_job.job.xml 

BASENAMENOT NULLVARCHAR  

job base name

e.g. my_job, for a given job path /test/my_job.job.xml

TITLENULLVARCHAR  

job title

corresponds to INVENTORY_JOBS.TITLE

START_TIMENOT NULLDATETIME  

UTC timestamp of the task start

corresponds to SCHEDULER_HISTORY.START_TIME

END_TIMENULLDATETIME  

UTC timestamp of the task end

corresponds to SCHEDULER_HISTORY.END_TIME

CAUSENOT NULLVARCHAR  

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 NULLNUMERIC  corresponds to SCHEDULER_HISTORY.EXIT_CODE
ERRORNOT NULLNUMERIC  

specifies if an error occurred:

  • 0: no error
  • 1: error
ERROR_CODENULLVARCHAR  exception-code of the task error
ERROR_TEXTNULLVARCHAR  exception-message of the task error
AGENT_URL
 VARCHAR  

agen url

corresponds to SCHEDULER_HISTORY.AGENT_URL

IS_RUNTIME_DEFINEDNOT NULLNUMERIC  

specifies if a start time has been configured:

  • 1: yes
  • 0: no

corresponds to INVENTORY_JOBS.IS_RUNTIME_DEFINED

SYNC_COMPLETED
NOT NULLNUMERIC  

for use by the JobScheduler Reporting Interface only:
specifies if a synchronization has been completed:

  • 1: yes
  • 0: no
RESULTS_COMPLETED
NOT NULLNUMERIC  

for use by the JobScheduler Reporting Interface only:
specifies if a aggregation has been completed:

  • 1: yes
  • 0: no
CREATEDNOT NULLDATETIME  UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME  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
FieldNullableData typeConstraintDescription
IDNOT NULLNUMERICPrimary Key 
SCHEDULER_IDNOT NULLVARCHAR corresponds to SCHEDULER_ORDER_HISTORY.SCHEDULER_ID
HISTORY_IDNOT NULLNUMERIC 

corresponds to 

  • SCHEDULER_ORDER_HISTORY.HISTORY_ID (triggers, executions) or
  • SCHEDULER_HISTORY.ID (tasks) 
REFERENCE_IDNOT NULLNUMERICForeign Key

references

  • REPORTING_TRIGGERS.ID or
  •  REPORTING_EXECUTIONS.ID or 
  • REPORTING_TASKS.ID
REFERENCE_TYPENOT NULLNUMERIC 

type of reference:

  • 0: references REPORTING_TRIGGERS
  • 1: references REPORTING_EXECUTIONS
  • 2: references REPORTING_TASKS
START_DAYNOT NULLNUMERIC 

calendar day

e.g. 1

START_WEEKNOT NULLNUMERIC 

calendar week

e.g. 52

START_MONTHNOT NULLNUMERIC 

calendar month

e.g. 12

START_QUARTERNOT NULLNUMERIC 

quarter

e.g. 1

START_YEARNOT NULLNUMERIC 

year

e.g. 2015

END_DAYNOT NULLNUMERIC 

calendar day

e.g. 1

END_WEEKNOT NULLNUMERIC 

calendar week

e.g. 52

END_MONTHNOT NULLNUMERIC 

calendar month

e.g. 12

END_QUARTERNOT NULLNUMERIC 

quarter

e.g. 1

END_YEARNOT NULLNUMERIC 

calendar year

e.g. 2015

CREATEDNOT NULLDATETIME UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME 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
FieldNullableData typeConstraintDescription
IDNOT NULLNUMERICPrimary Key 
CAUSENOT NULLVARCHAR 

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_CAUSENOT NULLVARCHAR individual mapping of start cause
CREATEDNOT NULLDATETIME UTC timestamp of record creation
MODIFIEDNOT NULLDATETIME 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
       

 

 

  • No labels