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
