paul.maxwell

Know your CA PPM job schedule (at a glance)

Blog Post created by paul.maxwell on Jan 10, 2016

Jobs are an important part of Clarity/CA PPM yet it isn't simple to know when all your jobs are scheduled. A simple solution is to create a portlet that lists the jobs and the schedule parameters.

know-your-job-schedule.png

Here is an NSQL (Oracle) query to produce that portlet.

 

select

   @select:dim:user_def:implied:JOBS:id:id@

,  @select:dim_prop:user_def:implied:JOBS:name:name@

,  @select:dim_prop:user_def:implied:JOBS:jtype_name:jtype_name@

,  @select:dim_prop:user_def:implied:JOBS:sched_method:sched_method@

,  @select:dim_prop:user_def:implied:JOBS:status_code:status_code@

,  @select:dim_prop:user_def:implied:JOBS:start_date:start_date@

,  @select:dim_prop:user_def:implied:JOBS:end_date:end_date@

,  @select:dim_prop:user_def:implied:JOBS:schedule_date:schedule_date@

,  @select:dim_prop:user_def:implied:JOBS:minutes:minutes@

,  @select:dim_prop:user_def:implied:JOBS:hours:hours@

,  @select:dim_prop:user_def:implied:JOBS:days_of_week:days_of_week@

,  @select:dim_prop:user_def:implied:JOBS:months:months@

,  @select:dim_prop:user_def:implied:JOBS:days_of_month:days_of_month@

,  @select:dim_prop:user_def:implied:JOBS:last_updated_date:last_updated_date@

,  @select:dim_prop:user_def:implied:JOBS:is_active:is_active@

,  @select:dim_prop:user_def:implied:JOBS:is_log_enabled:is_log_enabled@

,  @select:dim_prop:user_def:implied:JOBS:url_properties:url_properties@

,  @select:dim_prop:user_def:implied:JOBS:url_definition:url_definition@

from (

    select

      jobs.id

    , jobs.name

    , jtype_name

    , case

        when jobs.recurrence_type = 3 then 'Cron Schedule'

        when jobs.recurrence_type = 2 then 'Monthly Schedule'

        when jobs.recurrence_type = 1 then 'Weekly Schedule'

        else 'User Request'

      end as sched_method

    , jobs.status_code

    , jobs.start_date

    , jobs.end_date

    , jobs.schedule_date

    , jobs.minutes

    , jobs.hours

    , jobs.days_of_week

    , jobs.months

    , jobs.days_of_month

    , jobs.last_updated_date

    , jdef.is_active

    , jdef.is_log_enabled

    , '/niku/nu#action:nmc.jobProperties&id=' || jobs.id url_properties

    , '/niku/nu#action:njs.editProgram&id=' || jobs.job_definition_id url_definition

    from cmn_sch_jobs jobs

    inner join cmn_sch_job_definitions jdef on jobs.JOB_DEFINITION_ID = jdef.id

    inner join (

            select

            id, lookup_code, sort_order, name jtype_name, description

            from cmn_lookups_v

            where lookup_type = 'SCH_JOB_TYPE'

            and language_code = @WHERE:PARAM:LANGUAGE@

          ) jtype on jdef.job_type = jtype.id

    where status_code in ('SCHEDULED','PAUSED')

    and jdef.is_active = 1

   ) innerq

where @filter@

Outcomes