Finding size of Personal workspace of all users using SQL 1

[balakumar86]

Guys,

Is there any way to find out the size of Personal workspace of all the users using SQL. If it is for a single user, I can find out using the obj id of the pws(by manually getting it using browser option for that user)and can calculate its size. If it's for all the users, it's impossible to get the obj id of pws of everyone. Is there a place where obj id of pws of all users get stored. Or is there any other easy way to do it? Please advise.

Regards,
Bala

 

[ggriffit]

If you look in the database all Personal Workspaces have the same Livelink Object Type (see

http://www.greggriffiths.org/livelink/reference/subtypes/)

using this you should be able to get a list of all Personal Workspaces in the system, then you would need to filter out those for users who have been deleted, but not had their workspace purged.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[balakumar86]

Thanks Greg. It gives me an idea to progress with this. Now I am at this stage. Please refer to the SQL below

select sum(datasize) from dversdata where docid in(
select dataid from dtree start with dataid in(select dataid
from dtree where subtype=142) connect by prior dataid=parentid)

This would give me the sum of size of all PWS. But, If I wish to group by users, How can I do it. Help me please.

Regards,
Bala

 

[ggriffit]

The simplest way to do this would be to create a temporary table and then use a cursor to move through that list and then populate the size for each row.

You may be able to do this in a single SQL statement, but I'll let someone who knows SQL better than me to come up with a SQL statement for you.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[appnair]

Post your existing query in the OT KB with what you are trying to acheive.Lindsay Davies might just do it for you he seems extremely and genuinely helpful.

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer and probably certfiable,Livelink ECM Champion 2008

http://www.linkedin.com/in/appunair

 

[balakumar86]

@Greg: Thanks. I'll try this with Cursor.
@Appnair: If I couldn't achieve this myself, I'll post in OT KB as you said.

Regards,
Bala

 

[balakumar86]

I have written both Query and PL/SQL code for this. It may be useful for someone.

select A.Name, a.id, a.mailaddress, a.firstname, a.lastname, SUM(b.DataSize)/(1024*1024)
from KUAF A inner join DTREE c on A.id = -c.OwnerID
inner join dversdata b on b.docID = c.DataID
where a.deleted = 0
Group by A.Name, a.id, a.mailaddress, a.firstname, a.lastname
Order by SUM(cast(b.DataSize as real))/1048576 DESC;

and the PL/SQL Code

declare
name varchar2(64);
kuafid number(10);
deleted number(10);
tempid number(10);
fname varchar2(64);
lname varchar2(64);
titl varchar2(64);
group varchar2(64);
pwssize number(10);

cursor cur is select dataid,ownerid from dtree where subtype=142;

begin

for row in cur
loop
kuafid:=-row.ownerid;
select sum(datasize)/(1024*1024) into pwssize from dversdata where docid in (select dataid from dtree start with dataid=row.dataid connect by prior dataid=parentid);

select name,firstname,lastname,title,groupid,deleted into name,fname,lname,titl,tempid,deleted from kuaf where id=kuafid;
select name into group from kuaf where id=tempid;
if deleted=0 then
insert into pwsdetails values(name,fname,lname,titl,pwssize,group);
end if;
end loop;

end;