I have incidents getting stuck in SLOStatus = Active when the incident is resolved.

Alan_Foster

I have incidents getting stuck in SLOStatus = Active when the incident is resolved, the status should move to SLOStatus = Met.
Has anyone else seen this?  How do I troubleshoot?

Brian_Wiest

You are running the report off the SQL query targeting the ServiceManagement DB (Cireson) 
As I started above the portal cache builder does NOT update that value unless the work item was updated. 
Meaning if you check IR290304 in the console and review the SLA the expectation is that it will show status of Met. Then if you save IR290304 so the last modified is updated you will find that the SLA status will get replicated into the portal thus updating the report. 

Here is an example of the similar query off the ServiceManager DB. It does not have the caching issues. 

 select 

 I.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C IRID, 

 l.LTValue,

 e2.EnumTypeName,

 i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as [CreateDate],

 du2.DisplayName,

 du3.DisplayName,

 du3.Department_312201FE_C1B3_E95A_01DF_E132E9BD3EC9 as [Department]

 

 from [MT_System$WorkItem$Incident] I (nolock) 

  left join [Relationship] r (nolock) on I.BaseManagedEntityId=r.SourceEntityId 

left join [MT_System$SLA$Instance$TimeInformation] s (nolock) on s.BaseManagedEntityId=r.TargetEntityId 

left join [EnumType] e (nolock) on e.EnumTypeId=s.Status_F632EA19_0367_CC81_BCB5_5E0387B1B5E3 

left join LocalizedText l (nolock) on l.ElementName=e.EnumTypeName 

left join [MT_System$SLA$Configuration] sc (nolock) on sc.DisplayName=s.DisplayName 

  --System.WorkItemAssignedToUser-- 

left join Relationship r2 (nolock) ON I.BaseManagedEntityId = r2.SourceEntityId and r2.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' and r2.IsDeleted = 0 

left join MT_System$Domain$User du2 (nolock) on du2.BaseManagedEntityId=r2.TargetEntityId 

 --System.WorkItemAffectedUser-- 

left join Relationship r3 (nolock) ON I.BaseManagedEntityId = r3.SourceEntityId and r3.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' and r3.IsDeleted = 0 

left join MT_System$Domain$User du3 (nolock) on du3.BaseManagedEntityId=r3.TargetEntityId 

 --Support Group TierQueue--

left JOIN EnumType e2 (nolock) ON e2.EnumTypeId=I.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C

where l.LanguageCode='ENU' 

Adam_Dzyacky

Could you share how you've configured the SLO(s) ?

Alan_Foster

in SLM,
Calendar:  M-F 7a-5p,
Metric:  Created date - Resolved date,
SLO:  Desktop Support queue - Resolution Time target 4 hours - Warning 1 hour. 

The reports depend on the SLOStatus field in the service management DB. 
It works correctly if an incident breaches but gets stuck if Met.  Below is a query of the Workitems that are resolved today in less than 240 minutes.  The status should be SLAInstance.Status.Met.


Two hours later and they are still in Active status.

Since submitting the original request I have submitted this to Microsoft and also a formal request to Cireson.

Brian_Wiest

Please define more about the differences in Met and Active.
The screenshot is reporting and looks like it could possibly be a SQL query return. 
Also are you talking about the status in the portal?

The best place for monitoring SQL status is in the console looking at a specific work item SLA tab. 
Here you can see what is the current status of an SLA.

Some notes 
If pulling from a SQL query it depends on where you are pulling. if pulled from the incorrect source there could be rows for each status of the SLA as it progresses thru the different stages.

The portal DOES NOT update the SLA on the GUID views. I have found that the GUID only will update the status of an SLA at last save. If that last save resolves the request the SLA workflow about a minute later will update to the expected value but the GUID will not update as nothing saved the work item after the SLA updates. 

HTH

Alan_Foster

yes, it is from this SQL query:
SELECT  WorkItemId, SLOStatus, StatusDisplay.DisplayString AS 'Status', DATEDIFF(minute,Created,ResolvedDate)AS 'Min', DATEADD(hour,-5, ResolvedDate) AS 'Resolved Date'

FROM [ServiceManagement].[dbo].[WorkItem] 

JOIN [dbo].[DisplayString]AS StatusDisplay ON StatusDisplay.ElementID = WorkItem.StatusId -- Support Group

WHERE WorkItemId LIKE '%IR%' -- only incidents

AND TierID IN ('140463F8-8C4D-B4B4-E074-A9A3AFB23B59')   -- Desktop Support Team

AND StatusID IN ('BD0AE7C4-3315-2EB3-7933-82DFC482DBAF','2B8830B6-59F0-F574-9C2A-F4B4682F1681')  -- Resolved,Closed

AND DATEDIFF(minute,Created,ResolvedDate) < 240 -- minutes to resolved

AND NOT SLOStatus = 'NULL'

ORDER BY SLOStatus

WorkItemId SLOStatus                          Status       Min Resolved Date

IR290304    SLAInstance.Status.Active Resolved  34   2018-06-27 11:11:28.000

IR290217    SLAInstance.Status.Active Resolved  24   2018-06-27 08:21:17.000

IR290339    SLAInstance.Status.Active Resolved  11   2018-06-27 13:00:41.000

IR290273    SLAInstance.Status.Met     Resolved  14   2018-06-27 10:05:43.000

IR290219    SLAInstance.Status.Met     Resolved  4     2018-06-27 08:09:05.000

IR290142    SLAInstance.Status.Met     Resolved  38   2018-06-27 06:34:34.000

IR290225    SLAInstance.Status.Met     Resolved  15   2018-06-27 08:41:46.000

IR290198    SLAInstance.Status.Met     Resolved  52   2018-06-27 08:23:48.000

IR290335    SLAInstance.Status.Met     Resolved  7     2018-06-27 12:07:47.000

IR290274    SLAInstance.Status.Met     Resolved  52   2018-06-27 10:42:55.000

IR290248    SLAInstance.Status.Met     Resolved  95   2018-06-27 10:52:50.000

All of these where resolved today and the first three still have not flipped to Status = Met.

I have a report in SSRS that uses the SLOStatus column from Workitem table in the ServiceManagement DB.

Brian_Wiest

You are running the report off the SQL query targeting the ServiceManagement DB (Cireson) 
As I started above the portal cache builder does NOT update that value unless the work item was updated. 
Meaning if you check IR290304 in the console and review the SLA the expectation is that it will show status of Met. Then if you save IR290304 so the last modified is updated you will find that the SLA status will get replicated into the portal thus updating the report. 

Here is an example of the similar query off the ServiceManager DB. It does not have the caching issues. 

 select 

 I.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C IRID, 

 l.LTValue,

 e2.EnumTypeName,

 i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as [CreateDate],

 du2.DisplayName,

 du3.DisplayName,

 du3.Department_312201FE_C1B3_E95A_01DF_E132E9BD3EC9 as [Department]

 

 from [MT_System$WorkItem$Incident] I (nolock) 

  left join [Relationship] r (nolock) on I.BaseManagedEntityId=r.SourceEntityId 

left join [MT_System$SLA$Instance$TimeInformation] s (nolock) on s.BaseManagedEntityId=r.TargetEntityId 

left join [EnumType] e (nolock) on e.EnumTypeId=s.Status_F632EA19_0367_CC81_BCB5_5E0387B1B5E3 

left join LocalizedText l (nolock) on l.ElementName=e.EnumTypeName 

left join [MT_System$SLA$Configuration] sc (nolock) on sc.DisplayName=s.DisplayName 

  --System.WorkItemAssignedToUser-- 

left join Relationship r2 (nolock) ON I.BaseManagedEntityId = r2.SourceEntityId and r2.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' and r2.IsDeleted = 0 

left join MT_System$Domain$User du2 (nolock) on du2.BaseManagedEntityId=r2.TargetEntityId 

 --System.WorkItemAffectedUser-- 

left join Relationship r3 (nolock) ON I.BaseManagedEntityId = r3.SourceEntityId and r3.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' and r3.IsDeleted = 0 

left join MT_System$Domain$User du3 (nolock) on du3.BaseManagedEntityId=r3.TargetEntityId 

 --Support Group TierQueue--

left JOIN EnumType e2 (nolock) ON e2.EnumTypeId=I.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C

where l.LanguageCode='ENU'