While working with a client on a Cognos upgrade effort, I was asked to provide a list of each report by the package it sourced. Since I am a database fanatic I prefer to get this information directly from the Cognos content store. Ultimately, I was able to provide a list with each Report name, package and path for each report by using the query provided below.

The Cognos content store has a lot of valuable information. Unfortunately, it is not always so easy to find this information. Have you ever been asked where a specific report can be found in Cognos? Or better yet, have you had to find all the existing reports that use a certain package? Below you will find a query useful for finding Report Name, Package and Path information from the Cognos content store database.

Let us know in the comments if you have done something similar or encountered any challenges around using the content store as a data source.

This example works with Cognos 10 running on an Oracle database. In addition, it will also work on the Cognos 8 content store.

--=============================================================
--  COGNOS CONTENT STORE QUERY (Oracle database)
--  Use to find list REPORT_NAME, PACKAGE and SEARCH-PATH
--=============================================================
WITH base_query AS
(select classid,
         cmobjects.cmid,
         cmobjects.pcmid,
         cmobjnames.name as report_name
    from cognos_c84.cmobjects, cognos_c84.cmobjnames
   where cmobjects.cmid = cmobjnames.cmid
     and classid in (10)
     and cmobjnames.mapdlocaleid = 24
  union
  select classid,
         cmobjects.cmid  as cmid,
         cmobjects.pcmid,
         cmobjnames.name as report_name
    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,report_name,Hlevel,classid,directory_path) AS
(select base_query.cmid,
       base_query.pcmid,
       base_query.report_name,
       1 as Hlevel,
       classid,
       to_char(base_query.report_name) as directory_path
  from base_query
where report_name = 'Public Folders' /* add myfolders to retrieve reports from my folders of users */
union all
select recur.cmid,
       recur.pcmid,
       recur.report_name,
       cte.Hlevel + 1 as Hlevel,
       recur.classid,
       to_char(cte.directory_path || ' > ' || recur.report_name) as directory_path
  from base_query recur, ctebuildpath cte
where cte.cmid = recur.pcmid)
, report_spec AS
(select spec,
       cmid,
       instr(spec,'/package[@name=')+15 as startposition,
       instr(spec, ']/model[@name=')-instr(spec,'/package[@name=')-15 as endposition
  from cognos_c84.cmobjprops7
where spec is not null)
--
SELECT report_name,
       dbms_lob.substr(spec, endposition, startposition) as package,
       directory_path
  FROM ctebuildpath cte, report_spec rep
WHERE cte.classid = 10
   and cte.cmid = rep.cmid
order by directory_path, report_name

Also see our User, Group, and Role query

Be aware that future patches or hotfixes from the vendor may cause this SQL to fail or return incorrect results. There is a better method for returning this information by using the Cognos SDK. More info here.

Thanks to Cognos with Hari for putting this together using SQL Server Content Store.

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.

Many of my client engagements ask for details around their users and roles, especially during annual audit time. I often find the quickest way to obtain these details is to query the Cognos content store directly. Using the script below I am able to provide each user along with their group and role assignments from within the Cognos content store.

Content Store version:
Cognos 10 running on an Oracle database (also works on Cognos 8)

--=============================================================
--  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

Be aware the database structures can be changed with any  patch, fixpack, or hotfix 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.

Also see our Report Package and Path query.

Thanks to Cognos with Hari for putting this together on a SQL Server Content Store.