The Cognos Content Store database has a lot of valuable information, however, it is not always so easy to find this information. The following query can be useful for returning USER, GROUP, and ROLE information from the Cognos Content Store database.
Be aware the database structures can be changed with any any patch, fixpack or hotsite to Cognos and the SQL may fail or return wrong results. There is a better method for returning this information by using the Cognos SDK. More info here.
Content Store version:
Cognos 8 running on an Oracle database (also works on Cognos 10)
--============================================================= -- COGNOS CONTENT STORE QUERY (Oracle database) -- Use to find list USERS, ROLES and GROUPS information --============================================================= WITH base_query AS (select classid, cmobjects.cmid, cmobjects.pcmid, cmobjnames.name as groupname from cognos_c84.cmobjects , cognos_c84.cmobjnames where cmobjects.cmid = cmobjnames.cmid and classid in (26,54) and cmobjnames.mapdlocaleid = 24 union select classid, cmobjects.cmid as cmid, cmobjects.pcmid, cmobjnames.name as groupname from cognos_c84.cmobjects , cognos_c84.cmobjnames where cmobjects.cmid = cmobjnames.cmid and cmobjects.cmid in (select distinct cmobjects.pcmid from cognos_c84.cmobjects) and cmobjnames.mapdlocaleid = 24) , ctebuildpath (cmid,pcmid,groupname,Hlevel,classid,directory_path) AS (select base_query.cmid, base_query.pcmid, base_query.groupname, 1 as Hlevel,classid, to_char(base_query.groupname) as directory_path from base_query where groupname = 'Cognos' union all select recur.cmid, recur.pcmid, recur.groupname, cte.Hlevel+1 as Hlevel, recur.classid, to_char(cte.directory_path || ' > ' || recur.groupname) as directory_path from base_query recur, ctebuildpath cte where cte.cmid = recur.pcmid) SELECT v_user.first_name, v_user.last_name, v_user.ldap_id as user_full_name, v_group.name, v_group.directory, v_group_user.cmid as GROUP_ID, classid, v_group_user.refcmid user_id FROM cognos_c84.cmreford1 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 cognos_c84.cmobjprops1 c1 left outer join cognos_c84.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 cognos_c84.cmobjprops1, cognos_c84.cmobjprops33 WHERE cmobjprops1.cmid = cmobjprops33.cmid) v_user ON v_group_user.refcmid = v_user.user_id LEFT OUTER JOIN (SELECT ctebuildpath.cmid cmid, UPPER(ctebuildpath.groupname) name, ctebuildpath.directory_path as directory, classid FROM ctebuildpath) v_group ON v_group.cmid = v_group_user.cmid
Thanks to Cognos with Hari for putting this together on a SQL Server Content Store.