Capturing Authentication Properties in Cognos 8

If you’ve done much metadata modeling in IBM Cognos’s Framework Manager, you’re probably aware that you can use attributes of the user from the authentication source (Active Directory, LDAP, etc.) to enhance security, through the use of session parameters and macro functions.  Most authentication sources contain a wealth of information about the user.  Common attributes include default name, last name, surname, phone number, and email address,  All of this can be accessed in Framework Manager, or even in a Report Studio report.

This is great if you need to know something about the current user, but what if you need to know details about all of your users?  A couple of examples of where this would be useful are in audit reports (listing report usage), or for email bursting.  These session parameters are of no use then, because they only exist while the user is logged in, and only for that session.

One option could be to extract the LDAP (export to a text file) and then transform and load it into your database.  If you have a cooperative network security administrator, this may be the cleanest way to go.  But if this is not practical (for whatever reason) then you could try capturing the data yourself as users log into Cognos 8.

The basic technique involves creating a database report that all of the users automatically run at login (I chose to use a “Warehouse Status Report”). Then using the Open Session Commands in the data source, running a stored procedure (passing in session parameters) that updates information about the user.

Add Custom Properties to Session Parameters

The default session parameters may work for you (if so, skip this section). But if not, Cognos 8 allows you to add custom properties and map them to your LDAP attributes.  This is done in IBM Cognos Configuration on the application server.

1. Start IBM Cognos Configuration.
2. Navigate to Local Configuration -> Security -> Authentication -> (your authentication source).
3. Scroll down to the bottom of the list and edit the Custom Properties.
   
4. Add any custom properties you may need, including the name of the LDAP attribute that it maps to.
   
5. Click OK to save the custom properties.
6. Click the Save button to save your configuration changes.
7. Stop, and then restart Cognos 8 to apply the changes.

Storing the User Information

The first thing you’ll want to do is create a table that will hold your user information.  I call my table User_Profiles. The example here is in Oracle 10g, but it should be easy to adopt this to your database.

CREATE TABLE USER_PROFILES( USER_NAME varchar2(256) PRIMARY KEY NOT NULL, DEFAULT_NAME varchar2(256), GIVEN_NAME varchar2(256), SURNAME varchar2(256), DEPARTMENT_NAME varchar2(256), OFFICE_NAME varchar2(256), STATE_NAME varchar2(256), BUSINESS_PHONE varchar2(256), EMAIL varchar2(256), RUN_LOCALE varchar2(256), LAST_LOGIN_DATETIME date ); CREATE UNIQUE INDEX USER_PROFILES_PK ON USER_PROFILES(USER_NAME);

Then you will create a stored procedure that takes in all the session parameters and performs an insert into the table if the user does not exist, or an update if the user does exist (sometimes  referred to as an “upsert”).  I called mine UPSERT_USER_PROFILES.

PROCEDURE UPSERT_USER_PROFILES ( p_USER_NAME USER_PROFILES.USER_NAME%type, p_DEFAULT_NAME USER_PROFILES.DEFAULT_NAME%type, p_GIVEN_NAME USER_PROFILES.GIVEN_NAME%type, p_SURNAME USER_PROFILES.SURNAME%type, p_DEPARTMENT_NAME USER_PROFILES.DEPARTMENT_NAME%type, p_OFFICE_NAME USER_PROFILES.OFFICE_NAME%type, p_STATE_NAME USER_PROFILES.STATE_NAME%type, p_BUSINESS_PHONE USER_PROFILES.BUSINESS_PHONE%type, p_EMAIL USER_PROFILES.EMAIL%type, p_RUN_LOCALE USER_PROFILES.RUN_LOCALE%type, p_LAST_LOGIN_DATETIME USER_PROFILES.LAST_LOGIN_DATETIME%type ) AS BEGIN MERGE INTO USER_PROFILES USING DUAL ON (USER_NAME = p_USER_NAME) WHEN MATCHED THEN UPDATE SET DEFAULT_NAME = p_DEFAULT_NAME, GIVEN_NAME = p_GIVEN_NAME, SURNAME = p_SURNAME, DEPARTMENT_NAME = p_DEPARTMENT_NAME, OFFICE_NAME = p_OFFICE_NAME, STATE_NAME = p_STATE_NAME, BUSINESS_PHONE = p_BUSINESS_PHONE, EMAIL = p_EMAIL, RUN_LOCALE = p_RUN_LOCALE,j LAST_LOGIN_DATETIME = p_LAST_LOGIN_DATETIME WHEN NOT MATCHED THEN INSERT ( USER_NAME, DEFAULT_NAME, GIVEN_NAME, SURNAME, DEPARTMENT_NAME, OFFICE_NAME, STATE_NAME, BUSINESS_PHONE, EMAIL, RUN_LOCALE, LAST_LOGIN_DATETIME ) VALUES( p_USER_NAME, p_DEFAULT_NAME, p_GIVEN_NAME, p_SURNAME, p_DEPARTMENT_NAME, p_OFFICE_NAME, p_STATE_NAME, p_BUSINESS_PHONE, p_EMAIL, p_RUN_LOCALE, p_LAST_LOGIN_DATETIME ); COMMIT; END;

(Note, this stored procedure uses Oracle’s  relatively new MERGE statement, but there are a number of ways to perform an upsert)

Create a Data Source That Calls the Stored Procedure

Of course, you could use any data source for the report, but I chose to create a new data source just for the login report.  The reason for this is, I didn’t want to update the USER_PROFILE table every time I ran a report, just when they first logged into Cognos 8.

1. Open up Cognos 8 and launch IBM Cognos Connection.
2. Click on the Configuration tab.
3. Create the data source that will connect to the database containing the data for your login report.  This assumes that this is the same database that will hold your USER_PROFILES table and UPSERT_USER_PROFILES procedure.
   (You’re on your own here)
4. After you’ve created the data source, edit the properties of the data source.
5. Click on the Connection tab
6. Edit the Open session commands (more on this below*)
7. Add a command block that will call the stored procedure, passing in the session parameters
   

   <commandBlock> <commands> <sqlCommand> <sql>BEGIN DW.UPSERT_DW_USER_PROFILES(#sq($account.personalInfo.userName)# , #sq($account.defaultName)#, #sq($account.personalInfo.givenName)#, #sq($account.personalInfo.surname)#, #sq($account.parameters.departmentName)#, #sq($account.parameters.officeName)#, #sq($account.parameters.stateName)#, #sq($account.personalInfo.businessPhone)#, #sq($account.personalInfo.email)#, #sq($runLocale)#, TO_DATE( substr( #sq($current_timestamp)#, 1, 19), 'YYYY/MM/DD HH24:MI:SS' ) ); END; </sql> </sqlCommand> </commands> </commandBlock>

Why use the Open Session command as opposed to the Open Connection command?  Cognos 8 shares connections among it’s users.  One user may create the connection, but there could be hundreds of reports using that connection before it times out through inactivity.  So if you were to use the Open Connection command, only that first user who started the connection would be recorded.

Create the Package and the Login Report

If you’ve read this far, I assume you know how to create a package, add a query subject to it and develop a report in Report Studio or Query Studio.  The important thing to remember her is that this report should be the only place where you use the data source we just created.

Create a Page

I’m also assuming you know how to create pages in Cognos 8.  Put whatever you need into the page, but be sure to add a Cognos Viewer portlet to the page, and set the portlet’s Entry to the Login report you created.

Make Sure They See the Login Report

There are a number of ways to make sure that your users see the login report (none of them perfect). But if this is a new Cognos 8 installation, it’s easy to set the page to be the default tab.  This way, each new user will see the page when they first log in.

1. Open up Cognos 8 and launch IBM Cognos Connection.
2. Click on the Security tab.
3. Click on the Cognos namespace.
4. Click the Edit Default User Profile button.
5. Click the Portal Tabs tab.
6. Add the page you created to the list of tabs.
7. Click Modify the sequence, and move it to the top.

If this is an existing installation, then the above method will not work as it only applies to new users.  Alternatives include:

• Sending a broadcast email to the users with a link to the page.
• Manually going through the authentication source namespace and changing each user.
• I have also heard of third-party metadata managers that can change user portal pages in bulk (sorry, but I have no experience with these).