Doug Deitterick's Blog

Information about Skype for Business, Lync, OCS, and Exchange UM.

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

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

  • Comments 4
  • Likes

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
  • When I run this query for PChat members, I'm getting: Msg 208, Level 16, State 1, Line 1 Invalid object name 'GroupChat.dbo.Exp_RoleView'.

  • 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 Good catch! I updated the post to better reflect that you will need to update the query with the correct database name.

  • 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.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment