hi all,
hopefully someone can help me
We use two namespace authentications for our cognos-platform (Cognos-Namespace + ActiveDirectory). We use a sql againts the content store to show all groups, users and permissions.
sql-example to select one usergroup:
select cn.NAME as GRUPPE, op.NAME as BENUTZER
from CONTENT_STORE.cmreford1 ro
inner join CONTENT_STORE.cmobjnames cn on ( cn.CMID = ro.CMID and cn.MAPDLOCALEID = 10 )
inner join CONTENT_STORE.cmobjprops33 op on ( op.CMID = RO.REFCMID )
where CN.NAME = '[group]'
But if the group contains an active directory group, we won't see the users of this group, only a timestamp. Is it possible to select these users?
Thanks,
mike.son
The SQL below may not give everything you need but may help:
use CognosContent -- name of content store db
SELECT v_group.name as group_name,
v_user.ldap_id
FROM cmreford1 as v_group_user JOIN-- GET USERS
(SELECT cmid user_id,UPPER (NAME) ldap_id
FROM cmobjprops33)
v_user
ON v_group_user.refcmid = v_user.user_id JOIN--GET GROUPS
(SELECT cmobjnames.cmid cmid,UPPER (cmobjnames.NAME) name
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
order by 1,2
--Source ittoolbox.
hi Suraj Neupane,
thanks for your help. But i resolve the problem with a perl-script. ;D
Now i have the chance to show all groups (including Groups that contains an active directory group) and users in our Content Store. The script uses different CS_Tables amongst others:
CMREFORD1 - Relationship between parent and child
CMOBJPROPS1 - OBJID from AD-GROUP
CMOBJNAMES - Names of the Objects
CMOBJPROPS33 - Users
greets
mike.son
Hi Mike,
I try to do the same thing. And i can't.
What other tables are you using to do this request ?
Is it possible to do this without using perl script ?
Thank you for your response.
Olivier.MAHE