//Get list of scheduled MR batch job
USE ManagementReporter
SELECT
Trig.Id,
Task.Name,
Trig.Interval,
CASE Trig.[UnitOfMeasure]
WHEN 1 THEN 'Seconds'
WHEN 2 THEN 'Minutes'
WHEN 3 THEN 'Hours'
WHEN 4 THEN 'Days'
End as UnitOfMeasure
FROM [Connector].[Map] Map
Inner join [Scheduling].[Task] Task
on Map.MapId = Task.Id
Inner join [Scheduling].[Trigger] Trig
on Task.TriggerId = Trig.Id
ORDER BY Name desc
//Get Current Batch status of AX to MR integration data sync
USE ManagementReporter
SELECT T.NAME,
CASE TS.STATETYPE
WHEN 3 THEN 'PROCESSING'
WHEN 5 THEN 'COMPLETE'
WHEN 7 THEN 'ERROR'
END AS STATETYPE,
TS.PROGRESS,
TS.LastRunTime as LastRun,
TS.NextRunTime as nextRun,
T.Id as TaskId,
T.TYPEID as TypeId,
T.ID AS TRIGGERID,
CASE TR.ISENABLED
WHEN 1 THEN 'ENABLED'
ELSE 'DISABLED'
END AS NAMESTATUS,
TR.INTERVAL,
CASE TR.UNITOFMEASURE
WHEN 2 THEN 'MINUTES'
ELSE 'SECONDS'
END AS INTERVALTIMING,
TS.STATETYPE
FROM SCHEDULING.TASK T JOIN SCHEDULING.TASKSTATE TS
ON T.ID = TS.TASKID JOIN SCHEDULING.[TRIGGER] TR ON TR.ID = T.TRIGGERID
WHERE ISENABLED <> 0
AND T.TYPEID in ('55D3F71A-2618-4EAE-9AA6-D48767B974D8',
'6F6B935B-FC0A-46B9-8F53-27C6AF7437F0',
'D81C1197-D486-4FB7-AF8C-078C110893A0')
ORDER BY T.NAME
//Get MR schedule error list
select
b.Name,
c.Text,
DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), a.StartTime) as LocalStartTime,
DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), a.EndTime) as LocalEndTime,
a.TotalRetryNumber,
a.IsFailed,
d.Name as TaskType
from [Scheduling].[Log] a
inner join [Scheduling].[Task] b on a.TaskId = b.Id
inner join [Scheduling].[Message] c on a.Id = c.LogId
inner join [Scheduling].[TaskType] d on b.TypeId = d.Id
where a.StartTime > (select DATEADD(DAY, -8, GETDATE()))
and c.Text like '%Error%'
order by LocalStartTime desc