nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Group Usage Report

Matthew_Turner

Would anyone be able help us to extract certain group information from Livelink.

Specifically we want to find out if any groups are 'empty' and also if there are any groups which have not been assigned to any areas in Livelink.

We also have Webreports 5.2.

Thanks

Matt

Find more posts tagged with

Comments

Lindsay_Davies

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Hi Matt,

These queries should help....

This will report groups that are not deleted and have no users or groups as members of the reported groups.

select id,name from kuaf where type=1 and deleted=0 and id not in (select distinct(id) from kuafchildren)

This will report groups that are not deleted and are not currently used to control permissions on DTree objects.

select id,name from kuaf where type=1 and deleted=0 and id not in (select distinct(rightid) from dtreeacl)

For workflow objects

select id,name from kuaf where type=1 and deleted=0 and id not in (select distinct(workacl_rightid) from workacl)

Regards

Lindsay

OpenText UK

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 December 06, Tue 13:32
To: eLink Recipient
Subject: Group Usage Report

Group Usage Report

Posted by matthew.turner@derbyshire.gov.uk (Turner, Matthew) On 12-06-2011 08:31

Hi Would anyone be able help us to extract certain group information from Livelink. Specifically we want to find out if any groups are 'empty' and also if there are any groups which have not been assigned to any areas in Livelink. We also have Webreports 5.2. Thanks Matt

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Matthew_Turner

Hi Lindsay,

This is just what we are after thank you.

The first two statement run fine and provide us with some really useful info here.

Cant get the last one to run though (for workflow objects) - not sure why ?

This is the message:

Livelink Error: Error processing request.

[[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'workacl'. - select id,name from kuaf where type=1 and deleted=0 and id not in (select distinct(workacl_rightid) from workacl)]

Any idea?

Thanks

Matt

Matthew_Turner

Got it !

The SQL at the end was missing a 'W' so making it read 'wworkacl' sorted it.

[[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'workacl'. - select id,name from kuaf where type=1 and deleted=0 and id not in (select distinct(workacl_rightid) from wworkacl)]

Matt

Lindsay_Davies

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

Sorry Matt,

Must have been a copy/paste/spellcheck issue when I sent it to you.

I realise now that it only makes sense to combine the two statements to know which groups are not used at all,

otherwise you need to compare the two sets of output to see items that are listed in both.

select id,name from kuaf where type=1 and deleted=0

and id not in (select distinct(rightid) from dtreeacl

UNION

select distinct(workacl_rightid) from wworkacl)

Regards

Lindsay

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 December 07, Wed 11:30
To: eLink Recipient
Subject: RE Group Usage Report

RE Group Usage Report

Posted by matthew.turner@derbyshire.gov.uk (Turner, Matthew) On 12-07-2011 06:25

Got it ! The SQL at the end was missing a 'W' so making it read 'wworkacl' sorted it. [[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'workacl'. - select id,name from kuaf where type=1 and deleted=0 and id not in (select distinct(workacl_rightid) from wworkacl)] Matt

[To post a comment, use the normal reply function]
Topic:	Group Usage Report
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Bhupinder_Singh

Here’s one for empty groups you can try:

select a.name from kuaf a where a.type=1 and a.deleted=0 and a.id not in (select distinct b.id from kuafchildren b)

NOTES:

kuafchildren stores the data defining the relationships between groups and the members of the group. In the above SQL we end up selecting those groups from kuaf that have no entry in the kuafchildren table, i.e. they have no members.

type=1 singles out groups instead of users

deleted=0 singles out existing groups, and prevents deleted groups from appearing

- Bhupinder
----------------------------------------------
Bhupinder Singh, B.Math, B.Ed.
Senior Systems Analyst, Information Technology
Open Text, Waterloo, Ontario, Canada
----------------------------------------------

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, December 06, 2011 8:32 AM
To: eLink Recipient
Subject: Group Usage Report

Group Usage Report

Posted by matthew.turner@derbyshire.gov.uk (Turner, Matthew) On 12-06-2011 08:31

Would anyone be able help us to extract certain group information from Livelink.

Specifically we want to find out if any groups are 'empty' and also if there are any groups which have not been assigned to any areas in Livelink.

We also have Webreports 5.2.

Thanks

Matt

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center