How to generate automatic reports through Schedulix Like the batch name, Start time, time taken, total time taken, successful job,Failed job?

[saloni mittal]

Hi,

How can we generate automatic report through Schedulix.

After executing all batches in Schedulix , is there any provision that we can see the full details in one report.

Thank you,

Saloni Mittal

[Dieter Stubler]

Hi Mittal,

To generate any report you can just use SQL queries on the schedulix repository database.
Use the SCI_... views which decode values and transform unix timestamps into data types.

Most of the colum names are self explaining if you have a look at the API syntax.

Foreign keys have the format <abrivation>_id for example esd_id -> id of exit_state_definition.

Version objects have two views names SCI_C_... SCI_V_...
SCI_C_... views contain the current definition, SCI_V... views contain all versions with addition attributes VALID_FROM and VALID_TO

So SCI_SUBMITTED_ENTITY contains all instances for submitted batches and jobs.

To correctly join SCI_SUBMITTED_ENTITY with versioned objects like exit state definition you have to use an additional condtion utilising the SE_VERSION_ID.

Example:

Generate a report of all master submitted entities with their name, start_timr, end_time and their exit state you can use the following query:

select se.name, 

       sme.start_ts, 

       sme.finsh_ts, 

       esd.name

  from SCI_SUBMITTED_ENTITY sme,

       SCI_V_SCHEDULING_ENTITY se,

       SCI_V_EXIT_STATE_DEFINITION esd

 where se.id = sme.se_id

   and sme.se_version >= se.valid_from 

   and sme.se_version <  se.valid_to

   and esd.id = sme.final_esd_id

   and sme.se_version >= esd.valid_from 

   and sme.se_version <  esd.valid_to;

When issuing the query from sdmsh (user logged in must be member of group admin) you can issue the query as:

select se.id as se_id, 

       sme.start_ts, 

       sme.finsh_ts, 

       esd.name

  from SCI_SUBMITTED_ENTITY sme,

       SCI_V_SCHEDULING_ENTITY se,

       SCI_V_EXIT_STATE_DEFINITION esd

 where se.id = sme.se_id

   and sme.se_version >= se.valid_from 

   and sme.se_version <  se.valid_to

   and esd.id = sme.final_esd_id

   and sme.se_version >= esd.valid_from 

   and sme.se_version <  esd.valid_to

with se_id folder;

the with se_id folder will convert the se_id into the full path_name of the scheduling entity; 

Regards

Dieter

[saloni mittal]

Hi Dieter,

Thank you.

While using the below query:

select se.id as se_id, 

       sme.start_ts, 

       sme.finsh_ts, 

       esd.name

  from SCI_SUBMITTED_ENTITY sme,

       SCI_V_SCHEDULING_ENTITY se,

       SCI_V_EXIT_STATE_DEFINITION esd

 where se.id = sme.se_id

   and sme.se_version >= se.valid_from 

   and sme.se_version <  se.valid_to

   and esd.id = sme.final_esd_id

   and sme.se_version >= esd.valid_from 

   and sme.se_version <  esd.valid_to

with se_id folder;

I am getting an error as You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SE_ID FOLDER' at line 14

mysql version is : 5.6.40 

Thanks,

Saloni Mittal

[Dieter Stubler]

Hello Mittal,

did you issue the query from the schedulix sdmsh utility ?

if you use the mysql command line utility 'with se_id folder' is not understood.

Regards
Dieter

[saloni mittal]

Hi Dieter,

We have used query given by you to generate report from Schedulix(attachment:query.sdms)

and we have used below command :

sdmsh < /home/schedulix/query.sdms >/home/schedulix/report/report.txt

but from that in the attached report.txt we want mentioned below section :

NAME      START_TS                FINSH_TS                NAME  
--------- ----------------------- ----------------------- -------
MOM_BATCH 2018-07-18 22:30:14.388 2018-07-18 22:30:14.388 FAILURE
DUMMY_JOB 2018-07-18 22:30:25.0   2018-07-18 22:30:25.0   FAILURE

What change do we need to achieve that?

Thank you,

Saloni Mittal

[Ronald Jeninga]

Hi Saloni,

well, you could use AWK, Perl, Python, Ruby, sed, ... to filter out the lines you're not interested in.

Or you can use some professional reporting tool to create a visually attractive report.

Or you can use OpenOffice (or some commercial software) and access the database using odbc and do the formatting of the resulting spreadsheet according to your needs.

Dieter gave you the SQL query to retrieve the required information. How you use that query in combination with what tool is entirely up to you.

Best regards,

Ronald

[saloni mittal]

Hi Ronald,

Thank you for the help, we have used "sed" command for customizing report.

But query given by dieter is below:

select se.id as se_id, 

       sme.start_ts, 

       sme.finsh_ts, 

       esd.name

  from SCI_SUBMITTED_ENTITY sme,

       SCI_V_SCHEDULING_ENTITY se,

       SCI_V_EXIT_STATE_DEFINITION esd

 where se.id = sme.se_id

   and sme.se_version >= se.valid_from 

   and sme.se_version <  se.valid_to

   and esd.id = sme.final_esd_id

   and sme.se_version >= esd.valid_from 

   and sme.se_version <  esd.valid_to

with se_id folder;

and we need alias as STATUS for name column, SO we have modified this with "as status"(mentioned in red colour):

select se.id as se_id, 

       sme.start_ts, 

       sme.finsh_ts, 

       esd.name as status

  from SCI_SUBMITTED_ENTITY sme,

       SCI_V_SCHEDULING_ENTITY se,

       SCI_V_EXIT_STATE_DEFINITION esd

 where se.id = sme.se_id

   and sme.se_version >= se.valid_from 

   and sme.se_version <  se.valid_to

   and esd.id = sme.final_esd_id

   and sme.se_version >= esd.valid_from 

   and sme.se_version <  esd.valid_to

with se_id folder;

so from this query we are getting output as:

NAME      START_TS                FINSH_TS                NAME  
--------- ----------------------- ----------------------- -------
MOM_BATCH 2018-07-18 22:30:14.388 2018-07-18 22:30:14.388 FAILURE
DUMMY_JOB 2018-07-18 22:30:25.0   2018-07-18 22:30:25.0   FAILURE

but we want last coloumn name as STATUS instead of NAME.

How we can achieve that?

Thank you,

Saloni Mittal.

[Ronald Jeninga]

Hi Saloni,

this seems to be an issue of the underlying DBMS.

schedulix does nothing more than to request the describing information of the output and uses this to render the output.

Since you're working with sed already, you could replace the headline, e.g.

s/^(.*) NAME *$/\1 STATUS/

or something alike.

(I didn't test this, but that's basically the pattern).

As long as you don't have exit state definitions that are called NAME, it should work.

Best regards,

Ronald