Home SQL Server

Support Group Membership

Metro_ITSMetro_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


  • Metro_ITSMetro_ITS Customer IT Monkey ✭

    @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!

Sign In or Register to comment.