Hi,
We have Cognos 8.4 ans SQL Server 2008 for COGNOS_CONTENT_STORE database. I tooked an SQL script used in Oracle, I made some small modification for SQL Server in the statement, and I was able to find the name of AD users and CAMID associated in COGNOS. Since we need to create a bursting table and I didn't want to insert manually all CAMID's, this script accomplished my needs. Thanks for peoples that provides all kind of scripts to help the users like me to undestand more COGNOS_CONTENT_STORE database.
SELECT v_user.ldap_id,
--v_user.first_name,
--v_user.last_name,
'CAMID("' + v_user.objid + '")' AS user_id,
-- SUBSTRING(v_user.objid,
-- CHARINDEX(v_user.objid, ':', -1) + 1,
-- LEN(v_user.objid)
-- ) AS user_guid,
v_group.NAME AS group_name,
v_group_user.cmid AS GROUP_ID,
v_group_user.refcmid user_id
FROM cmreford1 v_group_user
INNER JOIN
(SELECT c33.cmid user_id, UPPER (c33.NAME) ldap_id,
c1.surname last_name, c1.givenname first_name,
c1.email email, c1.objid
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,
cmobjprops1.objid
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
ORDER BY GROUP_name
Hope that this script for SQL Server 2008 helps other Cognos users.