locked
Querying Operational (Not DW) Database For Active Incidents RRS feed

  • Question

  • I sure hope I am asking this in the correct forum...  Before I get started a little bit of background is needed...

    We (my company) are mostly happy with SCSM but the biggest request we hear about is that there is no easy way for technicians to see their Active/Pending tickets when out at locations.  We know about web analyst portals offered by 3rd parties such as GridPro, Cireson, et al. however the funding (we are in public education) for this has never been realized and the first request to get shot down.  For some reason I wondered if it would be possible to query the operational database myself for a list of incidents to appear and for some reason I decided to try it.  My knowledge of the inner workings of the SCSM databases (both operational and DW) are severely lacking but after 3 hours of looking around, Googling, and brute-forcing through it I have the following with the hopes of a free analyst portal/view...

      

    SELECT inc.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C  AS ID
        , inc.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS Title
        , inc.Description_59B77FD5_FE0E_D2B5_D541_0EBBD1EC9A2B AS Description
        , inc.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 AS CreatedDate
        , statusdisp.DisplayName AS IncidentStatus
        , AffectedUser.DisplayName AS AffectedUser
        , AssignedToUser.DisplayName AS AssignedToUser
        , classdisp.DisplayName AS Classification
        , locdisp.DisplayName AS Location
        , incExt.RoomNumber_24150BC7_9958_313D_8DD1_3CE2A0A68165 AS RoomNumber
        , incExt.AltSchedule_B1A8258C_1048_4D6E_AEB9_910811B94326 AS AltSchedule
    FROM MT_System$WorkItem$Incident (NOLOCK) inc

    INNER JOIN EnumType e ON 
    inc.Status_785407A9_729D_3A74_A383_575DB0CD50ED = e.EnumTypeId
    INNER JOIN DisplayStringView statusdisp ON e.EnumTypeName = statusdisp.ElementName
    AND statusdisp.LanguageCode='ENU'
    AND statusdisp.DisplayName!='Closed'
    AND statusdisp.DisplayName!='Resolved'

    INNER JOIN [ServiceManager].[dbo].[Relationship] AssignedToUserRel ON
    [BaseManagedEntityId] = AssignedToUserRel.[SourceEntityId]
    AND AssignedToUserRel.[RelationshipTypeId] = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722'
    INNER JOIN [ServiceManager].[dbo].[MT_System$Domain$User] AssignedToUser ON
    AssignedToUserRel.[TargetEntityId] = AssignedToUser.[BaseManagedEntityId]

    INNER JOIN [ServiceManager].[dbo].[Relationship] AffectedUserRel ON
    inc.[BaseManagedEntityId] = AffectedUserRel.[SourceEntityId]
    AND AffectedUserRel.[RelationshipTypeId] = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE'
    INNER JOIN [ServiceManager].[dbo].[MT_System$Domain$User] AffectedUser ON
    AffectedUserRel.[TargetEntityId] = AffectedUser.[BaseManagedEntityId]

    INNER JOIN MT_ClassExtension_685ca40e_4af9_4bca_93e1_5397c69cc946 incExt ON
    inc.BaseManagedEntityId = incExt.BaseManagedEntityId
    INNER JOIN DisplayStringView locdisp ON incExt.Locations_30F8DDC7_9552_A890_D1FC_6FC14F5B45AF = locdisp.LTStringId
    AND locdisp.LanguageCode='ENU'

    INNER JOIN EnumType enumClass ON 
    inc.Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA = enumClass.EnumTypeId
    INNER JOIN DisplayStringView classdisp ON enumClass.EnumTypeName = classdisp.ElementName
    AND classdisp.LanguageCode='ENU'

    where AssignedToUser.DisplayName='somename'

    ORDER BY locdisp.DisplayName, ID ASC

    This query is run against the ServiceManager database.

    (We extended the Incident class so that we could get detauls from users such as Location (56+ physical locations), Room Number, and Alternate Schedule (for users who are not full time at one location).

    This query works on my end and assuming the AssignedToUser and AffectedUser are not NULL properly display the list of incidents for a given technician (or all technicians) however there is one major flaw.  If a ticket gets assigned to Technician A and gets reassigned at some point to Technician B (or reassigned again to Technician C) there are duplicate records coming up for each AssignedTo user.  If you search for all technicians the duplicates show but if you filter by individual technicians then each tech has a version of the incident appear in their list.

    Does anyone out there know of a way to query for the latest 'version' of an incident so that only the most recent AssignedTo is listed.

    Note: Feel free to use this query as a building block for your own potential portal or use, although obviously if you figure it out please share!

    Tuesday, January 13, 2015 9:37 PM

Answers

  • first off, welcome :) second..not bad for a beginner. The operational database is basically a no-man's land where dangerous monsters roam. And it's dark. ;)

    third: a warning..you shouldn't query the database directly. But..we all do it anyway. :)

    I recommend using "with (NOLOCK)" on _all_ tables, not just the incident table. The Operational Database is supposed to be hands off..even simple "select" queries can interfere with the data access service and the hundreds of queries that run automatically against this database. So, to be safe..always use "with (nolock)" on every table that you query.

    If you want unassigned incidents to show up, replace your inner joins with left outer joins. That's a SQL thing..you want to display all incidents whether they have related users or not. So, left outer join.

    The reason you're seeing multiple assigned-to users is because "deleted" relationships between objects aren't truly deleted for about 2 days. So, in your join criteria, simply add "AffectedUserRel.IsDeleted=0". This will filter out any "deleted" relationships from your result set. Obviously you'll have to add that filter for each relationship table instance in your query.

    Lastly: I strongly recommend you use the SDK instead of querying the database directly. There's a learning curve, but it's far safer and it's actually supported :)

    • Marked as answer by nbb359 Friday, February 20, 2015 9:17 PM
    Wednesday, January 14, 2015 3:53 AM

All replies

  • first off, welcome :) second..not bad for a beginner. The operational database is basically a no-man's land where dangerous monsters roam. And it's dark. ;)

    third: a warning..you shouldn't query the database directly. But..we all do it anyway. :)

    I recommend using "with (NOLOCK)" on _all_ tables, not just the incident table. The Operational Database is supposed to be hands off..even simple "select" queries can interfere with the data access service and the hundreds of queries that run automatically against this database. So, to be safe..always use "with (nolock)" on every table that you query.

    If you want unassigned incidents to show up, replace your inner joins with left outer joins. That's a SQL thing..you want to display all incidents whether they have related users or not. So, left outer join.

    The reason you're seeing multiple assigned-to users is because "deleted" relationships between objects aren't truly deleted for about 2 days. So, in your join criteria, simply add "AffectedUserRel.IsDeleted=0". This will filter out any "deleted" relationships from your result set. Obviously you'll have to add that filter for each relationship table instance in your query.

    Lastly: I strongly recommend you use the SDK instead of querying the database directly. There's a learning curve, but it's far safer and it's actually supported :)

    • Marked as answer by nbb359 Friday, February 20, 2015 9:17 PM
    Wednesday, January 14, 2015 3:53 AM
  • All excellent points, thank you Aaron for your reply.

    I had started out hoping to use the SDK (which I mistakingly thought in the beginning was just for building console tasks and whatnot)...  However as I looked into it I swore I heard "O Fortuna" playing in the background and a feeling a sense of impending doom, even more-so than with directly querying the operational database!

    I will give the SDK another try and see what I can find/do.  I see there is a subforum here for customization using the SDK and authoring tool so I will see what is out there for tips and tricks!

    Wednesday, January 14, 2015 8:59 PM
  • Let me suggest a good starting point to help you up the learning curve:

    http://social.technet.microsoft.com/wiki/contents/articles/13472.system-center-2012-service-manager-developer-s-survival-guide.aspx

    That article contains links to a lot of information about the SDK and the Console API. (note, they are two different animals. The SDK is officially supported, the console API is not). I recommend focusing on the SDK for now.

    Wednesday, January 14, 2015 9:55 PM
  • it may be quicker to download the SMLets from codeplex and build your query in Powershell than learning the SDK if you already have a good understanding of how powershell works.
    Thursday, February 5, 2015 8:45 PM
  • If we hadn't extended the Incidents class to include additional fields SMLets would definitely be the way to go.  I haven't monkeyed with the SMLets too much to be honest but I wasn't able to see a way of getting the added properties which I would want to collect, unless I wasn't looking deep enough.

    For example, we extended it to include an enumerated "Locations" list for the physical building sites, Room Number for the location within said buildings, and an AltSchedule for those Affected Users that work in more than 1 location on a schedule.  While not super critical for version 1 it is still something that will need to be displayed.

    That SDK does have quite a learning curve it seems unfortunately.

    EDIT: Actually I wonder if it would somehow be possible to something similar to https://gallery.technet.microsoft.com/SCSM-Get-All-Incidents-861d1bdd to get the related data?
    • Edited by nbb359 Thursday, February 5, 2015 9:42 PM
    Thursday, February 5, 2015 9:25 PM