Home› Analytics

Incident - Business Service Relationship Query

Gabriel_LencesGabriel_Lences Customer Advanced IT Monkey ✭✭✭
edited October 2020 in Analytics

Hello everyone,

would anyone be kind enough to help me build our custom SQL / Odata Query? In our environment, we started with pairing the Business Service Class to our Incident Records (as a relationship to the "affected services" section of an IR). We'd like to have this visualized through a query - chart widget (probably bars would be the best option), where it would separate the bars according to what Business Service was paired to the IR (displayname of the Business Service on the horizontal side).

E.g bar 1: Hardware , bar 2: Exchange , bar 3: Teams , etc. , bar xy: null if the relationship doesn't exist between an IR and a Business Service.

and the vertical side would just show the number of the IRs which have these relationships.

We'd also love to be able to filter the incidents by date in the same way as some of the other queries can be filtered out on the portal. This date would reflect when the incident was created. (today, yesterday, last 7 days, last 30 days, etc.). It'd be also handy if there was a possibility to order the bars in a way where the Incidents with the most services would start out from the leftmost bar and Incidents with the least amount of paired business services would end at the rightmost bar (or vice versa, doesn't really matter that much).

Also if I clicked on one of the bars we'd like to get a list of all these IR's with the following columns:

ID of the IR, Displayname of the IR, Created Date, Affected User, Status, Support Group, Assigned User.

Any chance someone over here could help us with this "mildly" complex query, we'd be very grateful! 😊

Best Answers

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Answer ✓

    If you look at this post https://community.cireson.com/discussion/comment/16787#Comment_16787 you will see an example of how to encapsulate the sql query so the as function works with the portal scripts to parse properly the names columns. HTH

  • Gabriel_LencesGabriel_Lences Customer Advanced IT Monkey ✭✭✭
    Answer ✓

    @Brian_Wiest , together with a bit of @Peter_Miklian 's help we've managed to get this working by following your advice in the thread you linked.

    So I finally have the final query with everything we needed and everything I mentioned in my initial post!



    declare @lang as varchar(3)

    set @lang = 'enu'


    DECLARE @TimeZone varchar(50)

    exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT


    SELECT * from 

    (select 

    i.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as [Id]

    ,i.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 as [Title]

    ,bme.DisplayName

    ,i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as [Created]


    ,isnull(AffectedUser.DisplayName,'') as [Affected user]

    ,isnull(StatusLT.DisplayName,'') as [Status]

    ,isnull(Tier.DisplayName,'') as [Support Group]

    ,isnull(AssignedUser.DisplayName,'') as [Assigned To]

    ,isnull(AffectedService.DisplayName,'No business service paired') as [Affected Service]


     FROM MTV_System$WorkItem$Incident i


     --Get BME

    join BaseManagedEntity bme on (bme.BaseManagedEntityId = i.BaseManagedEntityId)


     --Get Status

    left join DisplayStringView StatusLT on (StatusLT.MPElementId = i.Status_785407A9_729D_3A74_A383_575DB0CD50ED and StatusLT.LanguageCode = @lang)


     --Get Tier Queue

    left join DisplayStringView Tier on (Tier.MPElementId = i.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C and Tier.LanguageCode = @lang)


     --Get Assigned User

    left join RelationshipView rAssignedUser on (rAssignedUser.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' and rAssignedUser.SourceEntityId = i.BaseManagedEntityId and rAssignedUser.IsDeleted = 0)


    left join MTV_System$Domain$User AssignedUser on (AssignedUser.BaseManagedEntityId = rAssignedUser.TargetEntityId)


     --Get Affected User

    left join RelationshipView rAffectedUser on (rAffectedUser.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' and rAffectedUser.SourceEntityId = i.BaseManagedEntityId and rAffectedUser.IsDeleted = 0)


    left join MTV_System$Domain$User AffectedUser on (AffectedUser.BaseManagedEntityId = rAffectedUser.TargetEntityId)


     --Get Affected Service


    left join RelationshipView rAffectedService on (rAffectedService.RelationshipTypeId in ( 'B73A6094-C64C-B0FF-9706-1822DF5C2E82' /*System.WorkItemAboutConfigItem*/,'D421CDA9-B728-9B41-C27C-7677B9A40D37' /*System.WorkItemImpactsService*/ ) and rAffectedService.SourceEntityId = i.BaseManagedEntityId and rAffectedService.TargetTypeId = 'B2A806A6-87F6-0BC9-DA74-C27E9AB5A5D7' and rAffectedService.IsDeleted = 0)


    left join MTV_Microsoft$SystemCenter$BusinessService AffectedService on (AffectedService.BaseManagedEntityId = rAffectedService.TargetEntityId)

    ) WI


    WHERE 

    @createdFilter


    Thank you Brian again and also thanks to @Chris_Chekaluk1 , @Justin_Workman and everyone else who contributed to this with their advices! 😊👍️

Answers

  • Gabriel_LencesGabriel_Lences Customer Advanced IT Monkey ✭✭✭

    Bump, anyone?

  • Chris_Chekaluk1Chris_Chekaluk1 Customer Advanced IT Monkey ✭✭✭

    Gabe- this should get you going in the right direction. This runs against the ServiceManager database. A couple items to note: 1) you should change the @lang value to your appropriate language code, I also included both the IR id, the title, and the DisplayName- choose which combination you want to retain. This query converts the time to local time zone- you may want to use the UTC time instead for display in the portal. Lastly, for the Affected Service, I included 2 relationship types in scope the 'AffectedCI' one and the 'ImpactsService' one. One of these two should work for you.


    Note, the IR's will be duplicated if you have multiple


    declare @lang as varchar(3)

    set @lang = 'enu'


    DECLARE @TimeZone varchar(50)

    exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT



    SELECT 

    i.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as 'Id'

    ,i.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 as 'Title'

    ,bme.DisplayName


    -- ,i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as 'CreateDate' /*

    ,convert(datetime,i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688)

    at time zone 'UTC' --associates the default time zone to a datetime object

    at time zone @TimeZone --does the desired conversion

    as 'CreateDate (local)'

    --*/


    ,isnull(AffectedUser.DisplayName,'') as 'Affected user'

    ,isnull(StatusLT.DisplayName,'') as 'Status'

    ,isnull(Tier.DisplayName,'') as 'Support Group'

    ,isnull(AssignedUser.DisplayName,'') as 'Assigned To'

    ,isnull(AffectedService.DisplayName,'') as 'Affected Service'   



     FROM MTV_System$WorkItem$Incident i

     --Get BME

    join BaseManagedEntity bme on (bme.BaseManagedEntityId = i.BaseManagedEntityId)

     --Get Status

    left join DisplayStringView StatusLT on (StatusLT.MPElementId = i.Status_785407A9_729D_3A74_A383_575DB0CD50ED and StatusLT.LanguageCode = @lang)

     --Get Tier Queue

    left join DisplayStringView Tier on (Tier.MPElementId = i.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C and Tier.LanguageCode = @lang)


     --Get Assigned User

    left join RelationshipView rAssignedUser on (rAssignedUser.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' and rAssignedUser.SourceEntityId = i.BaseManagedEntityId and rAssignedUser.IsDeleted = 0)

    left join MTV_System$Domain$User AssignedUser on (AssignedUser.BaseManagedEntityId = rAssignedUser.TargetEntityId)


     --Get Affected User

    left join RelationshipView rAffectedUser on (rAffectedUser.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' and rAffectedUser.SourceEntityId = i.BaseManagedEntityId and rAffectedUser.IsDeleted = 0)

    left join MTV_System$Domain$User AffectedUser on (AffectedUser.BaseManagedEntityId = rAffectedUser.TargetEntityId)


     --Get Affected Service

    left join RelationshipView rAffectedService on (rAffectedService.RelationshipTypeId in ( 'B73A6094-C64C-B0FF-9706-1822DF5C2E82' /*System.WorkItemAboutConfigItem*/,'D421CDA9-B728-9B41-C27C-7677B9A40D37' /*System.WorkItemImpactsService*/ ) and rAffectedService.SourceEntityId = i.BaseManagedEntityId and rAffectedService.TargetTypeId = 'B2A806A6-87F6-0BC9-DA74-C27E9AB5A5D7' and rAffectedService.IsDeleted = 0)

    left join MTV_Microsoft$SystemCenter$BusinessService AffectedService on (AffectedService.BaseManagedEntityId = rAffectedService.TargetEntityId)

  • Gabriel_LencesGabriel_Lences Customer Advanced IT Monkey ✭✭✭
    edited November 2020

    Thanks again Chris for your time on this! For everyone else, we've managed to modify the query a bit to do what it needs to do , the only thing we're missing over here with Chris is how to enable the query for chart filtering by using the @createdFilter. We tried our best to have this running and the query loads correctly at first but whenever i change it either to no filter / yesterday or practically any other option it doesn't bring in the results at all.

    Could anyone help us out with this on how would we go around to optimize this query?

    Sending the updated query below


    declare @lang as varchar(3)

    set @lang = 'enu'


    DECLARE @TimeZone varchar(50)

    exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT


    SELECT 

    i.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as [Id]

    ,i.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 as [Title]

    ,bme.DisplayName

    ,i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as [created]

    ,isnull(AffectedUser.DisplayName,'') as [Affected user]

    ,isnull(StatusLT.DisplayName,'') as [Status]

    ,isnull(Tier.DisplayName,'') as [Support Group]

    ,isnull(AssignedUser.DisplayName,'') as [Assigned To]

    ,isnull(AffectedService.DisplayName,'') as [Affected Service]  


     FROM MTV_System$WorkItem$Incident i

     --Get BME

    join BaseManagedEntity bme on (bme.BaseManagedEntityId = i.BaseManagedEntityId)


     --Get Status

    left join DisplayStringView StatusLT on (StatusLT.MPElementId = i.Status_785407A9_729D_3A74_A383_575DB0CD50ED and StatusLT.LanguageCode = @lang)


     --Get Tier Queue

    left join DisplayStringView Tier on (Tier.MPElementId = i.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C and Tier.LanguageCode = @lang)


     --Get Assigned User

    left join RelationshipView rAssignedUser on (rAssignedUser.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' and rAssignedUser.SourceEntityId = i.BaseManagedEntityId and rAssignedUser.IsDeleted = 0)


    left join MTV_System$Domain$User AssignedUser on (AssignedUser.BaseManagedEntityId = rAssignedUser.TargetEntityId)


     --Get Affected User

    left join RelationshipView rAffectedUser on (rAffectedUser.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' and rAffectedUser.SourceEntityId = i.BaseManagedEntityId and rAffectedUser.IsDeleted = 0)


    left join MTV_System$Domain$User AffectedUser on (AffectedUser.BaseManagedEntityId = rAffectedUser.TargetEntityId)


     --Get Affected Service

    left join RelationshipView rAffectedService on (rAffectedService.RelationshipTypeId in ( 'B73A6094-C64C-B0FF-9706-1822DF5C2E82' /*System.WorkItemAboutConfigItem*/,'D421CDA9-B728-9B41-C27C-7677B9A40D37' /*System.WorkItemImpactsService*/ ) and rAffectedService.SourceEntityId = i.BaseManagedEntityId and rAffectedService.TargetTypeId = 'B2A806A6-87F6-0BC9-DA74-C27E9AB5A5D7' and rAffectedService.IsDeleted = 0)


    left join MTV_Microsoft$SystemCenter$BusinessService AffectedService on (AffectedService.BaseManagedEntityId = rAffectedService.TargetEntityId)


    WHERE 

    @createdFilter

  • Gabriel_LencesGabriel_Lences Customer Advanced IT Monkey ✭✭✭
    edited November 2020

    Also just noticed the incidents that don't have a business service paired show up in the chart widget, but they're not marked as "null" so i cannot filter these ones out if I wanted to 🤔

  • Chris_Chekaluk1Chris_Chekaluk1 Customer Advanced IT Monkey ✭✭✭

    there are a couple ways you can address this... First, if you want to complete omit IR's that do not have a related Business Service, you can change the 'LEFT JOIN MTV_Microsoft$SystemCenter$BusinessService AffectedService ...' line by deleting 'LEFT' from the beginning. Doing this will in effect hide all IR's that are missing a Business Service.

    If you instead want to display some text whenever an IR is missing a related Business Service, then instead of the above change, you should be looking to change the ',isnull(AffectedService.DisplayName,'') as [Affected Service] ' line instead. What I would suggest is to place the desired text between the single quotes.

  • Gabriel_LencesGabriel_Lences Customer Advanced IT Monkey ✭✭✭

    Awesome, thanks Chris changing the line as you mentioned in your second point worked like a charm! 😊 The only thing remaining now is to somehow get the @createdFilter to work 😁😅

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

    The @createdFilter is expecting a date with the column name 'Created'. I think you can alias whatever date column you want as 'Created' and use where @createdFilter and it will work.

  • Chris_Chekaluk1Chris_Chekaluk1 Customer Advanced IT Monkey ✭✭✭

    maybe try using [Created] instead of [created] in the following line:

    ,i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as [created]

  • Gabriel_LencesGabriel_Lences Customer Advanced IT Monkey ✭✭✭

    Hey there @Justin_Workman , thanks for the info we've managed to read on this with Chris as well, that's why if you look at the adjusted query I posted above we went with

    ,i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as [created]

    As you can see at the beginning of the query, we already have it aliased and it's still not working.

    @Chris_Chekaluk1 thanks for the suggestion, tried capitalizing the C and still the same result - doesn't work.

    Really really weird. 😔🤷

  • Brian_WiestBrian_Wiest Customer Super IT Monkey ✭✭✭✭✭
    Answer ✓

    If you look at this post https://community.cireson.com/discussion/comment/16787#Comment_16787 you will see an example of how to encapsulate the sql query so the as function works with the portal scripts to parse properly the names columns. HTH

  • Gabriel_LencesGabriel_Lences Customer Advanced IT Monkey ✭✭✭

    Hey @Brian_Wiest , thanks for the lnk to the thread, the info is useful but I kind of can't seem to get my head around this : how exactly would this look like in my specific query? (4th post, the updated one) I've tried several variants but none of these seemed to have worked. Thanks.

  • Gabriel_LencesGabriel_Lences Customer Advanced IT Monkey ✭✭✭
    Answer ✓

    @Brian_Wiest , together with a bit of @Peter_Miklian 's help we've managed to get this working by following your advice in the thread you linked.

    So I finally have the final query with everything we needed and everything I mentioned in my initial post!



    declare @lang as varchar(3)

    set @lang = 'enu'


    DECLARE @TimeZone varchar(50)

    exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT


    SELECT * from 

    (select 

    i.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C as [Id]

    ,i.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 as [Title]

    ,bme.DisplayName

    ,i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as [Created]


    ,isnull(AffectedUser.DisplayName,'') as [Affected user]

    ,isnull(StatusLT.DisplayName,'') as [Status]

    ,isnull(Tier.DisplayName,'') as [Support Group]

    ,isnull(AssignedUser.DisplayName,'') as [Assigned To]

    ,isnull(AffectedService.DisplayName,'No business service paired') as [Affected Service]


     FROM MTV_System$WorkItem$Incident i


     --Get BME

    join BaseManagedEntity bme on (bme.BaseManagedEntityId = i.BaseManagedEntityId)


     --Get Status

    left join DisplayStringView StatusLT on (StatusLT.MPElementId = i.Status_785407A9_729D_3A74_A383_575DB0CD50ED and StatusLT.LanguageCode = @lang)


     --Get Tier Queue

    left join DisplayStringView Tier on (Tier.MPElementId = i.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C and Tier.LanguageCode = @lang)


     --Get Assigned User

    left join RelationshipView rAssignedUser on (rAssignedUser.RelationshipTypeId = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' and rAssignedUser.SourceEntityId = i.BaseManagedEntityId and rAssignedUser.IsDeleted = 0)


    left join MTV_System$Domain$User AssignedUser on (AssignedUser.BaseManagedEntityId = rAssignedUser.TargetEntityId)


     --Get Affected User

    left join RelationshipView rAffectedUser on (rAffectedUser.RelationshipTypeId = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' and rAffectedUser.SourceEntityId = i.BaseManagedEntityId and rAffectedUser.IsDeleted = 0)


    left join MTV_System$Domain$User AffectedUser on (AffectedUser.BaseManagedEntityId = rAffectedUser.TargetEntityId)


     --Get Affected Service


    left join RelationshipView rAffectedService on (rAffectedService.RelationshipTypeId in ( 'B73A6094-C64C-B0FF-9706-1822DF5C2E82' /*System.WorkItemAboutConfigItem*/,'D421CDA9-B728-9B41-C27C-7677B9A40D37' /*System.WorkItemImpactsService*/ ) and rAffectedService.SourceEntityId = i.BaseManagedEntityId and rAffectedService.TargetTypeId = 'B2A806A6-87F6-0BC9-DA74-C27E9AB5A5D7' and rAffectedService.IsDeleted = 0)


    left join MTV_Microsoft$SystemCenter$BusinessService AffectedService on (AffectedService.BaseManagedEntityId = rAffectedService.TargetEntityId)

    ) WI


    WHERE 

    @createdFilter


    Thank you Brian again and also thanks to @Chris_Chekaluk1 , @Justin_Workman and everyone else who contributed to this with their advices! 😊👍️

Sign In or Register to comment.