"servicemanage" DB query - SLA

Ozgur_Sahin

I have a query for SLA

 select I.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C IRID, l.LTValue from [MT_System$WorkItem$Incident] I (nolock)
inner join [Relationship] r (nolock) on I.BaseManagedEntityId=r.SourceEntityId
 inner join [MT_System$SLA$Instance$TimeInformation] s (nolock) on s.BaseManagedEntityId=r.TargetEntityId
 inner join [EnumType] e (nolock) on e.EnumTypeId=s.Status_F632EA19_0367_CC81_BCB5_5E0387B1B5E3
 inner join LocalizedText l (nolock) on l.ElementName=e.EnumTypeName
 inner join [MT_System$SLA$Configuration] sc (nolock) on sc.DisplayName=s.DisplayName
 where l.LanguageCode='ENU'



 I want to add other columns like “Assigned User”, “Affected User”,“Support Group” Can you help me how can I do it ?

Olena_Prychyna

just add:

--System.WorkItemAssignedToUser-- left outer join Relationship r2 (nolock) ON I.BaseManagedEntityId = r2.SourceEntityId and r2.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' and r2.IsDeleted = 0 inner join MT_System$Domain$User du2 (nolock) on du2.BaseManagedEntityId=r2.TargetEntityId --System.WorkItemAffectedUser-- left outer join Relationship r3 (nolock) ON I.BaseManagedEntityId = r3.SourceEntityId and r3.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' and r3.IsDeleted = 0 inner join MT_System$Domain$User du3 (nolock) on du3.BaseManagedEntityId=r3.TargetEntityId --Support Group - TierQueue inner JOIN EnumType e2 (nolock) ON e2.EnumTypeId=I.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C

Olena_Prychyna

just add:

--System.WorkItemAssignedToUser-- left outer join Relationship r2 (nolock) ON I.BaseManagedEntityId = r2.SourceEntityId and r2.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' and r2.IsDeleted = 0 inner join MT_System$Domain$User du2 (nolock) on du2.BaseManagedEntityId=r2.TargetEntityId --System.WorkItemAffectedUser-- left outer join Relationship r3 (nolock) ON I.BaseManagedEntityId = r3.SourceEntityId and r3.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' and r3.IsDeleted = 0 inner join MT_System$Domain$User du3 (nolock) on du3.BaseManagedEntityId=r3.TargetEntityId --Support Group - TierQueue inner JOIN EnumType e2 (nolock) ON e2.EnumTypeId=I.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C

Scott_Wright

Do you have an example of what this would look like put together?  I tried appending (I'm not a SQL expert), but I still only get the two fields to display.

Olena_Prychyna

@Scott_Wright :  it will be something like this - see attached

Scott_Wright

Looks great! Thanks for your help.  Is there a method to display the Support Group name, rather than the Enum GUID?

Olena_Prychyna

EnumTypeName is a name

Scott_Wright

What I'm looking for is displaying the Support Tier name in the table, rather than the enum.  Is this possible?  Thanks in advance, you have helped me a great deal already.

Olena_Prychyna

Instead of join EnumType you can do:

SELECT ...     ,Display.DisplayName As Tier     ....

JOIN DisplayStringView Display ON I.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = Display.MPElementId

Scott_Wright

That worked!  Thank you for all your help!