Home Analytics

Run SQL query on custom field (IR)

Philip_HåkansPhilip_Håkans Customer IT Monkey ✭
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!


Best Answers

  • steve_tuelsteve_tuel Cireson Consultant Adept IT Monkey ✭✭
    Answer ✓
    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åkansPhilip_Håkans Customer IT Monkey ✭
    Answer ✓
    Thank you Steve, that solved my problem! :smiley:

Answers

  • steve_tuelsteve_tuel Cireson Consultant Adept IT Monkey ✭✭
    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åkansPhilip_Håkans Customer IT Monkey ✭
    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_tuelsteve_tuel Cireson Consultant Adept IT Monkey ✭✭
    Does the Class Extension point to a List Value or CI?
  • Philip_HåkansPhilip_Håkans Customer IT Monkey ✭
    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_tuelsteve_tuel Cireson Consultant Adept IT Monkey ✭✭
    Answer ✓
    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åkansPhilip_Håkans Customer IT Monkey ✭
    Answer ✓
    Thank you Steve, that solved my problem! :smiley:
Sign In or Register to comment.