Personal Workspace Query.
Message from Benjamin J. Shapiro via eLink Hi LiveReports users! One particularly nice LiveReport is the ability to see how much spaceeach user is using in their Personal Workspaces. There was one posted onthis discussion May 9, 2000, but I thought it was time to send it again.Actually, this one is much faster though remaining flexible:---select b.name, b.mailaddress, sum(a.datasize), from dversdata a, kuaf b,dtree cwhere a.docid = c.dataidand c.userid=b.idand a.docid in (select dataid from dtree where subtype=144 start with dtree.dataid in (select dataid from dtree where subtype=142) connect by prior dtree.dataid = dtree.parentid)group by b.name, b.mailaddress order by sum(a.datasize) desc;--- And it's easily modified!--->select b.name, b.mailaddress, sum(a.datasize), from dversdata a, kuaf b,dtree c- If you want more information about the person, add b.firstname - be sureto add it in the "group by" at the end!- Note as well - if you remove the "b.mailaddress", it can make a nifty pieor bar chart! Oooo!>where a.docid = c.dataid>and c.userid=b.id- This lists all users. To restrict to undeleted ones, add "andb.deleted=0". Though sometimes deleted users have lots of items lyingaround that will never be used.>and a.docid in> (select dataid from dtree> where subtype=144- This selects only documents. However, changing it to something like"where subtype > 0" will get everything except folders that have size.> start with dtree.dataid in> (select dataid from dtree> where subtype=142)- Subtype 142 is the personal workspace. Consider that if you want, you cansee the total size of items in the Enterprise volume as well! Simply add:"or dataid=2000" (note: it will be 2001 if you upgraded from Livelink 7).This might put a bit of a load on the database.> connect by prior dtree.dataid = dtree.parentid)- This is the magic line that makes me suspect it might not work with MS-SQLserver.>group by b.name, b.mailaddress order by sum(a.datasize) desc;- As earlier - if you wish any additional information, be sure to group ithere.--- Note that this counts the space used by all versions of the documents aswell! Happy snooping! Cheers, Benjamin Shapiro--Benjamin J. Shapiro, BMathSenior ConsultantEnterprise Solutions Group--Open Text AGEschenstrasse 39004 St. Gallen - SwitzerlandPhone: ++41-(0)71-2278500Fax: ++41-(0)71-2278585mailto:bshapiro@opentext.com
