on 06-18-2015 4:15 PM
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!
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
...and you can use Epoch Converter - Unix Timestamp Converter to convert the date time stamp to epoch (pick the value in milliseconds).
thanks
Nanda
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
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
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
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.