none
SQL Query On Incidents - Trying To Find Friendly Name for Tier Queue GUID

    Question

  • I'm running a SQL query on the MT_System$WorkItem$Incident table and it is returning GUIDs for the TierQueue_1E9615.... etc. column.

    I'm not able to find the GUIDs that are returned anywhere in other tables.  I've read the blog posts on using smlets to find display names from enumerations or exporting the management pack but this particular list/enum is in a sealed management pack.

    is there any way I can find the display names that match the GUIDS in the work item table?

    thanks,
    Dan

    Thursday, September 29, 2011 11:58 PM

Answers

  • What Patrik said.

    Below is the same query as Patriks really, just that I joined Incident and DisplayStringView together.

    SELECT 
    	IR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C,
    	IR.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104,
    	Display.DisplayName As Tier
    FROM MT_System$WorkItem$Incident IR
    JOIN DisplayStringView Display
    ON IR.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = Display.MPElementId
    WHERE Display.LanguageCode = 'ENU'
    

    But I'm wondering why you are running SQL queries towards the ServiceManager database; what's the purpose?

    Regards
    //Anders

     


    Anders Asp | Lumagate | www.lumagate.com | Sweden | My blog: www.scsm.se
    • Marked as answer by danwheeler Monday, October 03, 2011 6:33 PM
    Monday, October 03, 2011 7:30 AM

All replies

  • Hi danwheeler,

    You could use the view called "DisplayStringView"

    SELECT TOP 1000 *
      FROM [ServiceManager].[dbo].[DisplayStringView]
      Where MPElementId = 'DF3896F5-3145-0546-4D25-E485DE6765AF'
      And LanguageCode = 'ENU'
    

    The MPElementID should relate to the GUIDs found in among other columns "TierQueue_1E9615...."

    Either use the view as in my example or join the view with the table your talking about.

    Let me know how it goes!


    Patrik Sundqvist Solutions Architect @ Gridpro AB Blog: http://blogs.litware.se
    Friday, September 30, 2011 5:12 AM
  • What Patrik said.

    Below is the same query as Patriks really, just that I joined Incident and DisplayStringView together.

    SELECT 
    	IR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C,
    	IR.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104,
    	Display.DisplayName As Tier
    FROM MT_System$WorkItem$Incident IR
    JOIN DisplayStringView Display
    ON IR.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = Display.MPElementId
    WHERE Display.LanguageCode = 'ENU'
    

    But I'm wondering why you are running SQL queries towards the ServiceManager database; what's the purpose?

    Regards
    //Anders

     


    Anders Asp | Lumagate | www.lumagate.com | Sweden | My blog: www.scsm.se
    • Marked as answer by danwheeler Monday, October 03, 2011 6:33 PM
    Monday, October 03, 2011 7:30 AM
  • guys, thanks a ton that works awesome!

    Anders, I am building some weekly report charts in Excel.  It's a lot easier than dealing with SSRS and getting the DW involved.  Basically I'm trying to re-create the incident KPI report line graph but with separate lines for each team overlayed on top of each other.

    thanks again, guys

    Dan

    Monday, October 03, 2011 6:33 PM
  • Hi,

    My question is How do i get the list Incident using MT_System$WorkItem$Incident...

    My requirement is, i want to fetch the list of all the incident together with ASSIGNEDTO and AFFECTEDUSER and COMPANY...The table which is in ServiceManager MT_System$WorkItem$Incident  does not have ASSIGNEDTO and AFFECTEDUSER name..

    For example, if i create a new incident, that entry will go into MT_System$WorkItem$Incident  table but that table does not contains the ASSIGNEDTO and AFFECTEDUSER name...So how do i create the relationship between these two field and MT_System$WorkItem$Incident  table..

    Can you please tell me how do i get that information..

    Thanks,

    Jignesh

    Tuesday, March 06, 2012 5:13 PM
  • Jignesh, you'll want to use the Relationship table in conjunction with the RelationshipType table to find relationships between SM objects. This blog post should help you out
    http://blogs.technet.com/b/servicemanager/archive/2009/12/31/service-manager-database-tour-useful-queries.aspx

    Tuesday, March 06, 2012 6:45 PM
  • Thanks for your help...

    Just one more question..We have set up the email notification when someone either close or resolve the incidents, email will go to the affected user person..but someone email does not go..Can you please tell where should i look for this issue..

    I appreciate your help..

    Thanks,

    Jignesh

    Wednesday, March 28, 2012 3:02 PM
  • What Patrik said.

    Below is the same query as Patriks really, just that I joined Incident and DisplayStringView together.

    SELECT 
    	IR.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C,
    	IR.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104,
    	Display.DisplayName As Tier
    FROM MT_System$WorkItem$Incident IR
    JOIN DisplayStringView Display
    ON IR.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = Display.MPElementId
    WHERE Display.LanguageCode = 'ENU'
    

    But I'm wondering why you are running SQL queries towards the ServiceManager database; what's the purpose?

    Regards
    //Anders

     


    Anders Asp | Lumagate | www.lumagate.com | Sweden | My blog: www.scsm.se

    Hi Anders,

    This query is very useful but what if I want to display not only Tier but also status and classification category with a friendly name?

    They are all in one column in DisplayStringView.

    EDIT: Ok, I figured it out myself. I had to add more aliases in order to reference DisplayStringView. However, the problem is that this query doesn't return records with empty classification category because there's no match between MPElementID and Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA

    Regards,

    Piotr


    • Edited by Piotrek82 Thursday, June 28, 2012 7:40 AM
    Wednesday, June 27, 2012 7:35 AM