Home Service Manager

"servicemanage" DB query - SLA

Ozgur_SahinOzgur_Sahin Customer IT Monkey ✭
edited January 2017 in Service Manager
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 ?

Best Answer


  • Scott_WrightScott_Wright Customer IT Monkey ✭
    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_PrychynaOlena_Prychyna Customer IT Monkey ✭

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

  • Scott_WrightScott_Wright Customer IT Monkey ✭
    Looks great! Thanks for your help.  Is there a method to display the Support Group name, rather than the Enum GUID?
  • Olena_PrychynaOlena_Prychyna Customer IT Monkey ✭
    EnumTypeName is a name
  • Scott_WrightScott_Wright Customer IT Monkey ✭
    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_PrychynaOlena_Prychyna Customer IT Monkey ✭

    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_WrightScott_Wright Customer IT Monkey ✭
    That worked!  Thank you for all your help!
Sign In or Register to comment.