Management Reporter : Troubleshooting

Voici deux requêtes SQL qui vous aideront à diagnostiquer des problèmes avec Management Reporter. La première requête retourne le statut des tâches et la deuxième requête retourne les logs.

Tâches

SELECT CIG.[Description] 
, T.Name
, CASE TS.StateType
WHEN 3 THEN 'PROCESSING'
WHEN 5 THEN 'COMPLETE'
WHEN 7 THEN 'ERROR'
ELSE Convert(varchar(max),TS.StateType)
END AS StateType
, TS.Progress
, TR.Id AS TriggerId
, CASE TR.IsEnabled
WHEN 1 THEN 'ENABLED'
WHEN 0 THEN 'DISABLED'
ELSE Convert(varchar(max),TR.IsEnabled)
END AS TriggerStatus
, TR.Interval
, CASE TR.UnitOfMeasure
WHEN 4 THEN 'DAYS'
WHEN 3 THEN 'HOURS'
WHEN 2 THEN 'MINUTES'
WHEN 1 THEN 'SECONDS'
ELSE Convert(varchar(max),TR.UnitOfMeasure)
END AS IntervalTiming
, DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), TS.[LastRunTime]) as LocalLastRunTime
, DATEADD(minute, DATEDIFF(minute,GETUTCDATE(),GETDATE()), TS.[NextRunTime]) as LocalNextRunTime
--, TS.[LastRunTime] as UTCLastRunTime
--, TS.[NextRunTime] as UTCNextRunTime
FROM Scheduling.Task T with (nolock)
JOIN Scheduling.TaskState TS with (nolock) ON T.Id = TS.TaskId 
JOIN Scheduling.[Trigger] TR with (nolock) ON TR.Id = T.TriggerId 
JOIN Connector.IntegrationGroup CIG with (nolock) on CIG.[IntegrationId] = T.CategoryId
WHERE T.TypeId in ('55D3F71A-2618-4EAE-9AA6-D48767B974D8', '6F6B935B-FC0A-46B9-8F53-27C6AF7437F0', 'D81C1197-D486-4FB7-AF8C-078C110893A0')
ORDER BY CIG.[Description], T.Name

Logs

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]
--where st.Name like 'AX 2012 Account Categories to Account Category'
--where st.Name like 'AX 2012 Accounts to Account'
--where st.Name like 'AX 2012 Companies to Company'
--where st.Name like 'AX 2012 Companies to Organization'
--where st.Name like 'AX 2012 Dimension Combinations to Dimension Combination'
--where st.Name like 'AX 2012 Dimension Values to Dimension Value'
--where st.Name like 'AX 2012 Exchange Rates to Exchange Rate'
--where st.Name like 'AX 2012 Fiscal Years to Fiscal Year'
--where st.Name like 'AX 2012 General Ledger Transactions to Fact'
--where st.Name like 'AX 2012 Organization Hierarchies to Tree'
--where st.Name like 'AX 2012 Scenarios to Scenario'
--where st.Name like 'AX 2012 Transaction Type Qualifiers to Fact Type Qualifier'
--where st.Name like 'Maintenance Task'
ORDER BY SL.[StartTime] desc
Previous
« Prev Post