How to Get a List of Explicit Chat Room Members and Managers

You can use the query* below to get a list of explicit permissions assigned in Group Chat or Persistent Chat:

SELECT tblNode.nodeName AS "Category/Channel",
tblPrincipal.prinName AS "Name",
CASE tblPrincipalType.ptypeDesc
WHEN 'Parlano.Server.Common.User' THEN 'User'
WHEN 'Parlano.Server.Common.ExternalUser' THEN 'External User'
WHEN 'Parlano.Server.Common.FederatedUser' THEN 'Federated User'
ELSE tblPrincipalType.ptypeDesc
END AS "Type",
CASE isMember WHEN 1 THEN 'Y' ELSE 'N' END AS "Member",
CASE isManager WHEN 1 THEN 'Y' ELSE 'N' END AS "Manager"
FROM [GroupChat].[dbo].[Exp_RoleView]
INNER JOIN [GroupChat].[dbo].[tblPrincipal] on Exp_RoleView.principalId=tblPrincipal.prinGuid
INNER JOIN [GroupChat].[dbo].[tblPrincipalType] on tblPrincipal.prinTypeID=dbo.tblPrincipalType.ptypeID
INNER JOIN [GroupChat].[dbo].[tblNode] on Exp_RoleView.nodeDbId=tblNode.nodeID
/* WHERE isManager = 1 */
/* WHERE isMember = 1 */
ORDER BY [Category/Channel]

Note: This same query works on Lync Server 2013 Persistent Chat, however you will need to change the following line:

INNER JOIN [GroupChat].[dbo].[tblPrincipal] on Exp_RoleView.principalID=tblPrincipal.prinGuid

Remember to update the query above with the correct database name.  For GroupChat, replace [GroupChat] with the name of your GroupChat database.  For Persistent Chat, replace [GroupChat] with [mgc].

If you want only a list of members or managers, you can uncomment the appropriate line:

/* WHERE isManager = 1 */
/* WHERE isMember = 1 */

The query will produce results similar to this:

Thanks go out to Indranil Dutta for sending this my way and to Justin Kulesa and Michael Hendrix for working on the SQL query.

*These queries are provided for you to use at your own risk. Please make sure you test before running in a production environment.

Comments

• Anonymous
  January 01, 2003
  Ah - figured it out. There is no DB called GroupChat in greenfield Persistent Chat deployments. The DB is called mgc. Swapped out the name throughout the query and it worked fine.
• Anonymous
  January 01, 2003
  The comment has been removed
• Anonymous
  January 01, 2003
  @AnonymousGood catch! I updated the post to better reflect that you will need to update the query with the correct database name.
• Anonymous
  January 01, 2003
  I used this on Lync 2010 Group Chat and found 1 error when it ran. On the 2nd INNER JOIN, the dbo. on dbo.tblPrincipalType.ptypeID needed to be removed to get it to work.
  Thanks.