Skip to end of metadata
Go to start of metadata


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
Write a comment…