Home› Analyst Portal

PR Join Configuration Item

Ryan_KelleyRyan_Kelley Customer IT Monkey ✭
in Analyst Portal

Hello All!


I am trying to join a configuration item from inside a PR:

image.png


My current query is:


Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS ID,

Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS Title,

Description_59B77FD5_FE0E_D2B5_D541_0EBBD1EC9A2B As Description,

c.DisplayName As HostName,

CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 AS CreateDate,

ClosedDate_C529833E_0926_F082_C185_294CBC8BB9FD AS CompleteDate,

REPLACE(b.EnumTypeName, 'ServiceRequestStatusEnum.', '') As Status

FROM [ServiceManager].[dbo].[MTV_System$WorkItem$Problem] a With (nolock)

JOIN EnumType b WITH (NOLOCK) ON b.EnumTypeId = a.Status_3C8876F4_FCBF_148C_FBAF_4CF4F02C6187


However, I do not see a table or a way to join it in servicemanager.

Thank you for any help!

Best Answer

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

    You need to join the relationship and config item(s) tables. Since you're looking specifically at computers, I'd probably just join the MT_Computer table.

    select p.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C 'ID'

    ,p.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 'Title'

    ,c.DisplayName 'Hostname'

    ,dsv.DisplayName 'Status'

    from MTV_System$WorkItem$Problem p

    join Relationship r

    on r.SourceEntityId = p.BaseManagedEntityId

    and r.RelationshipTypeId = 'D96C8B59-8554-6E77-0AA7-F51448868B43' --System.WorkItemRelatesToConfigItem

    join MT_Computer c

    on c.BaseManagedEntityId = r.TargetEntityId

    join DisplayStringView dsv

    on p.Status_3C8876F4_FCBF_148C_FBAF_4CF4F02C6187 = dsv.LTStringId

    and dsv.LanguageCode = 'ENU'

Answers

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

    You need to join the relationship and config item(s) tables. Since you're looking specifically at computers, I'd probably just join the MT_Computer table.

    select p.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C 'ID'

    ,p.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 'Title'

    ,c.DisplayName 'Hostname'

    ,dsv.DisplayName 'Status'

    from MTV_System$WorkItem$Problem p

    join Relationship r

    on r.SourceEntityId = p.BaseManagedEntityId

    and r.RelationshipTypeId = 'D96C8B59-8554-6E77-0AA7-F51448868B43' --System.WorkItemRelatesToConfigItem

    join MT_Computer c

    on c.BaseManagedEntityId = r.TargetEntityId

    join DisplayStringView dsv

    on p.Status_3C8876F4_FCBF_148C_FBAF_4CF4F02C6187 = dsv.LTStringId

    and dsv.LanguageCode = 'ENU'

  • Ryan_KelleyRyan_Kelley Customer IT Monkey ✭

    Thank you!!! That is what I was missing :)

Sign In or Register to comment.