cancel
Showing results for 
Search instead for 
Did you mean: 

Report to find jobs no longer scheduled - cps 8

Former Member
0 Kudos

Hello all,

I'm looking for a way(script) to find all job defintions / job chains that havent been scheduled or run in x number of days. This will enable us to clean up definitions that are no longer needed.

Thanks!

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hi Brain,

If the job definition or job chain ran once at least then we can identified using the custom filter from job monitoring section.


If not ran at least once then do the same thing from the job definition/job chain from definition window.

Export both the filters from through reports from the definition window.

Use Excel for comparing the jobs from both reports through VLOOKUP.

This is how is how i will do.Please let me know if you find any other way.

Former Member
0 Kudos

Zameer,

This is a great recommendation but in our system we are removing jobs after 30 days. With this configuration we could have multiple jobs that no longer have history and wouldn't be detected by the filter.

Thanks,

Brian

0 Kudos

Hi Brain,

Try to remove all those jobs from excel remove duplicates.

I think this is the best option.Please try to share if you get any best alternative method here..

It will help us lot.


Answers (1)

Answers (1)

nanda_kumar21
Active Contributor
0 Kudos

hi Brian,

I am taking this suggestion from another thread by .

the runstart column is epoch time(bigint) in Job table.

Say, you want the job that has not run after May 1, 2015 - convert that to epoch (milliseconds)

For each job definition in jobdefinition table

     check if there exists an entry greater than above epoch value in Job table

          Yes-->do nothing

          else --> print the job definition

the result will be you required list.     

As this may be an expensive statement on your DB, you can narrow down the selection on job definition table by name pattern or applications or partitions.

thanks

Nanda

Former Member
0 Kudos

Nanda,

Is there any chance you could provide a SQL example I could use to modify?

Thanks,

Brian

gmblom
Active Contributor
0 Kudos

select jd.* from JobDefinition jdm where jdm.UniqueId in (select distinct jd.MasterJobDefinition from JobDefinition jd where not exists (select 1 from Job j where j.JobDefintion = jd.UniqueId and j.ScheduledStartTime > 123456789))

where 123456789 is your check date in milliseconds since 1970.

Regards Gerben

nanda_kumar21
Active Contributor
0 Kudos

...and you can use Epoch Converter - Unix Timestamp Converter to convert the date time stamp to epoch (pick the value in milliseconds).

thanks

Nanda

Former Member
0 Kudos

Thanks for all the feedback so far on this.

I have attempted to use this in a report as well as a filter and every attempt to validate or save results in CPS issues the following messages -

"Connection to the server was lost; restart the application" and "Connection to the server has been restored" both of which are preventing me from saving.

Gerben or Nanda - do you have the same issue?

Thanks,

Brian

nanda_kumar21
Active Contributor
0 Kudos

If you executed the query in shell, you will encounter timeout issue because of the volume of records its pulling.

Save it in a job definition of type redwood script and and run it. It may take a long time for the job to complete.

For better performance, narrow down the selection to fewer records, for example add one more where clause to select only job definitions that start with 'ECC%' or that belongs to particular partition.

thanks

Nanda

gmblom
Active Contributor
0 Kudos

Hello Brian,

In a Report try 'select * from ' and select 'JobDefinition' from the dropdown box.

In the where clause box paste the following:

JobDefinition.UniqueId in (select distinct jd.MasterJobDefinition from JobDefinition jd where not exists (select 1 from Job j where j.JobDefintion = jd.UniqueId and j.ScheduledStartTime > 123456789))

Regards Gerben