cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
339
Views
5
Helpful
1
Replies

Tidal Program Mgr

kimbeller
Beginner
Beginner

How can I get a list of all the jobs that fail with the same dependencies?  I am new to creating SQL queries? 

1 Reply 1

jeff.hamway01
Beginner
Beginner

Hello Kim,  Hope the below helps.

 

First, my query below is assuming you mean 'Job Dependencies' ( if you meant variable, file, command-line etc.. you will need to adjust the query.

2nd, familiarize yourself with the schema (Scheduler Data Model) which is located in the documentation files.

 

This is how I know how to do it, their are many other ways to write the script im sure. if anyone has a more elegant solution, please share.

 

REQUEST: Find Job failures that use the same Job Dependency

jobdep_jobmst - the Job dependency ID that you want to query ( you can get this from the job definition, its also a column in the job definition view)

jobrun_status - Using 103 'Completed Abnormally'.  If you want to add additional status notations, feel free

Time Frame - I added a timeframe in the script, You can remove if if you want.  Please NOTE: anything older then you set historical will have to be queried using jobrun_statusx.  

here is the query. feel free to mess around with it: 
select jobmst_name as 'Job Name', jobrun.jobrun_Time as 'Job Run Time', dbo.jobdep.jobdep_jobmst as 'Job dependency Name ID' from dbo.jobmst with (nolock)
-- Nolock prevents the query from locking your database. If you mess up , your DB will still process data giving you time to cancel your query
INNER JOIN dbo.jobrun ON jobmst.jobmst_id = jobrun.jobmst_id Left Outer Join dbo.jobdep ON dbo.jobmst.jobmst_id = dbo.jobdep.jobmst_id
where jobrun_status = 103 and jobrun.jobmst_type <> 1 and jobrun.jobrun_time >= '4/25/2022 7:00 AM' and jobrun.jobrun_time <= '5/31/2022 12:00 PM'
-- if you don't need the timeframe remove or adjust. to get older data, use jobrun_statusx table
and jobdep_jobmst = 1234
-- If you want to group, unhash this line -- group by jobmst_name, jobrun_time, jobdep_id, jobdep_jobmst

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community:

Recognize Your Peers