How to find timeout duration for the Work Incident task for existing Incidents?
When the configurable WorkIncidentTaskTimeoutInDays value is changed, the change applies to newly created Incidents but not existing ones.
This question is more tricky that it appears at first. There is no easy place to check this. Timeout date for each Work Incident task is calculated just before starting task itself. This timeout date is not shown anywhere in the Portal and while it can be found in the database, querying the value needs a bit of doing.
Once you have identified problematic incidents, do not try to change the values in the database. This is likely to break things. The way to address less than expected timeouts from Portal is to put Incident on hold and then take it out of hold. This will result in a new Work Incident task and a new timeout on that.
TOP 100 was intentionally left in both SELECT queries. The queries are not optimized and are not light to run as the tables involved are both large and busy. Please make sure queries work for you on a smaller set before extending the amount of results.
Timeout date for a task is saved as a trigger date in properties of the associated message in the database. Querying that is not too bad and can be done with a query like this:
SELECT TOP 100 t.WFTaskNumberPrefix AS [Incident ID], t.AssignedDate AS [Started Date], mp.AttributeNumValue AS [Timeout in .NET Ticks] FROM MessageProperties mp LEFT JOIN Task t ON mp.messageID = t.TaskID WHERE AttributeKey = 'TRIGGER_DATE' AND QueueName='local.workflowsqlexchange-incident_mgmt.tasks'
Here comes the problem - timeout date format there is .NET ticks. To get this date to a readable and usable datetime format, adding a function to the database is the easiest way:
CREATE FUNCTION dbo.ToDateTime2 ( @Ticks bigint ) RETURNS datetime AS BEGIN DECLARE @DateTime datetime2 = '00010101'; SET @DateTime = DATEADD( DAY, @Ticks / 864000000000, @DateTime ); SET @DateTime = DATEADD( SECOND, ( @Ticks % 864000000000) / 10000000, @DateTime ); SET @DateTime = DATEADD( NANOSECOND, ( @Ticks % 10000000 ) * 100, @DateTime ); RETURN CAST(@DateTime AS datetime); END
After this function is added to the database, the timeout value can be converted to datetime in the query which will both give a readable date as well as enable some calculations to make it easier to find what we are looking for. Here is the query from above with two additional columns - timeout date in a readable format and calculated difference between the time task was started and timeout date in days. The last column should be the needed value:
SELECT TOP 100 t.WFTaskNumberPrefix AS [Incident ID], t.AssignedDate AS [Started Date], mp.AttributeNumValue AS [Timeout in .NET Ticks], [dbo].[ToDateTime2](mp.AttributeNumValue) AS [Timeout Date], DATEDIFF(day,t.AssignedDate,[dbo].[ToDateTime2](mp.AttributeNumValue)) AS [Timeout Days] FROM MessageProperties mp LEFT JOIN Task t ON mp.messageID = t.TaskID WHERE AttributeKey = 'TRIGGER_DATE' AND QueueName='local.workflowsqlexchange-incident_mgmt.tasks'
You can add to the WHERE clause of the last query to filter out the Incidents where timeout is less than 365 days, for example by adding this to the end of WHERE clause to filter results to only Incidents with timeout time less than 370 days:
AND DATEDIFF(day,t.AssignedDate,[dbo].[ToDateTime2](mp.AttributeNumValue)) < 370
Subscribing will provide email updates when this Article is updated. Login is required.