Clarity PPM - How to get a list of all jobs running in a system
Issue/Introduction:
- Jobs can be scheduled by different admin accounts in Clarity PPM, and some of them can be invisible from any given admin account.
- Therefore, how can I extract the of all the jobs that have executed upto a point in time, irrespective of which admin account it was scheduled from?
- (And also get useful information such as their execution times, parameters used etc.?)
Resolution:
- Following is a query that can be run on the Database of a given system, to get such a list. (For On Premise customers)
- This can also be modified into an NSQL Query to create a portlet. (For On Premise and SaaS customers)
- If we are interested in specific jobs, then provide that job's name in the first WHERE clause, otherwise comment it out.
- If we are interested in specific jobs, then provide that job's name in the first WHERE clause, otherwise comment it out.
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
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
JOIN cmn_captions_nls ccn ON
ccn.pk_id = att.id
AND
ccn.table_name = 'CMN_VIEW_ATTRIBUTES'
WHERE
lower(j.name) LIKE '%<provide job name in lower case>%'
AND
ccn.language_code = 'en'
AND
att.display_order = 0
ORDER BY
jrun.start_date DESC,
jrun.job_id,
ccn.name ASC;
Some examples of interpreting the resultant dataset for the above query.
1. Since the Load Data Warehouse job has only one parameter, there will be a single row for each execution of this job
2. 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.
1. Since the Load Data Warehouse job has only one parameter, there will be a single row for each execution of this job
2. 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.
Additional Information:
See KB: 141061 for tips on how to optimize use of Self Service for Clarity PPM