-- Autor: Dariusz Brejnak -- Procedura, która listuje joby po filtrze @JobName z zakresu @Hour ostatnich godzin i wyświetla @rankning ostatnich wywołań. -- W szybki spób mamy podgląd na to czy jakiś krok sie nie wywalił w jobie. -- Można mieć też podgląd na tabelę przejściową (opcja @temptable = 1) USE [DBA] GO declare @Hour INT = 24 -- ile godzin wstecz maja byc zbierane dane ,@Ranking INT = 5 -- 0 - wszystkie - ilość ostatnich uruchomień jobów ,@JobName NVARCHAR(100) = 'NPM$|RDS' -- filtr na nazwę joba ,@TempTable smallint = 0 -- 1 PIVOT 0 Temp table 2 raporty ,@LinkedServer NVARCHAR(128) = @@SERVERNAME ,@FailureOnly bit = 0 -- 0 wszystkie 1 - tylko z błędami SET NOCOUNT ON DECLARE @maxSteps INT ,@Sql NVARCHAR(max) ,@sql1 NVARCHAR(max) ,@sql2 NVARCHAR(max) ,@i INT = 1 IF @ranking = 0 SET @Ranking = 9999 SET @LinkedServer = '['+@LinkedServer+'].' IF OBJECT_ID('tempdb..##Jobs') IS NOT NULL DROP TABLE ##Jobs; SET @sql = N' WITH CTE AS ( SELECT t3.instance_id ,t1.NAME AS JobName ,t1.job_id ,t3.ExecutionJob_id ,MAX(t3.ExecutionJob_id) OVER (PARTITION BY t1.job_id) - t3.ExecutionJob_id + 1 AS ranking ,t3.step_id AS StepID ,t3.step_name AS StepName ,CONVERT(CHAR(10), CAST(STR(t3.run_date, 8, 0) AS DATETIME), 112) AS RunDate ,STUFF(STUFF(RIGHT(''000000'' + CAST(t3.run_time AS VARCHAR(6)), 6), 5, 0, '':''), 3, 0, '':'') AS RunTime ,STUFF(STUFF(RIGHT(''000000'' + CAST(t3.run_duration AS VARCHAR(6)), 6), 5, 0, '':''), 3, 0, '':'') as Duration ,CASE t3.run_status WHEN 0 THEN ''F A I L E D !!!'' WHEN 1 THEN ''Succeeded'' WHEN 2 THEN ''Retry'' WHEN 3 THEN ''Cancelled'' WHEN 4 THEN ''In Progress'' END AS ExecutionStatus ,t3.run_status ,t3.message AS MessageGenerated FROM '+@LinkedServer+'[msdb].[dbo].[sysjobs] t1 INNER JOIN ( SELECT instance_id ,[job_id] ,[step_id] ,[step_name] ,[run_date] ,[run_time] ,[run_status] ,[run_duration] ,[message] ,SUM(CASE step_id WHEN 0 THEN 1 ELSE 0 END) OVER ( PARTITION BY [job_id] ORDER BY [run_date] ,[run_time] ,step_id rows BETWEEN unbounded preceding AND CURRENT row ) AS ExecutionJob_id FROM '+@LinkedServer+'[msdb].[dbo].[sysjobhistory] WHERE 1 = 1 AND cast(cast(run_date AS VARCHAR(10))+'' ''+STUFF(STUFF(RIGHT(''000000'' + CAST(run_time AS VARCHAR(6)), 6), 5, 0, '':''), 3, 0, '':'') AS DATETIME) >= CAST( dateadd(hh, - @Hour, getdate()) AS DATETIME) ) t3 ON t3.job_id = t1.job_id ), CTE2 as ( SELECT *, DATEADD(second,DATEPART(second,cast(cte.Duration as time(0))),DATEADD(minute,DATEPART(minute,cast(cte.Duration as time(0))),DATEADD(hour,DATEPART(hour,cast(cte.Duration as time(0))),cast(cte.RunTime as time(0))))) EndTime ,count(job_id) over (Partition by job_id, executionjob_id ) jobcnt ,SUM(IIF(run_status=4,1,run_status)) over (Partition by job_id, executionjob_id ) sumcnt FROM cte ) SELECT * INTO ##Jobs FROM cte2 WHERE Ranking <= @Ranking and dba.tools.RegexIsMatch(JobName, @JobName,0)=1 '+IIF(@FailureOnly=0,'',' and jobcnt<>sumcnt') +' ORDER BY JobName ,rundate DESC ,runtime DESC ,stepID DESC ' /* exec sp_executesql @sql , N'@Hour INT, @Ranking INT, @JobName NVARCHAR(100), @FailureOnly bit' , @Hour = @Hour, @Ranking = @Ranking, @JobName= @JobName, @FailureOnly = @FailureOnly */ /* select * into dba.dbo.Darek_jobs from ##Jobs */ select * into ##Jobs from dba.dbo.Darek_jobs IF @TempTable = 0 BEGIN SELECT @maxSteps = max(stepid) FROM ##Jobs SET @Sql = N' Select JobName as JobName, StepName,RunDate, RunTime, EndTime, IIF([0] is null, '''',IIF([0]=0,''ERROR'', ''OK'')) Job ' SET @sql1 = N'' SET @sql2 = N'[0], ' WHILE @i <= @maxSteps BEGIN SET @sql1 = @sql1 + ' , IIF([' + cast(@i AS NVARCHAR(2)) + '] is null, '''',IIF([' + cast(@i AS NVARCHAR(2)) + ']=0,''ERROR'', ''OK'')) Step' + ltrim(cast(@i AS NVARCHAR(2))) SET @sql2 = @sql2 + '[' + cast(@i AS NVARCHAR(2)) + '], ' SET @i = @i + 1 END SELECT @sql = @sql + @sql1 + ' from (select JobName,Run_status, StepID ,RunDate, RunTime, StepName, EndTime from ##Jobs ) as source PIVOT (sum(run_status) FOR StepID IN ( ' + left(@sql2, len(@sql2) - 1) + ') ) As PivotTable order by jobname, rundate desc,runtime desc, IIF(StepName=''(Job outcome)'',runtime,endtime) desc, IIF(StepName=''(Job outcome)'',9,0)' EXEC sp_executesql @sql END ELSE IF @TempTable = 1 BEGIN SELECT *,mds.mdq.RegexIsMatch(JobName, @JobName,0) Matched FROM ##Jobs ORDER BY jobname, instance_id DESC END ELSE IF @TempTable = 2 BEGIN SELECT JobName as JobName, StepID, StepName,RunDate, RunTime, EndTime, Duration, ExecutionStatus FROM ##Jobs ORDER BY jobname, instance_id DESC END