Management Reporter scripts

Few scripts that can be used to check the MR integration tasks:

Check all integration tasks that have been executed:

--List details about each DDM task: state, progress, last...

--List details about each DDM task: state, progress, last/next runtime in local time, and the interval that each runs 

--Status 5=Success; 3=Running; 6=Cancelled

select CIG.[Description] 

, STK.[Name] 

, STS.[Progress] 

, CASE STS.[StateType]  

WHEN 3 THEN 'Processing' 

WHEN 5 THEN 'Complete' 

WHEN 7 THEN 'Error' 

END AS StateType

, DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), STS.[LastRunTime]) as LocalLastRunTime 

, DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), STS.[NextRunTime]) as LocalNextRunTime 

, CM.[ContinueOnRecordError] 

, STRG.[Interval] 

, CASE STRG.[UnitOfMeasure] 

WHEN 2 THEN 'Minutes' 

ELSE 'Seconds' 

END AS IntervalTiming

, CASE STRG.[IsEnabled] 

WHEN 1 THEN 'Enabled' 

ELSE 'Disabled' 

END AS NameStatus

from [Connector].[Map] CM with (nolock) 

inner join [Scheduling].[Task] STK with (nolock) on STK.[Id] = CM.[MapId] 

inner join [Scheduling].[TaskState] STS with (nolock) on STK.[Id] = STS.[TaskId] 

inner join [Connector].[IntegrationGroup] CIG with (nolock) on CIG.[IntegrationId] = STK.[CategoryId] 

inner join [Scheduling].[Trigger] STRG with (nolock) on STK.[TriggerId] = STRG.[Id] 

order by CIG.[Description], STK.[Name]

Check the tasks outcomes

select CIG.[Description], ST.[Name], SM.[Text], 

DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), SL.[StartTime]) as LocalStartTime, 

DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), SL.[EndTime]) as LocalEndTime, 

SL.[TotalRetryNumber], SL.[IsFailed], STT.[Name] as TaskType 

from [Scheduling].[Log] SL with (nolock) 

inner join [Scheduling].[Task] ST with (nolock) on SL.TaskId = ST.Id 

inner join [Scheduling].[Message] SM with (nolock) on SL.Id = SM.LogId 

inner join [Scheduling].[TaskType] STT with (nolock) on ST.TypeId = STT.Id 

inner join [Connector].[IntegrationGroup] CIG with (nolock) on CIG.[IntegrationId] = ST.[CategoryId] 

order by SL.[StartTime] desc

Check the current integration activities

SELECT sqltext.TEXT, 

req.session_id, 

req.status, 

req.command, 

req.cpu_time, 

req.total_elapsed_time 

FROM sys.dm_exec_requests req 

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext