Product Knowledge Base

Space shortcuts

Page tree

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Next »

Scope

  • Provide technical information on possible customizations of the data model.
  • Users can use this information to create their own reports from the data model.
  • SQL scripts in this article are provided for PostgreSQL. The SQL scrpts for the respective DBMS are provided by the JobScheduler installer.

Customization

  • The standard data model can be customized by use of additional tables and procedures.
  • Such tables are not required by the JobScheduler Reporting Interface.

Sample Custom Tables

  • The following tables implement samples for the customization of reports:
    • Assigning applications to jobs
    • Assigning mandators to jobs
  • Samples are presented for instructional use only.
  • SQL Scripts

Assigning Applications to Jobs

  • Should a report present information on a number of jobs that are used for the same application then the following tables could be used:

Application Master Data

 

FieldNullableData typeConstraintDescription
IDNOT NULLNUMERICPrimary Key 
NAMENOT NULLVARCHAR application name
TITLENULLVARCHAR application title

Application Joins to Jobs

 

FieldNullableData typeConstraintDescription
IDNOT NULLNUMERICPrimary Key 
JOB_NAMENOT NULLVARCHAR corresponds to INVENTORY_JOBS.NAME
APP_NAMENOT NULLVARCHAR corresponds REPORT_CUSTOM_APPS.NAME

Assigning Mandators to Jobs

Mandator Master Data

 

FieldNullableData typeConstraintDescription
IDNOT NULLNUMERICPrimary Key 
NAMENOT NULLVARCHAR mandator name
TITLENULLVARCHAR mandator title

Mandator Joins to Jobs

 

FieldNullableData typeConstraintDescription
IDNOT NULLNUMERICPrimary Key 
JOB_CHAINNOT NULLVARCHARForeign Keyreferences REPORT_JOBS.ID
MANDATOR_IDNOT NULLNUMERICForeign Keyreferences REPORT_MANDATOR.ID

Sample Custom 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 above custom tables for reporting results that are aggregated per application and mandator.
  • SQL Scripts

Report about Installed Objects

  • The standard procedure is improved to map installed objects to applications and mandators.

Custom Report on Installed Objects

 

CREATE OR REPLACE FUNCTION REPORT_CUSTOM_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"          DATE,
    "FILE_MODIFIED"         DATE,
    "MANDATOR_NAME"         VARCHAR(100), 
    "APPLICATION_NAME"      VARCHAR(100)
)

Report about Executions

Custom Report on Execution Summary

 

CREATE OR REPLACE FUNCTION REPORT_CUSTOM_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,
    "MIN_START_DATE"        DATE,
    "MAX_START_DATE"        DATE,
    "MANDATOR_NAME"         VARCHAR(100), 
    "APPLICATION_NAME"      VARCHAR(100)
)

 

Sample Excel Template

Error rendering macro 'viewxls'

com.atlassian.confluence.macro.MacroExecutionException: com.atlassian.confluence.macro.MacroExecutionException: The viewfile macro is unable to locate the attachment "JobSchedulerReporting Customization.xlsx" on this page

  • No labels