Can you obtain statistics on the use of the tool?: Specifically, the number of executions per month.

[Juan Luis Sánchez Flores]

Hi, I would like to know if there is a mysql query or a way to obtain usage statistics directly in the tool, specifically the number of executions per month.

[Pablo Maldonado]

Hola Juan, 

Mira la verdad hace mucho no toco Schedulix, pero algo así creo es lo que queres :

## Jobs ejecutados por mes en Schedulix

select DATE_SUB(CONCAT(CURDATE(),' 00:00:00'), INTERVAL 43 DAY);
select DATE_SUB(CONCAT(CURDATE(),' 23:59:59'), INTERVAL  14 DAY);

SELECT COUNT(*)
FROM   SCI_SUBMITTED_ENTITY
WHERE  submit_ts BETWEEN DATE_SUB(CONCAT(CURDATE(),' 00:00:00'), INTERVAL 43 DAY)
       AND DATE_SUB(CONCAT(CURDATE(),' 23:59:59'), INTERVAL 14 DAY);

Lo de INTERVAL va a variar, podes ejecutar los dos primeros selects y vas a ver que fechas tomaría.

Es totalmente mejorable esa query !!!!

Pablo Maldonado

[Ronald Jeninga]

Hi all,

great answer, even if I don't speak Spanish. But the query seems OK to me.

If you'd like to know the number of executions per Job Definition, you can GROUP BY se_id.

And if you execute the query in the GUI-Shell (or in sdmsh), you can use an extension I've built.

That would then look like

SELECT SE_ID, COUNT(*)

FROM   SCI_SUBMITTED_ENTITY
WHERE  submit_ts BETWEEN DATE_SUB(CONCAT(CURDATE(),' 00:00:00'), INTERVAL 43 DAY)
       AND DATE_SUB(CONCAT(CURDATE(),' 23:59:59'), INTERVAL 14 DAY)

GROUP BY SE_ID

/* my extension: */

WITH SE_ID JOB;

The effect will be that the full name of the Job Definition will be displayed.

That would look like (in sdmsh):

[SYSTEM@localhost:2506] SDMS> SELECT SE_ID, COUNT(*)
FROM SCI_SUBMITTED_ENTITY
GROUP BY SE_ID
WITH SE_ID JOB;

Selected Values

SE_ID                                                     COUNT
--------------------------------------------------------- -----
SYSTEM.EXAMPLES.E0050_COMPLEX_CONDITION.LOAD_A                2
SYSTEM.EXAMPLES.E0120_SYNC_SIMPLE_LOCK.JOB_X                  2
SYSTEM.EXAMPLES.E0270_RUNTIME_ALERT.ALERTER                   2
SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB                     1
SYSTEM.EXAMPLES.E0050_COMPLEX_CONDITION.LOAD_B                2
SYSTEM.EXAMPLES.E0100_TRIGGER.ON_FAILURE                      1
SYSTEM.EXAMPLES.E0050_COMPLEX_CONDITION.REPORT                2
SYSTEM.EXAMPLES.E0080_LOADBALANCE.CHILD                      20
SYSTEM.EXAMPLES.E0120_SYNC_SIMPLE_LOCK.JOB_S                  4
SYSTEM.EXAMPLES.E0050_COMPLEX_CONDITION.SELECT_LOAD           2
SYSTEM.EXAMPLES.E0050_COMPLEX_CONDITION.COMPLEX_CONDITION     2
SYSTEM.EXAMPLES.E0270_RUNTIME_ALERT.RUNTIME_ALERT             2
SYSTEM.EXAMPLES.E0050_COMPLEX_CONDITION.LOAD_C                2
SYSTEM.EXAMPLES.E0100_TRIGGER.TRIGGER                         1
SYSTEM.EXAMPLES.E0050_COMPLEX_CONDITION.MAIN_LOAD             2
SYSTEM.EXAMPLES.E0080_LOADBALANCE.LOADBALANCE                 2
SYSTEM.EXAMPLES.E0050_COMPLEX_CONDITION.LOAD_COMPLETED        2


21 Row(s) selected

Since I started with a new repository last Monday, no need to for date arithmetic.

Best regards,

Ronald

[Dieter Stubler]

Hi,

Here is an example query on the schedulix_repository using a postgres repository. 

To execute this query you have use the frontend shell or the sdms command line utility because it will be executed by the schedulix server which passes everything before the with clause as is to your underlying databas system.
The query might not be compatible over all different systems. That's why you will have to adapt it for mysql. I will try to explain it as good as possible to understand it.

The reason not running it directly against your database system is the with clause which is processed by schedulix after query execution giving you nice way to obtain the full pathnames of your batches, jobs and folders.
Note that you have to be member of the ADMIN group to run queries through the schedulix server. 

select
   sme.master_id,
   msme.se_id as master_name,
   sme.id,
   sme.se_id as name,
   sme.start_ts,
   sme.finsh_ts,
   sme.finsh_ts - sme.start_ts as runtime
from
    sci_submitted_entity sme,
    sci_v_scheduling_entity se,
    sci_submitted_entity msme,
    sci_v_scheduling_entity mse
where
    msme.submit_ts between '2024-05-09 00:00:00' and '2024-05-09 23:59:59' and
    sme.master_id = msme.id and
    msme.se_id = mse.id and msme.se_version >= mse.valid_from and sme.se_version < mse.valid_to and
    sme.se_id  = se.id  and sme.se_version  >= se.valid_from and sme.se_version < se.valid_to and
    se.type = 'JOB'
with
    master_name folder,
    name folder
;

Table names here all start with sci_ indicating that we access the standard catalog views. Those views take care about versioning, decoding values, date conversion, ...
Unversioned tables only have one sci view with a prefix of sci_ like sci_submitted_entity.
Versioned tables have two views. Views with prefix sci_c_ showing the current (latest) version of rows. Views with prefix sci_v_ showing all versions with valid_from and valid_to which are not dates but tx ids.  
During operation schedulix uses the version of a versioned row which has been valid for the tx id of the submit of the master (top level) batch/job. 

The where clause:

msme.submit_ts between '2024-05-09 00:00:00' and '2024-05-09 23:59:59'

    The time interval you want to inspect (we want to see all masters submitted at one day here)

sme.master_id = msme.id and

    Join to the master sme 

 msme.se_id = mse.id and msme.se_version >= mse.valid_from and sme.se_version < mse.valid_to and
 sme.se_id  = se.id  and sme.se_version  >= se.valid_from and sme.se_version < se.valid_to 

   Join to the correct version of your master job definionen and job definition

se.type = 'JOB'

  We are only interested in runtimes of jobs here

Of course this is just an example and its up to you to you adapt the query for grouping, aggegating, sorting, ...
Hope this was usefull for you.

Regards
Dieter

pmald...@gmail.com schrieb am Mittwoch, 8. Mai 2024 um 22:59:29 UTC+2:

[Juan Luis Sánchez Flores]

Muchas gracias Pablo, me diste muchas pistas para elaborar un buen query.

[Juan Luis Sánchez Flores]

Thank you very much Ronald, your query is great and it helped me a lot.

[Juan Luis Sánchez Flores]

Thanks Dieter, the explanation and functionality helped me a lot to understand and obtain more statistics, again thank you very much.