List of Cognos Users

[CognosPolzovatel]

Is it possible to quickly/easily extract a list of the Cognos users (that are part of the Cognos directory)? If so, how? Can this be automated? (I would regularly need to extract an updated list.)
I.e., the users who have a particular cognos license.
Even better would be a report of:
Cognos users and what license type each of them have (Report Studio, Query Studio, or Consumer license).

Thanks.

[CognosPolzovatel]

I guess my question is also:
Where in Cognos do the users get stored (on the DB-level). I know that I can take a look at the Cognos users via the Cognos Connection > Tools > Directory. Inside one of the Cognos manuals, I am reading the following:

"A user entry is created and maintained in a third-party authentication provider to uniqely identify a human or a computer account. You cannot create user entries in Cognos 8."
"Information about users, such as first and last names, passwords, IDs, locales, and email addresses, is stored in the providers. *However, this may not be all the information required by Cognos 8. For example, it does not specify the location of the users' personal folders, or format preferences for viewing reports. This *additional information about users is stored in Cognos 8..." Where in Cognos 8?

It looks like we have our users setup via LDAP. However, how can I find (even on the 3rd-party authentication provider) the list of the *Cognos users (only Cognos)? 

[CognosPolzovatel]

Well, I kept on searching for the answer and found a posting on ITtoolbox in regards to a similar question/request:
http://businessintelligence.ittoolbox.com/groups/technical-functional/cognos-admin-l/generate-list-of-cognos-users-1388963?cv=expanded
(Note: If not already expanded, you can expand the responses/threads for the posting and scroll towards the bottom to read all of the responses.)

It turns out, ITtoolbox has a Cognos group that you can register for and share your Cognos knowledge with the others and/or post your questions. Thought perhaps this may help some one out in the future and so - decided to post the link.

[larsonr]

If I understand your question correctly, you are looking for anyone who has used the Cognos system essentially.

Your authentication provider allows for users to basically have a method for existence.  Cognos's namespace is used to create custom roles/groups and use existing ones for their default capabilites (assign users to query studio, report studio, etc...) and security settings.

To answer your question, you really want to extract who belongs to the Cognos groups and roles you have assigned -or - to the groups and roles in your authentication provider that you are leveraging in your Cognos environment.

In my first environment, we used only Cognos roles and groups to define security and capabilities.  Really the only good method is to use the SDK to parse out each member of the Cognos roles. 
We then chose to store them in a database. 

At my new place of work, we use pretty much only those in the authentication provider.  At that point, you can use either Cognos's SDK or some other tool to parse out who belongs to what role or group in that provider.

If you are looking for just a particular subset and need it quickly (without SDK intervention) just check every role/group that was assigned with that capability (Report Studio, Query Studio) and copy,paste your user membership selection.

[CognosPolzovatel]

Hello user larsonr:

"If you are looking for just a particular subset and need it quickly (without SDK intervention) just check every role/group that was assigned with that capability (Report Studio, Query Studio) and copy,paste your user membership selection."
Yes, up to now, this was the way that we were keeping track of our Cognos users and their roles. I.e., initially the users and their license type were documented/extracted from the Cognos directory (inside the Cognos Connection). A spreadsheet was created and as new users are added, the spreadsheet is kept up to date. I don't like relying on a spreadsheet and hence, went in sought of an automated method. I was surprised to learn that Cognos does not provide this capability out-of-the box. I have the Audit package setup now. However, in order to get the desired report, it looks like I will indeed need to use the SDK. And so, the SDK will need to be installed. We are currently using Cognos roles and groups (not LDAP/AD roles).

[rocket]

Have you had a look at the documentation feature of MetaManager?  You can selectively pick what to document not only from the Content store (all content, who has access to what content, what users are a member of what roles / classes), but also document FM Models. The resulting documentation can be captured in XLS, PDF, TXT and HTML formats.

Have a look at this (and the other 8 modules) in the Powered by Cognos section of COGNOiSe at http://www.cognoise.com/community/index.php?topic=3008.0.  You can also see a recording of the product at http://www.bspsoftware.com/videoRequest/index.php.

[larsonr]

Not to be a buzz kill, but that seems like plug for a piece of software not issued by Cognos (endorsed but not issued).  The forums purpose is to help each other but this almost seemed like a sales pitch.  I understand that BrightStar did start this up and is entitled, but I would like to empower this individual to learn more about the Cognos backend than to be offerred a gui.  There is more to learn sometimes other than the fact that a very nice extension exists.

[rocket]

larsonr -

First of all, thank you for the open and honest feedback on my post.  The goal of this site is to do what's in the site member's best interest, so I want to post my thoughts and then a question.

My opinion of the forum's purpose is to provide support and exposure to techniques, ideas, and solutions that make Cognos Administration and development easier for all of us. Powered by Cognos solutions are endorsed by Cognos, and with many people around the world not knowing they exist, posting information about them here (where appropriate) provides exposure to those folks in the community that need / desire to consider purchasing solutions to solve implementation pains.

Though many folks on the site work in organizations that have the funding and resources to support SDK development to enable Cognos do things outside of the delivered functionality, many do not and are in need of a cost effective, packaged solution to address these issues. 

So my question to the community is whether each of you feel this type of post is beneficial or not.  The goal is to do what's best for the community, and if the community agrees that this type of post is not in the best interest of the forum, I will 100% endorse the policy to prevent it.

Thoughts?

[COGNOiSe administrator]

Actually, BrightStar was only one of the many initial contributors to make this forum alive with ideas and advices. Later on, with great pride and excitment, we took the offer made by the original owner, to keep caring for this and many other communities. We did it without a single thought of financial reward.

We hope that the amount of Google advertisemnt and our own occassional recommendation for a product we've created, is acceptable to the community. There are other independent Cognos communities out there, which offer similar content in exchange for more aggressive advertising in my opinion.

I want to offer my support to rocket posing the question of what does the community think.  We do our best to sometimes think for the community, but our goal is to let the community be driven by the community and we're open to setting a policy on this based on the vote of the members

[larsonr]

I guess my personal opinion is based off my experience from other user forums.  It was often acceptable to point out a product existed and where to find it -- usually they were dubbed as the quick solution if users could not afford the time to develop their own. The atypical was to post something along the lines of full product descriptions in the posts.

I do value Bright Star's impact on the user community to be able to deliver solutions as well as impact on the forum. I think its great.  I was just blown away by seeing a full ad in regards to metamanager and its capabilities. 

If this forum were to allow for these posts to be acceptable, thats great. It does open up where we may find consulting groups and others trying to make similar types of posts or those seeking clients.  Competition is good but I do not know if it should exist in this corner of the web in the form of posts. I value the community's opinion and will gladly adhere to how they wish they handle the question at hand.

[rocket]

We will hold off on further "marketing" posts until we find a good way to check with the members on their opinion.  Thanks for giving yours.  It is much valued...

[CognosPolzovatel]

Perhaps there should exist a separate forum topic/category for cognos-related products in the main forum page :
http://www.cognoise.com/community/

That way, if users are interested in researching what products/tools are out there to assist them in the cognos administration, they can visit that particular category and post their own questions or read what others have posted.

Just a suggestion I guess.

[COGNOiSe administrator]

There already is a Powered By section on this forum.

[cmills]

To me, it is valuable information for me to know that a particular third-party solution exists to this very real need for easier license management of Cognos (ie. list of Cognos users).   Any product or development help that can solve a need should be fair game for the forum IMHO.

I did not personally feel I was being bombarded with marketing hype by Rocket's short reply.  The only suggestion I might make is that Rocket might consider identifying himself in his signature line or profile as a representative of BSP.

Thanks very much

[megatrack67]

If you are using Oracle 9i or 10g  here are some select to list all users, groups and user by groups

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/roles (WIth English names)

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 groups

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

[CognosPolzovatel]

Thanks for the scripts megatrack67.

However, have a few questions:

The first select that you provided is supposedly the list of users. However, the result for me brings back users that in reality do not have a Cognos license (they do not belong to any of the roles that allow them to access any of the Studios or any other capability). It appears that this method brings back users that have logged in (at least once) into Cognos, not taking into account whether or not the user currently has a Cognos license (belongs to a Cognos role: Consumer, Author, Query User, etc.).

If one goes to Cognos > Tools > Directory > and the defined namespace (in my case it's an LDAP one), users that appear highlighted in blue (and underlined) are users that logged in at least once into Cognos for the course of their life. This doesn't necessarily mean that this user has a Cognos license.

The SDK approach, however, goes through each of the *roles* to obtain a list of the Cognos users.

[CognosPolzovatel]

One more thing:
I just checked my CMOBJPROPS33 table from the Cognos Content store and i have approximately only 26 users in their. Why doesn't the table display all of my Cognos users? How does this table get populated?