SQL query of Templates & connected SR/IRs
Liz_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
-
Options
Justin_Workman
Cireson Support Super IT Monkey ✭✭✭✭✭
@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.
1 -
OptionsJustin_Workman
Cireson Support Super IT Monkey ✭✭✭✭✭
Liz - This is just Request Offerings/Templates. It will display all work item types.
1 -
OptionsJustin_Workman
Cireson Support Super IT Monkey ✭✭✭✭✭
@Liz_Ross - updated query to include MP name where the RO lives:
select ro.DisplayName,
sta.DisplayName as 'Status',
so.DisplayName as 'ServiceOffering',
td.DisplayName as 'Template',
bme.LastModified as 'LastModified',
mp.MPFriendlyName
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'
join ManagementPack mp
on ro.Internal_ManagementPackId_632F11E2_C888_9C4B_95F6_DA4D83B09E16 = mp.ManagementPackId
5
Answers
Try looking in Views, dbo.DisplayStringView contains the mapping of Ids and DisplayName.
Thank you!! I will try that out :) :)
@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.
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?
Liz - This is just Request Offerings/Templates. It will display all work item types.
Oh I gotcha, thanks!!
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_Ross - updated query to include MP name where the RO lives:
select ro.DisplayName,
sta.DisplayName as 'Status',
so.DisplayName as 'ServiceOffering',
td.DisplayName as 'Template',
bme.LastModified as 'LastModified',
mp.MPFriendlyName
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'
join ManagementPack mp
on ro.Internal_ManagementPackId_632F11E2_C888_9C4B_95F6_DA4D83B09E16 = mp.ManagementPackId
You TOTALLY made my day again thank you so much!!! I have all the info I need!!! You are awesome thank you Justin!!!
Glad I could help! 😊