Determine what reports a user is assigned to

[gats1527]

I was wondering if there is a way to see what scheduled reports a user is assigned too?  We have a user leaving the company and i want to make sure his replacement is included in all the scheduled reports.  Does anyone know of a way to retrieve this information rather then going thru each and every report?

Thanks

Tom

[josepherwin]

I dont think that there's any easy way to do this.

The only thing I can start suggesting is to start developing a new method to send reports by using user groups instead of individuals.

That way, when someone leave the company, all you need to do is assign the new person to all groups that the leaving person belong to.

There was a thread somewhere which I post the SQL which would allow you to find out which users belong to which group.

[gats1527]

Thanks for the information.  I have started to create distribution groups but even that is hard to determine what group a certain user is in.

[jive]

Hi gats,
We used the same method here,by group, but when a user quit or arrive in the company is superior have to send to HR/IT what It will have access to, and in the security we read ldap, the group aare assign. So, normally each of our user have is bind to a specific group. But the most important thing is when a user leave it's name is delete from the group.

[josepherwin]

Thanks for the information.  I have started to create distribution groups but even that is hard to determine what group a certain user is in.

This is where this SQL comes in handy :) Run this against your Content Store database and it will give you a list of group that a particular user belongs to.

The below SQL ran on SQL Server 2003, so you may need to tweak it if you got different Content Store database

SELECT v_user.first_name, v_user.last_name, v_group.name, v_group_user.cmid as GROUP_ID,
   v_group_user.refcmid user_id FROM cmreford1 as v_group_user
LEFT OUTER JOIN (SELECT c33.cmid user_id, UPPER (c33.NAME) ldap_id,c1.surname last_name, c1.givenname first_name,c1.email email
      FROM cmobjprops1 c1 left outer join  cmobjprops33 c33             
      on c33.cmid = c1.cmid
      UNION
      SELECT cmobjprops1.cmid user_id, UPPER (cmobjprops33.NAME) ldap_id, cmobjprops1.surname last_name, cmobjprops1.givenname first_name, cmobjprops1.email
      FROM cmobjprops1, cmobjprops33
      WHERE cmobjprops1.cmid = cmobjprops33.cmid) v_user
ON v_group_user.refcmid = v_user.user_id
LEFT OUTER JOIN (SELECT cmobjnames.cmid cmid, UPPER (cmobjnames.NAME) name,
             cmobjects.created created, cmobjects.modified modified, cmobjects.disabled disabled
           FROM cmobjects, cmobjnames
          WHERE cmobjects.classid IN (26, 54)
            AND cmobjnames.mapdlocaleid = 24
            AND cmobjects.cmid = cmobjnames.cmid) v_group
ON v_group.cmid = v_group_user.cmid
WHERE v_user.first_name = INSERT FIRST NAME HERE
OR v_user.last_name = INSERT LAST NAME HERE
OR v_group.name = ENTER GROUP NAME HERE