SQL Help - Group Membership

Greg Kellogg

CS 10.0
SQL Server

I was hoping someone would have created SQL to list membership of all groups.

I.e.,

Group A
o User 1
o User 2
o User 3
o User 5

Group B
o User 4
o User 5
o User 6
o User 7

This shows that User 5 is in both groups. Anyway, what I want is a complete list of Groups with their Members.

Has anyone done this before?

Thanks.

Find more posts tagged with

Comments

Bhupinder_Singh

Though you are looking for a SQL Server query, I do have an Oracle query that a colleague put together recently:

SELECT a.Name "Groups", c.Name "Users", c.LastName, c.FirstName FROM KUAF a, KUAFChildren b, KUAF c

WHERE a.ID = b.ID(+)

AND b.ChildID = c.ID(+)

AND a.Type=1

AND a.Deleted = 0

ORDER BY a.Name

________________________________________________________________________________________________________

Bhupinder Singh

Systems Administrator | Managed Private Cloud

OpenText Enterprise World 2015, November 8-13, 2015. Register Now at http://www.opentext.com/ew2015

OpenText, 275 Frank Tompa Drive, Waterloo, Ontario N2L 0A1

Greg Kellogg

Thanks Bhupinder. Hope you are well. I will test this tomorrow.

Greg Kellogg | gkellogg@iqbginc.com | P 361-758-3762 | C 361-413-9200

the iQ Business Group

intelligence. applied.

www.IQBGinc.com

From: eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com>
Sent: Tuesday, August 11, 2015 4:55 PM
To: eLink Recipient
Subject: SQL Help - Group Membership

SQL Help - Group Membership

Posted by bsingh@opentext.com (Singh, Bhupinder) On 08/11/2015 05:54 PM

Though you are looking for a SQL Server query, I do have an Oracle query that a colleague put together recently:

SELECT a.Name "Groups", c.Name "Users", c.LastName, c.FirstName FROM KUAF a, KUAFChildren b, KUAF c

WHERE a.ID = b.ID(+)

AND b.ChildID = c.ID(+)

AND a.Type=1

AND a.Deleted = 0

ORDER BY a.Name

________________________________________________________________________________________________________

Bhupinder Singh

Systems Administrator | Managed Private Cloud

OpenText Enterprise World 2015, November 8-13, 2015. Register Now at http://www.opentext.com/ew2015

OpenText, 275 Frank Tompa Drive, Waterloo, Ontario N2L 0A1

[To post a comment, use the normal reply function]
Topic:	SQL Help - Group Membership
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

****** CONFIDENTIALITY NOTICE ****** This message and any attachments are confidential and for the sole use of the intended recipient. If you are not the intended recipient, any copying, forwarding, disclosure, distribution or use of any part of this message or its attachments is strictly prohibited and may be unlawful. If you believe that you received this email in error, please do not read it or any attachments thereto, and notify the sender immediately by email and then delete this message from your system. Thank you.

David_Turpie

I have a live reprt using SQL which may help

Inputs:

String: Enter Numeric GroupID or %%

String: Numeric UserID or %%

String: Enter Group Name or Partial Group Name plus%% or %%

String: Enter User ID or Partial User ID plus %% or %%

SQL:

select b.Name "Group Name",c.Name "User Name",c.firstname,c.lastname,c.title,a.ID,a.ChildID from KUAFChildren a,KUAF b,KUAF c where a.ID=b.ID and a.ChildID=c.ID and a.ID like %1 and a.ChildID like %2 and b.name like %3 and c.name like %4 order by b.name

Param %1: User Input 1

Param %2: User Input 2

Param %3 User Input 3

Param %4: User Input 4

Report Format: Auto LiveReport

To get all groups membership enter %% in all 4 input boxes when you run the report

Hope this helps

David