Portal Group Mapping Settings

Ozgur_Sahin

Hello Dear All

Can I get a list of "Cireson portal support group mappings" details(which SCSM Group mapped with which AD Group) by PS or SQL command?

Thank you.

Konstantin_Slavin-Bo

This question has actually been asked a few times, and I thought it wasn't possible, by I think I found a way:

Look up the SupportGroupMapping_CI$DomainGroup_Enumeration table in the ServiceManagement DB. For each entry, look up DomainGrouId in the CI$DomainGroup table to get UserName. Then using SMLets, lookup the EnumerationsId (from 1st table) using Get-SCSMChildEnumeration and where Id -eq EnumerationId. This would give you each mapping.

You would have to run this from a machine with SMLets installed, and with a user which has access to the ServiceManagement database.

Conner_Wood

Cireson - Portal Group Mapping Settings are stored in the ServiceManager Database!

However the mappings are stored in a single cell as xml within [ServiceManager].[dbo].[MTV_Cireson$ConsoleApps$Tier$Mappings$Settings]

Ex.
IR Mapping == [IncidentMappings_EB234D22_DE19_831B_F064_250FBF063507]
SR Mapping == [ServiceRequestMappings_EC534F73_EC68_D8D5_217E_66B87BF08D10]

You would need to retrieve this information and parse through the xml and make a list of mappings where a mapping holds both the GroupId and EnumerationId.  What is cool is you can parse XML using SQL:

USE ServiceManager;

DECLARE @IR_XML XML = (SELECT [IncidentMappings_EB234D22_DE19_831B_F064_250FBF063507]
FROM [dbo].[MTV_Cireson$ConsoleApps$Tier$Mappings$Settings] (nolock));


SELECT MappedGroup.value('(GroupId)[1]','nvarchar(max)') as 'GroupId'
      ,MappedGroup.value('(EnumerationId)[1]','nvarchar(max)') as 'EnumerationId'
FROM @IR_XML.nodes('/SupportGroupListData/Mappings/MappedGroup') as N(MappedGroup)

Note:  The GroupId is the SCSM GUID of the Config Item, not the objectGUID of the Active Directory Group.

Then in order to translate the GUIDs to the actual group you could either pass the GUID to SCSM powershell or continue with SQL.

If you continued with SQL:

1. To get the Group Display Name you would search [ServiceManager].[dbo].[MTV_System$Domain$User] WHERE [BaseManagedEntityId] = 'GroupId' .... and select DisplayName
2. To get the Enumeration Display Name you would search [ServiceManager].[dbo].[LocalizedText] WHERE LanguageCode = 'ENU' AND LTStringType = 1 AND LTStringId = 'EnumerationId'  ..... and select LTValue

• Edit:  Turns out you can also get the Enumeration Display Name by executing a Microsoft SCSM SQL Function:

SELECT [ServiceManager].[dbo].[fn_GetLocalizedText]('EnumerationId','ENU')

Good luck!

Konstantin_Slavin-Bo

@Conner_Wood - Aha! That is actually really useful! There has been multiple questions regarding this, and so far no clear answer. [Enter Conner] - thank you very much!