Report for Size of Personal Workspace

Amanda Rush · 2023-04-06T19:47:45+00:00

There was an error rendering this rich post.

Does anyone have a LiveLink report they are willing to share that can show the total size of each AND all My Workspace folders for CS 16.2.7? I would rather not have to do a manual hunt through each Workspace to find out if it is used and how much is in it.

Thank you!

Find more posts tagged with

Comments

Greg Griffiths

You can start with DVersData for the filesize, DTree for the content and DTreeAncestors for the relationships between parent and child objects. You should be able to use these three tables simlarly to the out of the box 25 biggest items LiveReport to generate what you need.

appuq

I wrote a reply on Saturday which it said was pending on approval let me see if something in the code is preventing upload

The forum's spam filter is blocking my reply for what its worth perhaps you can look at my query here

@Karen Weir is this Blog moderated ? if so what is wrong with my post here?

Is the intention of this blog not to help people in need?

I keep wondering if this blog has any meaning to its users…

https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=80395732&objAction=Open&nexturl=%2Fknowledge%2Fllisapi%2Edll%3Ffunc%3Dll%26objaction%3Dbrowse%26objtype%3D142%26reqcacheid%3D967794657

appuq

This come every few years and I doubt if the CS Schema has changed too terribly over the years however this can be used as a starter.I an all these you would see me equate Kuaf.ID(id of a user) to the Ownerid of the column and that too multiplied by a -1,that is because the owner or volumeid of a volume(yes PWS are volumes so too are Projects,BWS,Enterprise and so on) and in DTree they have negative ownerids.

Admin and some other users are execption to this rule so you would count them separately

Both thsese queries I ran on an Oracle livelink so small changes between databases may have to be accounted.

select   k.name "Who is the User", sum(dv.datasize)/(1024*1024) as "Total Size (MB)", count(dv.datasize) as "Number of Items"

from     kuaf k, dversdata dv, dtree dt

where    k.deleted=0  //want only active

and k.type=0          //want only user type

and      dt.dataid=dv.docid

and      k.id=-dt.ownerid  //this is the owner of all those items in dtree

group by k.name

order by "Number of Items" desc

What I am doing is the alias 'k' is the usertable and I am saying give me all valid users(k.deleted=0 and type=0) the rest is counting DtTee and DVersData for each DataID that has multiple Versions in DTREE

This wont find the Admin users 1000 content beacuse that guy wont match the ownerid construct.

or this one that uses joins and in GB.

select k.name "User", k.lastname||', '||k.firstname "FullName", k2.name "BaseGroup",

to_char(SUM((dv.datasize/(1024*1024))/1024), '999,999.999999') "Size in GB" ,count(dv.datasize) as "Number of Items(including versions of all kinds)"

from kuaf k

join dtree dt on k.id = abs(dt.ownerid) /all users personal workspaces will match the userid and ownerid column negative who owns all content in a volume /

join dversdata dv on dt.dataid = dv.docid  /count all versions of a document reagrdless if it is a document,rendito or thumbnail beacuse anything can consume space/

join kuaf k2 on k.groupid = k2.id

where k.deleted = 0 and k.type = 0  / we are saying looking for a user type and he is active/

Group by k.name, k.lastname||', '||k.firstname, k2.name

Karen Weir

@appuq depending on the code entered, the spam filter may flag posts. We do monitor and moderate the community, however we may miss certain posts getting stuck in the filter - my apologies for missing yours.

Your post from Saturday is now available.