Run SQL query on custom field (IR)

Philip_Håkans

Hello!

We have recently added a new custom field on our Incident Form called "Service" and added a bunch of our Services there.

Now I want to run some statistics to see how many Incidents X Service gets but I am having issues writing the query.

I think I have managed to find the correct table and column in the database:

ServiceManager.dbo.MT_ClassExtension_acdc1d27_decb_47d9_831b_8f84913d61a7

The column:

SKService_292E992A_7BD8_7E84_B575_50B8C46CC101

But I am not sure how to join this table to the WorkItemID table.

SELECT DISTINCT wi.WorkItemID as ID,

wi.Title as Titel,

wi.AssignedUser as 'Assigned to',

enum.DisplayString as 'Supportgrupp',

wi.AffectedUser as 'Affected User',

wi.Description as Beskrivning,

userSektor.Office as 'Sektor',

userSektor.Department as 'Enhet',

wi.Created as Skapad,

resolvedby.DisplayName as 'Löst av',

wi.ResolvedDate as 'Löst Datum',

wi.Source as 'Källa',

tjänst.SKService_292E992A_7BD8_7E84_B575_50B8C46CC101 as Tjänst,

category.DisplayString as Kategori,

c.DisplayString as Stängningskategori,

wi.ResolutionDescription as Lösning

FROM CiresonAnalytics.dbo.SM_WorkItem_Incident WI

LEFT JOIN [SM_DisplayString] enum ON enum.ElementID = wi.TierId

LEFT JOIN [SM_DisplayString] c ON c.ElementID = wi.ResolutionCategory

LEFT JOIN [SM_DisplayString] category ON category.ElementID = wi.CategoryId

LEFT JOIN ServiceManagement.dbo.[CI$User] usrr ON usrr.id = wi.AffectedUserId

LEFT JOIN ServiceManagement.dbo.[CI$User] resolvedby ON wi.AssignedUserId = resolvedby.Id

LEFT JOIN ServiceManagement.dbo.[CI$User] userSektor ON wi.AffectedUserId = userSektor.Id

LEFT JOIN ServiceManager.dbo.MT_ClassExtension_acdc1d27_decb_47d9_831b_8f84913d61a7 tjänst ON tjänst.SKService_292E992A_7BD8_7E84_B575_50B8C46CC101 = ????

I have managed to get some results but the field is not readable, for instance SKService comes out as "dc17efa5-0b5e-5041-715b-27fb303fc1b4"

Any ideas how to join or am I looking for the custom field in the wrong place ?

Thanks in advance!

steve_tuel

You need one more JOIN then:

LEFT OUTER JOIN ServiceManager.dbo.DisplayStringView DS ON

DS.LTStringId = tjänst.insert your list value column here

Then in the SELECT section add DS.DisplayName

Philip_Håkans

Thank you Steve, that solved my problem!

steve_tuel

You need this to properly JOIN:  

LEFT JOIN ServiceManager.dbo.MT_ClassExtension_acdc1d27_decb_47d9_831b_8f84913d61a7 tjänst ON tjänst.BaseManagedEntityId = WI.Id

Then you would add the Column Name in the SELECT portion of your query.

Philip_Håkans

Thank you for your quick reply Steve!

While it did help me some I still have the issue that I get unintelligible results. I see, what I assume are some GUID:s?

For instance I get this:

[Tjänst (Service)]

D47EE2A6-CB1D-7D3E-857D-E59F07EFCAB9
D47EE2A6-CB1D-7D3E-857D-E59F07EFCAB9
D47EE2A6-CB1D-7D3E-857D-E59F07EFCAB9
D47EE2A6-CB1D-7D3E-857D-E59F07EFCAB9
F0305681-BCA7-B72A-D593-8BD756B72778
F0305681-BCA7-B72A-D593-8BD756B72778

Instead of the service names in clear text.

It should look something like this:

[Tjänst (Service)]

Service1
Service1
Service1
Service1
Service2
Service2
etc

I added:

tjänst.SKService_292E992A_7BD8_7E84_B575_50B8C46CC101 as Tjänst,

To my SELECT

My FROM:

FROM CiresonAnalytics.dbo.SM_WorkItem_Incident WI

LEFT JOIN [SM_DisplayString] enum ON enum.ElementID = wi.TierId

LEFT JOIN [SM_DisplayString] c ON c.ElementID = wi.ResolutionCategory

LEFT JOIN [SM_DisplayString] category ON category.ElementID = wi.CategoryId

LEFT JOIN ServiceManagement.dbo.[CI$User] usrr ON usrr.id = wi.AffectedUserId

LEFT JOIN ServiceManagement.dbo.[CI$User] resolvedby ON wi.AssignedUserId = resolvedby.Id

LEFT JOIN ServiceManagement.dbo.[CI$User] userSector ON wi.AffectedUserId = userSector.Id

LEFT JOIN ServiceManager.dbo.MT_ClassExtension_acdc1d27_decb_47d9_831b_8f84913d61a7 tjänst ON tjänst.BaseManagedEntityId = WI.Id

steve_tuel

Does the Class Extension point to a List Value or CI?

Philip_Håkans

It should point to a list in our ServiceManager called "Service" however I was not the one who built the form. But to add or remove services to the field I open the Library and add items to the list called "Service" MP skovde - Incident Management - Custom Extensions

steve_tuel

You need one more JOIN then:

LEFT OUTER JOIN ServiceManager.dbo.DisplayStringView DS ON

DS.LTStringId = tjänst.insert your list value column here

Then in the SELECT section add DS.DisplayName

Philip_Håkans

Thank you Steve, that solved my problem!