List subscriptions per user...

We are currently upgrading from 5.3 SP5 to 6.5 SP3 and would like a script to count all the subscriptions per active user in both 5.3 and 6.5.

With a few users there seems to be a discrepancy in the subscriptions (less) after the upgrade.

Thank you.

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

KSMatson

I understood what you meant by subscriptions, try this query and change the <username> to the desired user. which is the user_name attribute of the dm_user object, the query will give you the document/folder that user has subscribed to.

select u.user_name,c.r_object_id, c.object_name
from dm_sysobject c, dm_user u, dm_relation r 
where r.relation_name = 'dm_subscription' 
and u.user_name = '<UserName>' 
and u.r_object_id = r.child_id  
and r.parent_id = c.r_object_id   
union 
select u.user_name, s.r_object_id, s.object_name
from dm_document s, dm_user u, dm_relation r 
where r.relation_name = 'dm_subscription' 
and u.user_name = '<UserName>'
and u.r_object_id = r.child_id 
and r.parent_id = s.r_object_id;

For all users use this query instead, note that you might see duplicates object names which only means that multiple users have subscribed to the same document/folder

select u.user_name,c.r_object_id, c.object_name
from dm_sysobject c, dm_user u, dm_relation r 
where r.relation_name = 'dm_subscription' 
and u.user_name in (select user_name from dm_user 
where r_is_group = 0)  
and u.r_object_id = r.child_id  
and r.parent_id = c.r_object_id and not type(dm_document)  
union 
select u.user_name, s.r_object_id, s.object_name
from dm_document s, dm_user u, dm_relation r 
where r.relation_name = 'dm_subscription' 
and u.user_name in (select user_name from dm_user 
where r_is_group = 0)
and u.r_object_id = r.child_id 
and r.parent_id = s.r_object_id;

All comments

KSMatson

Are you looking for something like this, the query will work on btoh 5.3 and 6.5 versions

select user_name, registered_id, event, sendmail 
from dmi_registry where is_audittrail = 0 
and user_name in (select user_name from dm_user 
where user_state = 0 and r_is_group = 0);

msroth

Or, do you mean this kind of subscription?

select count(*), u.user_name from dm_relation r, dm_user u where r.relation_name = 'dm_subscription' and r.child_id = u.r_object_id group by user_name

rpowid

Yes, these type of Webtop Subscriptions....

When I look at my results from your query it shows 25 and yet in Webtop - Subscriptions I only see 8!

Any reason for this discrepancy?

Thanks.

KSMatson

I have combined my query with Ross's query to give you a better break down.


select count(*) as event_cnt, u.user_name, g.event 
from dm_relation r, dm_user u, dmi_registry g
where r.child_id = u.r_object_id
and g.user_name = u.user_name
and r.relation_name = 'dm_subscription'
and g.is_audittrail = 0 
and u.user_state = 0
and u.r_is_group = 0
group by u.user_name, g.event;

rpowid

To clarify that we are on the same page.... Subscriptions - found when you log into Webtop - third option under "Repository Name: user name".

When I run this code in my Production environment I get twenty line results - I have 300 active users.

My own account results shows 4 entires, 25 each for dm_lock, dm_checkout, dm_checkin and all.

I have no documents checked-out and only 8 in my Subscriptions.

Thank you.

KSMatson

select u.user_name,c.r_object_id, c.object_name
from dm_sysobject c, dm_user u, dm_relation r 
where r.relation_name = 'dm_subscription' 
and u.user_name = '<UserName>' 
and u.r_object_id = r.child_id  
and r.parent_id = c.r_object_id   
union 
select u.user_name, s.r_object_id, s.object_name
from dm_document s, dm_user u, dm_relation r 
where r.relation_name = 'dm_subscription' 
and u.user_name = '<UserName>'
and u.r_object_id = r.child_id 
and r.parent_id = s.r_object_id;

For all users use this query instead, note that you might see duplicates object names which only means that multiple users have subscribed to the same document/folder

select u.user_name,c.r_object_id, c.object_name
from dm_sysobject c, dm_user u, dm_relation r 
where r.relation_name = 'dm_subscription' 
and u.user_name in (select user_name from dm_user 
where r_is_group = 0)  
and u.r_object_id = r.child_id  
and r.parent_id = c.r_object_id and not type(dm_document)  
union 
select u.user_name, s.r_object_id, s.object_name
from dm_document s, dm_user u, dm_relation r 
where r.relation_name = 'dm_subscription' 
and u.user_name in (select user_name from dm_user 
where r_is_group = 0)
and u.r_object_id = r.child_id 
and r.parent_id = s.r_object_id;