LiveReport for the Size of all Disabled User's Personal Workspaces

Hi there,

I have little experience with creating SQL queries and I'm trying to create a LiveReport to show me the sizes of the Personal Workspaces for all the accounts that are disabled. So far I have a query that shows all of the disabled users:

select ID, Name, LastName, Firstname, UserPrivileges from kuaf where UserPrivileges like '14' OR UserPrivileges like '2062' order by UserPrivileges, LastName

and a query that will show me the size of all Personal Workspaces:

SELECT SUM(dversdata.datasize)/(1024*1024) "Size(MB)", SUM(datasize/1024) "Size(KB)", COUNT(dversdata.datasize) "Item Count", -dt.ownerid AS "Owner ID", dt2.NAME "Personal Workspace Name" FROM dtree dt, dtree dt2, dversdata WHERE dversdata.docid = dt.dataid AND dt.ownerid IN (SELECT ownerid FROM dtree WHERE SUBTYPE = 142) AND dt2.dataid = -dt.ownerid GROUP BY dt.ownerid, dt2.NAME ORDER BY SUM (dversdata.datasize) DESC

How do I combine those 2 queries? I've tried different combinations and none of them work.

Thanks in advance for any help you can provide.

Ian

Find more posts tagged with

Comments

eLink User

If your second query works correctly and you are trying to eliminate the active users if you do this you will succeed

Take a look at KUAF.ID of a deleted user or for that matter a valid user

Check if the ID matches the dtree.ownerid or negative dtree.ownerid I forget because some objects have negatives

The

What I am getting at is when a personal workspace is created the KUAF.Id and the dtree.ownerid match a deleted user is type=0 and deleted=1

Your first query should be written to look really at the 'BitWise' logic as you don't need the 2062 or 14 plenty of queries in this KB for Bit manipulation they are different depending on SQLserver or Oracle

Ciao, Appu

On Oct 21, 2016, at 6:37 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

eLink : LiveReport for the Size of all Disabled User's Personal Workspaces
LiveReport for the Size of all Disabled User's Personal Workspaces Posted byOsterbeck, IanOn 10/21/2016 03:42 PM

Hi there,I have little experience with creating SQL queries and I'm trying to create a LiveReport to show me the sizes of the Personal Workspaces for all the accounts that are disabled. So far I have a query that shows all of the disabled users:select ID, Name, LastName, Firstname, UserPrivileges from kuaf where UserPrivileges like '14' OR UserPrivileges like '2062' order by UserPrivileges, LastNameand a query that will show me the size of all Personal Workspaces:SELECT SUM(dversdata.datasize)/(1024*1024) "Size(MB)", SUM(datasize/1024) "Size(KB)", COUNT(dversdata.datasize) "Item Count", -dt.ownerid AS "Owner ID", dt2.NAME "Personal Workspace Name" FROM dtree dt, dtree dt2, dversdata WHERE dversdata.docid = dt.dataid AND dt.ownerid IN (SELECT ownerid FROM dtree WHERE SUBTYPE = 142) AND dt2.dataid = -dt.ownerid GROUP BY dt.ownerid, dt2.NAME ORDER BY SUM (dversdata.datasize) DESCHow do I combine those 2 queries? I've tried different combinations and none of them work.Thanks in advance for any help you can provide.Ian
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center CS16

Donna Nalls

Hi Ian,

The dataid of a user's PWS volume should equal KUAF.ID for that user.

So, if i am reading your sql correctly, your dt2.dataid = kuaf.ID

Best Regards,

Donna

Greg Kellogg

Thanks to Giovanny from Bogota (my wife and daughter visited Bogota a couple weeks ago and loved it) for helping me complete this query.

Since my data set is somewhat limited and my users rarely utilize the Personal Workspace I modifiied my query to KB and I looked for ALL documents owned by deleted users - not just those in the Personal Workspace.

select k.name, sum(dv.datasize)/1024 as "Total Size (KB)", count(dv.datasize) as "Number of Items"
from kuaf k, dversdata dv, dtree dt
where k.deleted=1
and dt.dataid=dv.docid
and k.id=ABS(dt.ownerid)
group by k.name
order by "Number of Items" desc

This query will list the number and size of items owned by any user who has been deleted (deleted=1). If you want to limit the query to only documents add the dt.subtype=144 to the where clause.

I am by no means a SQL expert so if anyone out there has an issue with the order of my constraints or anything else please call it out.

Greg Kellogg

I have been informed that the query below will only point to the user's Personal Workspace (OwnerID points to the PWS volume). If you want to look throughout your enterprise for all data then you would change the ABS(dt.ownerid) to dt.userid - that will go enterprise wide. Still waiting for final verification on this but I believe it to be true.

Brian_Walsh

To match the ID in KUAF to a users Personal workspace you would use

k.ID = dt.DataID since a users personal workspace has the same DataID as the users ID is in KUAF.

And yes for users personal workspace the OwnerID will be a negative reflection of the DataID

The user that owns a document is identified using DTree.UserID

Cheers,

Brian G Walsh
Principal Technical Analyst

Content Server Team

OpenText Corporation
1-800-540-7292
bwalsh@opentext.com

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, October 24, 2016 12:50 PM
To: eLink Recipient
Subject: LiveReport for the Size of all Disabled User's Personal Workspaces 2

LiveReport for the Size of all Disabled User's Personal Workspaces 2

Posted by Kellogg, Greg On 10/24/2016 12:49 PM

Thanks to Giovanny from Bogota (my wife and daughter visited Bogota a couple weeks ago and loved it) for helping me complete this query.

This query will list the number and size of items owned by any user who has been deleted (deleted=1). If you want to limit the query to only documents add the dt.subtype=144 to the where clause.

I am by no means a SQL expert so if anyone out there has an issue with the order of my constraints or anything else please call it out.

[To post a comment, use the normal reply function]
Topic:	LiveReport for the Size of all Disabled User's Personal Workspaces
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16