Hopefully this is the right forum. It's fundamentally a T-SQL question.
I'm trying to make a report that is useful for our network sysadmins to monitor the SQL Agent jobs from 23 different servers. They aren't keen on using SSMS to connect to many servers and manually check each job so I'm doing an exception report to try and make their lives easier.
I'm nearly there in that I have the query, and it works, but there is too much detail so I want to collapse the steps into each job-run, but I have no way of grouping the job-runs.
In the MSDB system database there are these useful tables:
This is the query so far. To reproduce this, replace the GUID with the GUID of a job on your SQL Agent - which you can find in [msdb].[sysjobs] table. It makes the problem clearer if you pick a job with more than 1 step.
DECLARE @job_id uniqueidentifier
Set @job_id = '0F70F5A1-C169-4CB4-99D8-A16FC2ED32A0'
SELECT sJOBH.job_id, sJOB.name AS job_title, sJOB.description, sJOBH.step_id, sJOBH.step_name, CASE WHEN [sJOBH].[run_date] IS NULL OR
[sJOBH].[run_time] IS NULL THEN NULL ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8))
+ ' ' + STUFF(STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS DATETIME) END AS RunDateTime,
STUFF(STUFF(RIGHT('000000' + CAST(sJOBH.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS [RunDuration (HH:MM:SS)],
CASE [sJOBH].[run_status] WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'Running' END AS LastRunStatus,
sJOBH.retries_attempted, sJOB.date_modified AS last_modified, sJOBH.message
FROM sysjobhistory AS sJOBH INNER JOIN
sysjobs AS sJOB ON sJOBH.job_id = sJOB.job_id
WHERE (sJOB.enabled = 1)
AND (sJOBH.job_id = @job_id)
ORDER BY job_title, RunDateTime, sJOBH.step_id
I picked up this next query (from SQL Profiler) which appears to drive the SQL Agent job history screen in SSMS. It doesn't make it any clearer how to group the steps of a job into a single "session" i.e. for a particular job there are 9 steps in a job, step number 0 is the job level, the 9 steps that happen just after that are all part of that job.
Note you also need to replace the GUID of this query with one from your system.
declare @tmp_sp_help_jobhistory table
instance_id int null,
job_id uniqueidentifier null,
job_name sysname null,
step_id int null,
step_name sysname null,
sql_message_id int null,
sql_severity int null,
message nvarchar(4000) null,
run_status int null,
run_date int null,
run_time int null,
run_duration int null,
operator_emailed sysname null,
operator_netsent sysname null,
operator_paged sysname null,
retries_attempted int null,
server sysname null
insert into @tmp_sp_help_jobhistory
@job_id = '0f70f5a1-c169-4cb4-99d8-a16fc2ed32a0',
tshj.instance_id AS [InstanceID],
tshj.sql_message_id AS [SqlMessageID],
tshj.message AS [Message],
tshj.step_id AS [StepID],
tshj.step_name AS [StepName],
tshj.sql_severity AS [SqlSeverity],
tshj.job_id AS [JobID],
tshj.job_name AS [JobName],
tshj.run_status AS [RunStatus],
CASE tshj.run_date WHEN 0 THEN NULL ELSE
stuff(stuff(cast(tshj.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' +
stuff(stuff(substring(cast(1000000 + tshj.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'),
120) END AS [RunDate],
tshj.run_duration AS [RunDuration],
tshj.operator_emailed AS [OperatorEmailed],
tshj.operator_netsent AS [OperatorNetsent],
tshj.operator_paged AS [OperatorPaged],
tshj.retries_attempted AS [RetriesAttempted],
tshj.server AS [Server],
getdate() as [CurrentDate]
FROM @tmp_sp_help_jobhistory as tshj
ORDER BY [InstanceID] ASC
In the SSMS job history, it collapses each job run into the job level, and you can drill-down to see all the steps. This is the effect I want to reproduce.
Without using a loop or a cursor (coz they are evil!), how is this done? They must have done this in code because there is no clear way to group the results in the dataset.
There's an instance ID, but it varies for every step. There's a run-date, which would be fine if they were all daily jobs but some run more than once a day.
Sorry for the long intro but basically I think I want to create another column that will group one run of all the steps of a job together, which I could then use to group in the report. Unless there's another option I'm missing. Any ideas? Thanks 🙂