Remove unsuccessful login entries labeled as "Not Available" from Cognos Analytics audit package

When we tried to find out the logon information using the audit package in Cognos Analytics then we noticed that the user name details are showing as 'not available' in the audit report.

Detailed explanation:

When the logging level for ContentManager Service is set to BASIC basically every call to "logon" is recorded in the COGIPF_USERLOGON table within the audit db.

What's important to understand is, that those calls by design might "fail" which does not necessarily indicates an error condition but is part of the designed protocol

for authentication handling. For example, when a client opens the browser to access CA, the system will re-direct to the login screen. This however only happens after

a call to "logon" was sent and failed for one of several possible reasons like (paraphrasing for sake of explanation) "user must select a namespace" , "invalid credentials", "bouncing back for SSO".

Without going into detail, these failures are partially expected and usually get handled by the UI by presenting the required UI , like namespace selection, to the user.

That said, there are calls which succeed and those which fail. The COGIPF_USERLOGON table captures all of those. There is the COGIPF_STATUS column which will have "Failure" or "Success" value.

Only in case of "Failure" the column COGIPF_ERRORDETAILS is populated as well, which then contains error details in XML format. For any of those rows which is for a failed request, there will be no

values for COGIPF_USERNAME & COGIPF_USERID but instead "not available" is set.

If one filters out those rows by selecting only succeeded requests, all rows DO show username and userid correctly. The COGIPF_LOGON_OPERATION column shows the actual operation performed

as the function "logon" (unfortunately ambiguously) can perform several operations. There are "logon", "logon expired" and "logoff" and some more.

Unfortunately the reports and model delivered as part of the sample "Audit" deployment do not properly represent those details. The "Logon Operations by Timestamp" report for example attempts to show one line per user session and therefore applies some grouping and self-joining. This mostly works but if looking at the tabular data there are occurrences of negative session duration and lines with "not available" for example.

To verify the above, simply use some external SQL client like Squirrel and send this query:

select COGIPF_LOCALTIMESTAMP,COGIPF_SESSIONID,COGIPF_REQUESTID,COGIPF_SUBREQUESTID,COGIPF_LOGON_OPERATION,COGIPF_USERNAME,COGIPF_USERID from COGIPF_USERLOGON WHERE COGIPF_STATUS='Success'

This provides a clean list of rows , one for login, one when the session expires or is ended by explicit logoff.

Another glitch is, that when accessing the product with an already expired session cookie (the sessions expire server side, so client will continue to send the cookie) there will be a "failure" request in the audit log,

which uses a sessionID who's last entry otherwise was "Logon expired". This is simply the result of the client trying to use the by now expired session.

As one can see form the schema of the table, there is no logoff timestamp, this only exists in the model of the Audit package and must be calculated to the timestamp which has either "logon expired" or

"logged out" for that same sessionID. Alas I don't think this works properly currently.

To sum it up: to audit logging in and out (explicitly or by expiry) one would need to adjust the audit package. Since this is a "sample" provided "as-is" I suggest to create a new model in FM and work along the

recommendations I made above.