How to find out group owner through SQL query ??

Narendar_Reddyk

1. want to find out a list of users who are part of group and the owner of that group

2. want to find of list of all coordinators, Guests and Members of project

Any help on this .. could be greatly appreciated.

Find more posts tagged with

Comments

Nizar Ghazal

Quoted Narendar Reddyk on 06/04/2018 12:39 PM:

1. want to find out a list of users who are part of group and the owner of that group

2. want to find of list of all coordinators, Guests and Members of project

Any help on this .. could be greatly appreciated.

Here's something simple:

SELECT (SELECT Name from KUAF WHERE ID = OwnerID) AS Owner, ID AS UserID, Name As Member from KUAF WHERE GroupID = %1

I'm sure there are better ways to represent it, but there's my little way.

This is a little tricky, as you'll have various rights to the object for each member and their role...Do you have the option of just running the web services, instead?

The Collaboration.svc?WSDL provides really easy functions that lets you dump out an object that provides each member, and their role in a standard format.

Maybe someone else went through and made a nice little SQL chunk for the above.

Appu Nair

Nizar Ghazal's reply is excellent.In the olden days of this forum people would post helpful queries and knowing the code it is easy enough to report on Coordinators,Members,Guests

A project is SubType=202 from that we need the DataID of a Project so

select Name,DataID,OwnerID,SubType from DTree where DataID=21047333 and SubType=202; //remove the stu*** DataID to get all Projects and test on a few projects.

Then we can do something like this

select ID,Name from KUAF whereType=21047333; //in a project the Type in KUAF becomes the Project DataID

or more work where Name like ('Coordinators''Members','Guests')

it will give 3 unique Rows with a ID,Name like this

IDName21047334Coordinators21047336Guests21047335Members

Now tie back to the standard KUAFChilderen,KUAF to enumerate those

select KF.* from KUAFChildren KFC, KUAF KF where KFC.ChildID = KF.ID and KFC.ID = 21047334;

or like this for all three roles

select KF.* from KUAFChildren KFC, KUAF KF where KFC.ChildID = KF.ID and KFC.ID IN (21047334,

21047336,

21047335);

Mind you the above query does not enumerate Groups in that... Because the three roles can take Groups as well.

Ciao,

Appu Nair
Principal Architect | Alitek
Website: alitek.com

Confidentiality Note: This message contains information, which may be privileged or confidential, or exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee responsible for delivering the message to the intended recipient, you are hereby NOTIFIED that any dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you receive this message in error, please notify sender immediately.

Colin_Schmidt

The KUAF.TYPE can actually mean something else useful: User, group, etc…but when it exceeds a small value it becomes either a Project or Community number. This, as explained, goes back and forth between the KUAF, DTREE, and the KUAFCHILDREN.

Attached is an Oracle LR that will list all Explicit membership to Groups, Projects or Communities and who the leader is. It’s the reverse of what you want, though. A lot of tables are tied together and it takes some digging to find the relationships. WorkFlows to Maps and their location are a nice puzzle.

Colin J.

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 4, 2018 12:40 PM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: [EXTERNAL] - Re How to find out group owner through SQL query ??

[EXTERNAL] - Re How to find out group owner through SQL query ??

Posted byanair@alitek.com (Nair, Appu) On 06/04/2018 01:39 PM

Nizar Ghazal's reply is excellent.In the olden days of this forum people would post helpful queries and knowing the code it is easy enough to report on Coordinators,Members,Guests

A project is SubType=202 from that we need the DataID of a Project so

select Name,DataID,OwnerID,SubType from DTree where DataID=21047333 and SubType=202; //remove the stu*** DataID to get all Projects and test on a few projects.

Then we can do something like this

select ID,Name from KUAF whereType=21047333; //in a project the Type in KUAF becomes the Project DataID

or more work where Name like ('Coordinators''Members','Guests')

it will give 3 unique Rows with a ID,Name like this

ID	Name
21047334	Coordinators
21047336	Guests
21047335	Members

Now tie back to the standard KUAFChilderen,KUAF to enumerate those

select KF.* from KUAFChildren KFC, KUAF KF where KFC.ChildID = KF.ID and KFC.ID = 21047334;

or like this for all three roles

select KF.* from KUAFChildren KFC, KUAF KF where KFC.ChildID = KF.ID and KFC.ID IN (21047334,

21047336,

21047335);

Mind you the above query does not enumerate Groups in that... Because the three roles can take Groups as well.

Ciao,

Appu Nair
Principal Architect | Alitek
Website: alitek.com

[To post a comment, use the normal reply function]
Topic:	How to find out group owner through SQL query ??
Forum:	Content Server LiveReports Forum
Content Server:	My Support

This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient(s), please reply to the sender and destroy all copies of the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email, and/or any action taken in reliance on the contents of this e-mail is strictly prohibited and may be unlawful. Where permitted by applicable law, this e-mail and other e-mail communications sent to and from Cognizant e-mail addresses may be monitored.

ExplicitMembership.txt