Support Group Membership
Metro_ITS
Customer IT Monkey ✭
Good afternoon all,
I have been working in SQL with SCSM for the past year and a half, and have come across some outstanding support from this community...so thank you!
I started working in the ServiceManager/ServiceManagement DB's to develop more robust reporting, like many of you. I am now in the process of migrating all of my reports to the DWDataMart, which I am finding to be more elusive when searching for data. One such search is to determine group membership.
I can, easily enough, query tickets by support groups, and then find the assignee information. However, SCSM does not natively associate the Creator of a ticket to a support group. I have found a way to do this in the ServiceManagement DB. However, for the life of me, I cannot find the right information in the DWDataMart DB.
Here is the ServiceManagement query, which I use to determine which of my team members created a ticket that was escalated to another team. This is added as a subquery to the Where statement at the end of my normal data query.
Has anyone found a way to do this in the DWDataMart?
---------------------------------------------------
--Join Created By Team
LEFT JOIN SupportGroupMapping_CI$DomainGroup_Enumeration as SupportEnum2 on SupportEnum2.EnumerationId = WI.TierId
LEFT JOIN GroupMembership_CI$DomainGroup_CI$User as GroupUser2 on SupportEnum2.DomainGroupId = GroupUser2.DomainGroupId
Where DSGroup.DisplayString IN ('groupname1','groupname2')
and DATEADD(Hour,-6,WI.Created) >= (cast(@dtFriday-1 as date)) and DATEADD(Hour,-6,WI.Created) <= getdate()
and WI.CreatedByUserId IN
(Select GroupUser.UserId
From WorkItem WI2
--Join Created By Team
--LEFT JOIN ServiceManagement.dbo.GroupMembership_CI$DomainGroup_CI$User as CBT2 on WI2.TierId = CBT2.DomainGroupId
LEFT JOIN SupportGroupMapping_CI$DomainGroup_Enumeration as SupportEnum on SupportEnum.EnumerationId = WI2.TierId
LEFT JOIN GroupMembership_CI$DomainGroup_CI$User as GroupUser on GroupUser.DomainGroupId = SupportEnum.DomainGroupId
Where GroupUser.DomainGroupId = '614578FF-B678-BBCD-0F28-E589B5BF7150')
Order By DSStatus.DisplayString ASC
Best Answer
-
Brian_Wiest
Customer Super IT Monkey ✭✭✭✭✭
The Warehouse is a challenge sometimes getting desired reports. So much so we created another database with tables layed out the way we need for reporting. We also needed User to Support group mapping we, so we cheated to get it. The Cireson database has this so we copy the contents into a custom warehouse. We have one job that creates a list of support groups and the portal mappings setting to AD group. Then we use the Cireson table GroupMembership_CI$DomainGroup_CI$User to see who is a member of that group.
HTH
5
Answers
The Warehouse is a challenge sometimes getting desired reports. So much so we created another database with tables layed out the way we need for reporting. We also needed User to Support group mapping we, so we cheated to get it. The Cireson database has this so we copy the contents into a custom warehouse. We have one job that creates a list of support groups and the portal mappings setting to AD group. Then we use the Cireson table GroupMembership_CI$DomainGroup_CI$User to see who is a member of that group.
HTH
@Brian_Wiest this sounds like a great workaround for it. I was hoping to use the default tables, but it sounds like I may have to go a custom route like you all have. Thanks for sharing this info!