Cognos Administration 8.4 - GET CAMID for bursting table

[dtipser]

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.