Get a list of all jobs running in Clarity

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

Jobs can be scheduled by different admin accounts in Clarity, and some of them might not get displayed at Home -> Reports and Jobs, under certain circumstances
How can a list of all jobs that have executed upto a point in time, irrespective of the account from which they got scheduled, be obtained?
And also get other useful information such as their execution times, parameters used etc.?

Resolution

Following queries can be used to get this information directly from the Database. Choose the query based on the DB vendor in use.

SELECT
jrun.job_id,
j.name,
jparam.value,
ccn.name parameter_name,
CAST(jrun.start_date AS TIMESTAMP) start_date,
CAST(jrun.end_date AS TIMESTAMP) end_date,
round(
jrun.total_execution_time / (1000 * 60),
2
) "ExecutionTime(Minutes)",
jrun.status_code,
sr.full_name username,
jrun.scheduler_id
FROM
cmn_sch_job_runs jrun
JOIN cmn_sch_jobs j ON j.id = jrun.job_id
JOIN srm_resources sr ON sr.user_id = jrun.user_id
LEFT JOIN cmn_sch_job_run_params jparam ON jparam.job_run_id = jrun.id
LEFT JOIN cmn_view_attributes att ON
jparam.attribute_id = att.attribute_id
AND
att.display_order = 0
LEFT JOIN cmn_captions_nls ccn ON
ccn.pk_id = att.id
AND
ccn.table_name = 'CMN_VIEW_ATTRIBUTES'
AND
ccn.language_code = 'en'
WHERE
-- Replace with more appropriate Job Name in lower case, separated by wildcard character '%' if required
-- Remove the condition if analyzing for all the jobs
lower(j.name) LIKE '%<provide job name in lower case>%'
AND
-- Provide more appropriate Date/Time below
jrun.end_date BETWEEN TO_DATE('15-02-2022 13:00:00','dd-mm-yyyy hh24:mi:ss') AND TO_DATE('15-02-2022 14:00:00','dd-mm-yyyy hh24:mi:ss'
)
ORDER BY
jrun.end_date DESC,
jrun.job_id,
ccn.name ASC

Postgres

SELECT
jrun.job_id,
j.name,
jparam.value,
ccn.name parameter_name,
jrun.start_date::timestamp as start_date,
jrun.end_date::timestamp as end_date,
round(
jrun.total_execution_time / (1000 * 60),
2
) "ExecutionTime(Minutes)",
jrun.status_code,
sr.full_name username,
jrun.scheduler_id
FROM
cmn_sch_job_runs jrun
JOIN cmn_sch_jobs j ON j.id = jrun.job_id
JOIN srm_resources sr ON sr.user_id = jrun.user_id
LEFT JOIN cmn_sch_job_run_params jparam ON jparam.job_run_id = jrun.id
LEFT JOIN cmn_view_attributes att ON
jparam.attribute_id = att.attribute_id
AND
att.display_order = 0
LEFT JOIN cmn_captions_nls ccn ON
ccn.pk_id = att.id
AND
ccn.table_name = 'CMN_VIEW_ATTRIBUTES'
AND
ccn.language_code = 'en'
WHERE
-- Replace with more appropriate Job Name in lower case, separated by wildcard character '%' if required
-- Remove the condition if analyzing for all the jobs
lower(j.name) LIKE '%load%data%warehouse%'
AND
-- Provide more appropriate Date/Time below
jrun.start_date > '2024-10-02'::date
ORDER BY
jrun.end_date DESC,
jrun.job_id,
ccn.name ASC

MS SQL Server

SELECT
jrun.job_id,
j.name,
jparam.value,
ccn.name parameter_name,
CAST(jrun.start_date AS datetime) start_date,
CAST(jrun.end_date AS datetime) end_date,
round(
jrun.total_execution_time / (1000 * 60),
2
) "ExecutionTime(Minutes)",
jrun.status_code,
sr.full_name username,
jrun.scheduler_id
FROM
cmn_sch_job_runs jrun
JOIN cmn_sch_jobs j ON j.id = jrun.job_id
JOIN srm_resources sr ON sr.user_id = jrun.user_id
LEFT JOIN cmn_sch_job_run_params jparam ON jparam.job_run_id = jrun.id
LEFT JOIN cmn_view_attributes att ON
jparam.attribute_id = att.attribute_id
AND
att.display_order = 0
LEFT JOIN cmn_captions_nls ccn ON
ccn.pk_id = att.id
AND
ccn.table_name = 'CMN_VIEW_ATTRIBUTES'
AND
ccn.language_code = 'en'
WHERE
lower(j.name) LIKE '%load%data%warehouse%'
ORDER BY
jrun.end_date DESC,
jrun.job_id,
ccn.name ASC

Sample NSQL Query

Notice that this query does not have any WHERE clause in it. The filtering, ordering etc. can be accomplished by way of Portlet configuration.

select
@select:dim:user_def:implied:d0:rownum:row_num@,
@select:dim_prop:user_def:implied:d0:job_id:job_id@,
@select:dim_prop:user_def:implied:d0:name:job_name@,
@select:dim_prop:user_def:implied:d0:value:parameter_value@,
@select:dim_prop:user_def:implied:d0:parameter_name:parameter_name@,
@select:dim_prop:user_def:implied:d0:start_date:start_date@,
@select:dim_prop:user_def:implied:d0:end_date:end_date@,
@select:dim_prop:user_def:implied:d0:execution_time_minutes:time_taken_min@,
@select:dim_prop:user_def:implied:d0:status_code:job_status@,
@select:dim_prop:user_def:implied:d0:username:job_scheduled_by@,
@select:dim_prop:user_def:implied:d0:scheduler_id:job_executed_scheduler@
from(
SELECT
row_number() over (order by jrun.start_date asc) as rownum,
jrun.job_id,
j.name,
jparam.value,
ccn.name parameter_name,
jrun.start_date::timestamp as start_date,
jrun.end_date::timestamp as end_date,
round(
jrun.total_execution_time / (1000 * 60),
2
) execution_time_minutes,
jrun.status_code,
sr.full_name username,
jrun.scheduler_id
FROM
cmn_sch_job_runs jrun
JOIN cmn_sch_jobs j ON j.id = jrun.job_id
JOIN srm_resources sr ON sr.user_id = jrun.user_id
LEFT JOIN cmn_sch_job_run_params jparam ON jparam.job_run_id = jrun.id
LEFT JOIN cmn_view_attributes att ON
jparam.attribute_id = att.attribute_id
AND
att.display_order = 0
LEFT JOIN cmn_captions_nls ccn ON
ccn.pk_id = att.id
AND
ccn.table_name = 'CMN_VIEW_ATTRIBUTES'
AND
ccn.language_code = 'en'
)q
where @filter@

Additional Information

Some examples of interpreting the query result:

Since the Load Data Warehouse job has only one parameter, there will be a single row for each execution of this job
There can be jobs with more than one parameter in them. For such jobs, the number of rows per execution will be equal to the number of parameters in the job.