Incident - Business Service Relationship Query
Gabriel_Lences
Customer Advanced IT Monkey ✭✭✭
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_Wiest
Customer Super IT Monkey ✭✭✭✭✭
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
2 -
Gabriel_Lences
Customer Advanced IT Monkey ✭✭✭
@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! 😊👍️
1
Answers
Bump, anyone?
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)
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
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 🤔
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.
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 😁😅
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.
maybe try using [Created] instead of [created] in the following line:
,i.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 as [created]
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. 😔🤷
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
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.
@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! 😊👍️