Home SQL Server

SQL query of Templates & connected SR/IRs

Liz_RossLiz_Ross Customer IT Monkey ✭

I'm trying to figure out how to get a report from SQL that can tell me which templates (by name) are connected to which request offerings.

So far I can just pull their "templatetargetID", but not an actual display name. It's like I can't find a table that will give me their actual names. I've been browsing in ServiceManagement and ServiceManager.

We've made a custom management pack for our SRs and one for our IRs, if it's relevant.


Best Answers

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    Accepted Answer

    @Liz_Ross - I built something similar to this in my lab and display it in the portal in a SQL table widget. Here's my query:

    select ro.DisplayName, 

    sta.DisplayName as 'Status',  

    so.DisplayName as 'ServiceOffering', 

    td.DisplayName as 'Template',

    bme.LastModified as 'LastModified'

    from MTV_System$RequestOffering ro

    join Relationship roso

    on roso.TargetEntityId = ro.BaseManagedEntityId

    join MTV_System$ServiceOffering so

    on so.BaseManagedEntityId = roso.SourceEntityId

    join BaseManagedEntity bme

    on ro.BaseManagedEntityId = bme.BaseManagedEntityId

    join DisplayStringView sta

    on sta.LTStringId = ro.Status_17348DC0_DB86_81C4_DC59_5EE105DAF50A

    and sta.LanguageCode = 'enu'

    join ObjectTemplate t

    on t.ObjectTemplateName = cast('<Element>' + REPLACE(ro.TargetTemplate_81C7962D_4A07_4D37_FFAD_501261F93554, '|', '</Element><Element>') + '</Element>' AS XML).value('/Element[4]', 'varchar(50)')

    join DisplayStringView td

    on td.LTStringId = t.ObjectTemplateId

    and td.LanguageCode = 'enu'

    This is against ServiceManager.

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    Accepted Answer

    Liz - This is just Request Offerings/Templates. It will display all work item types.

Answers

  • Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Ninja IT Monkey ✭✭✭✭

    Try looking in Views, dbo.DisplayStringView contains the mapping of Ids and DisplayName.

  • Liz_RossLiz_Ross Customer IT Monkey ✭

    Thank you!! I will try that out :) :)

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    Accepted Answer

    @Liz_Ross - I built something similar to this in my lab and display it in the portal in a SQL table widget. Here's my query:

    select ro.DisplayName, 

    sta.DisplayName as 'Status',  

    so.DisplayName as 'ServiceOffering', 

    td.DisplayName as 'Template',

    bme.LastModified as 'LastModified'

    from MTV_System$RequestOffering ro

    join Relationship roso

    on roso.TargetEntityId = ro.BaseManagedEntityId

    join MTV_System$ServiceOffering so

    on so.BaseManagedEntityId = roso.SourceEntityId

    join BaseManagedEntity bme

    on ro.BaseManagedEntityId = bme.BaseManagedEntityId

    join DisplayStringView sta

    on sta.LTStringId = ro.Status_17348DC0_DB86_81C4_DC59_5EE105DAF50A

    and sta.LanguageCode = 'enu'

    join ObjectTemplate t

    on t.ObjectTemplateName = cast('<Element>' + REPLACE(ro.TargetTemplate_81C7962D_4A07_4D37_FFAD_501261F93554, '|', '</Element><Element>') + '</Element>' AS XML).value('/Element[4]', 'varchar(50)')

    join DisplayStringView td

    on td.LTStringId = t.ObjectTemplateId

    and td.LanguageCode = 'enu'

    This is against ServiceManager.

  • Liz_RossLiz_Ross Customer IT Monkey ✭
    edited October 2019

    Eeee thank you Jason this worked perfectly!!!! Is there any chance you could help me with the same code but for Incident Requests instead of Service Requests?

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    Accepted Answer

    Liz - This is just Request Offerings/Templates. It will display all work item types.

  • Liz_RossLiz_Ross Customer IT Monkey ✭

    Oh I gotcha, thanks!!

  • Liz_RossLiz_Ross Customer IT Monkey ✭

    One last favor, could you include the management pack ID? Even if it's just the GUID and not the "display name" of the pack, It'll just help me separate the SR's from the IR's because we use a different management pack for each so since there's only two it's easy for me to split up that way.

  • Liz_RossLiz_Ross Customer IT Monkey ✭

    You TOTALLY made my day again thank you so much!!! I have all the info I need!!! You are awesome thank you Justin!!!

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    Glad I could help! 😊

Sign In or Register to comment.