Report on Users / Roles / Groups and Permissions

[swordfish]

Hey

Is it possible to generate a report on the users, their roles and permissions from cognos? We may have hundreds of users and maintaining this info manually would be highly time consuming and may also be error prone.

Content Store may have the information but is it possible to query on the content store for user security using SDK?

Does cognos provide (even sell) any proprietary programs for this task? I am using Cognso 8.2

Any inputs on this would be greatly appreciated!!!

SF

[COGNOiSe administrator]

Yes, it is possible with the SDK. And also a commercial application we've wrote delivers that information in PDF, TXT, CSV, HTML, among other things. Look us up on Cognos site under Powered By Cognos Solutions or drop me an email. Otherwise the adds on this site should guide you there too ...  ;D

Dear community, please take my appologies for going a tad commercial here, but we, myself and my employees, are being asked these very same questions alot.

[rocket]

You can also see more information on the Powered by Cognos solutions on COGNOiSe.com at http://www.cognoise.com/community/index.php?board=92.0

[megatrack67]

If you are using Oracle as content store DB

here are some homemade select that could give you some information

list of users

SELECT cmobjprops1.cmid, cmobjprops1.busphone, cmobjprops1.email,
          cmobjprops1.faxphone, cmobjprops1.givenname, cmobjprops1.homephone,
          cmobjprops1.mobilephone, cmobjprops1.pagerphone,
          cmobjprops1.paddress, cmobjprops1.surname, cmobjprops1.TIMEZONE,
          cmobjprops1.clocaleid, cmobjprops1.prodlocale, cmobjprops1.objid,
          cmobjprops1.useaccessibility, cmobjprops33.cmid AS cmid1,
          UPPER (cmobjprops33.NAME)
     FROM cmobjprops1, cmobjprops33
    WHERE cmobjprops1.cmid = cmobjprops33.cmid

List of Groups (List of English groups)

SELECT cmobjnames_base.cmid, cmobjects.pcmid, UPPER (cmobjnames_base.NAME),
          cmobjects.created, cmobjects.modified, cmobjects.disabled,
          cmobjnames_base.isdefault
     FROM cmobjects, cmobjnames_base
    WHERE cmobjects.classid IN (26, 54)
      AND cmobjnames_base.mapdlocaleid = 24
      AND cmobjects.cmid = cmobjnames_base.cmid

List of users by group

SELECT   v_group_user.user_id, v_user.ldap_id,
            LTRIM (RTRIM (UPPER (v_user.ldap_id))), v_group.cmid,
            v_group.name_en name_en, v_group.name_fr name_fr, v_group.created,
            v_group.modified, v_group.disabled, v_user.last_name,
            v_user.first_name, v_user.email
       FROM dba_group_info v_group,
            (SELECT cmid GROUP_ID, refcmid user_id
               FROM ops$cgs.cmreford1) v_group_user,
            (SELECT c33.cmid user_id, UPPER (c33.NAME) ldap_id,
                    c1.surname last_name, c1.givenname first_name,
                    c1.email email
               FROM cmobjprops1 c1, cmobjprops33 c33
              WHERE c33.cmid = c1.cmid(+)
             UNION
             SELECT cmid user_id, UPPER (name_en) ldap_id, name_en last_name,
                    '' first_name, '' email
               FROM dba_group_info) v_user
      WHERE v_group.cmid = v_group_user.GROUP_ID
            AND v_group_user.user_id = v_user.user_id(+)
   ORDER BY 1, 3

I hope this will help you

[swordfish]

Thanks a million. Looks like lot of effort went into it!

We have decided to use SDK to get this info...Also our requirement evolved to become more complex.

Can someone tell me, what cognos' stand is on querying content store ?

Swordfish

[josepherwin]

Modified the query a little bit to make it work if the Content Store is in SQL Server  :D

SELECT v_user.first_name, v_user.last_name, v_group.name, v_group_user.cmid as GROUP_ID,
   v_group_user.refcmid user_id FROM cmreford1 as 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 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
      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
WHERE v_user.first_name = INSERT FIRST NAME HERE
OR v_user.last_name = INSERT LAST NAME HERE
OR v_group.name = ENTER GROUP NAME HERE

[Redrichmond]

This is great.....Mine works too...I did asked Cognos support which tables the data came from and they couldnt help.

Do you know how I can produce a report of the scheduled reports and which failed...What table in the content store holds the scheduler information.

We are using SQL Server DB.

John

[josepherwin]

It's just a pain in the a** if you ask Cognos about Content Store, they protect it as if it was their biggest secret...

From my experience, all table with prefix NC_ relates to scheduling.

I cant tell you how each table relates to another though. You'll have to give it a go yourself.

just a thought, wouldnt the new Administration in 8.3 provide you with a better insights on scheduled jobs/what's running?

[Redrichmond]

Hi I know what you mean about IBMs secret Content store...

We cant run 8.3 as we found a bug which cognos are fixing for our windows version.

Strange though. I ran your SQL and got the list of users...

but I had a rogue user who was deleted from the access manager and is not in the group when you look in there in cognos 8??

[josepherwin]

The query that you ran only go through the Content Store thus, you'll only be able to find users who are on Cognos 8 (AD).

If you delete anything from Access Manager (which is series 7) as long as this guy still have a role in Cognos 8, it will appear in the query.

[Rocks]

Whats the bug that you found in 8.3?

[biejorrun]

I ran into this page on ittoolbox, maybe it could help:
http://it.toolbox.com/wiki/index.php/Cognos_Content_Store

--
Björn

[josepherwin]

Bjorn, I wouldnt rely on the explanation on that website.

One thing that you have to remember is that Cognos always do some changes in their Content Store in every upgrade.

Let say they usually store user information in Cogobject 1, in the next version they might decide on storing it in Cogobject 33.

[biejorrun]

Bjorn, I wouldnt rely on the explanation on that website.

One thing that you have to remember is that Cognos always do some changes in their Content Store in every upgrade.

Let say they usually store user information in Cogobject 1, in the next version they might decide on storing it in Cogobject 33.

Of course, you're right. I won't rely on it either, but it helped me understanding the database. I can't say I'm a Content Store-professional, but these kind of lists could point someone in a certain direction.

Kind regards,

Björn

[Ingmar]

If you are using Oracle as content store DB

here are some homemade select that could give you some information
List of users by group

SELECT   v_group_user.user_id, v_user.ldap_id,
            LTRIM (RTRIM (UPPER (v_user.ldap_id))), v_group.cmid,
            v_group.name_en name_en, v_group.name_fr name_fr, v_group.created,
            v_group.modified, v_group.disabled, v_user.last_name,
            v_user.first_name, v_user.email
       FROM dba_group_info v_group,
            (SELECT cmid GROUP_ID, refcmid user_id
               FROM ops$cgs.cmreford1) v_group_user,
            (SELECT c33.cmid user_id, UPPER (c33.NAME) ldap_id,
                    c1.surname last_name, c1.givenname first_name,
                    c1.email email
               FROM cmobjprops1 c1, cmobjprops33 c33
              WHERE c33.cmid = c1.cmid(+)
             UNION
             SELECT cmid user_id, UPPER (name_en) ldap_id, name_en last_name,
                    '' first_name, '' email
               FROM dba_group_info) v_user
      WHERE v_group.cmid = v_group_user.GROUP_ID
            AND v_group_user.user_id = v_user.user_id(+)
   ORDER BY 1, 3

I hope this will help you

I'm trying to run the above query, but I can't find the "dba_group_info" table. Neither in the cognos-schema, nor in any other schema on the database (including sys and system). A search with google didn't give me any clues either. Could you please tell me where I can find this table (of how I should create it)?

[ej]

Which version of Cognos 8 were you using?  I am on 8.3 and on Oracle 10g.  The first two scripts worked like a charm when run as the COGNOS_ADMIN user.  The third, however did not, as there does not seem to be a DBA_GROUP_INFO table or view under the COGNOS_ADMIN user.  I am unclear if this is because 8.3 does not have this table/view, or if something was not installed properly. 

Note to others who are on Oracle and are using these scripts, the groups script shows Cognos groups.  If you are using LDAP security instead of COGNOS security the information doesn't show in this script.

If you are using Oracle as content store DB

here are some homemade select that could give you some information

list of users

SELECT cmobjprops1.cmid, cmobjprops1.busphone, cmobjprops1.email,
          cmobjprops1.faxphone, cmobjprops1.givenname, cmobjprops1.homephone,
          cmobjprops1.mobilephone, cmobjprops1.pagerphone,
          cmobjprops1.paddress, cmobjprops1.surname, cmobjprops1.TIMEZONE,
          cmobjprops1.clocaleid, cmobjprops1.prodlocale, cmobjprops1.objid,
          cmobjprops1.useaccessibility, cmobjprops33.cmid AS cmid1,
          UPPER (cmobjprops33.NAME)
     FROM cmobjprops1, cmobjprops33
    WHERE cmobjprops1.cmid = cmobjprops33.cmid

List of Groups (List of English groups)

SELECT cmobjnames_base.cmid, cmobjects.pcmid, UPPER (cmobjnames_base.NAME),
          cmobjects.created, cmobjects.modified, cmobjects.disabled,
          cmobjnames_base.isdefault
     FROM cmobjects, cmobjnames_base
    WHERE cmobjects.classid IN (26, 54)
      AND cmobjnames_base.mapdlocaleid = 24
      AND cmobjects.cmid = cmobjnames_base.cmid

List of users by group

SELECT   v_group_user.user_id, v_user.ldap_id,
            LTRIM (RTRIM (UPPER (v_user.ldap_id))), v_group.cmid,
            v_group.name_en name_en, v_group.name_fr name_fr, v_group.created,
            v_group.modified, v_group.disabled, v_user.last_name,
            v_user.first_name, v_user.email
       FROM dba_group_info v_group,
            (SELECT cmid GROUP_ID, refcmid user_id
               FROM ops$cgs.cmreford1) v_group_user,
            (SELECT c33.cmid user_id, UPPER (c33.NAME) ldap_id,
                    c1.surname last_name, c1.givenname first_name,
                    c1.email email
               FROM cmobjprops1 c1, cmobjprops33 c33
              WHERE c33.cmid = c1.cmid(+)
             UNION
             SELECT cmid user_id, UPPER (name_en) ldap_id, name_en last_name,
                    '' first_name, '' email
               FROM dba_group_info) v_user
      WHERE v_group.cmid = v_group_user.GROUP_ID
            AND v_group_user.user_id = v_user.user_id(+)
   ORDER BY 1, 3

I hope this will help you

[Schmidty]

Guys

I ahve run this script and the third doesnt work. The dba_group_info doesnt exist. We are using 8.3

Any ideas ?


SELECT   v_group_user.user_id, v_user.ldap_id,
            LTRIM (RTRIM (UPPER (v_user.ldap_id))), v_group.cmid,
            v_group.name_en name_en, v_group.name_fr name_fr, v_group.created,
            v_group.modified, v_group.disabled, v_user.last_name,
            v_user.first_name, v_user.email
       FROM dba_group_info v_group



Doesn’t work. Dba_group_info doesn’t exist




            SELECT cmid GROUP_ID, refcmid user_id
               FROM ops$cgs.cmreford1 v_group_user



Doesn’t work. ops$cgs.cmreford1 doesn’t exist



            SELECT c33.cmid user_id, UPPER (c33.NAME) ldap_id,
                    c1.surname last_name, c1.givenname first_name,
                    c1.email email
               FROM cmobjprops1 c1, cmobjprops33 c33
              WHERE c33.cmid = c1.cmid(+)
           

Works



             SELECT cmid user_id, UPPER (name_en) ldap_id, name_en last_name,
                    '' first_name, '' email
               FROM dba_group_info) v_user
      WHERE v_group.cmid = v_group_user.GROUP_ID
            AND v_group_user.user_id = v_user.user_id(+)



Doesn’t work. Dba_group_info doesn’t exist

[tonico]

Hello,
Try this code for cognos 8.4. for Oracle Repository 10g. Disclaimer: I am not the creator of this code, it was found somewhere on the net, sorry I dont know the author....but I am grateful to him/her.

   SELECT
          v_user.first_name,
          v_user.last_name,
          v_user.objid AS user_id,
          SUBSTR (v_user.objid,
                  INSTR (v_user.objid, ':', -1) + 1,
                  LENGTH (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

[andy_mason_84]

Hi,

Does anyone know if you can get what capabilities a user has using this script?

I have a script that pulls the user and groups associated to each user ... but can you get capability info out too?

Cheers,

AM

[Grayson_Basil]

I just inherited Cognos security and there are about 50 customer groups and I have been tasked to provide a list of which user belongs to which group

Will this work and where does one enter the above sql?

Cognos 10.2.1
Oracle DB

Thanks