Audit Report Question

[Arsenal]

Hey All,

Suppose I needed to have an audit report which would exclude runs from certain users such as IT folks. From Cognos's audit package, I could use the username and create a filter with a whole list of user names which the report should not consider.

But suppose I wanted to exclude 2 or 3 groups instead of individual user names. How would I do that? The package doesn't provide any group query items directly, unlike for user names, it seems. Don't think #sq(CSVIdentityNameList())# would work either.

Thanks

[cognostechie]

You could create a Prompt with Static Choices, add the Group Names in there , then use a filter to compare the Prompt selection with the CSVIdentityName macro. Use the 'in' operator or 'not in'.

[Arsenal]

HI,

Prompt is not really an option, since the users want it to automatically filter out IT folks.

But, even if it was an option would you please elaborate a little bit more on your suggestion? Suppose we have IT folks split across say 3 groups. We do the static choice thing and I create 3 group names..but how do I compose a filter that will tell Cognos which of these groups I belong to, for example?

I think it might work if do a data item with a long case statement that puts folks in the right groups and then compare this data item to the prompt selection but that I think would be the same as basically composing a filter with a list of user names.

Doesn't seem to be a way of controlling by way of an LDAP group the way I was envisioning this report to be.

[Lynn]

I had a similar situation at a previous client. The ETL process maintained a table of users including many attributes such as the organization they belong to. I joined this to the audit data to allow more robust reporting on usage.

In addition to optionally filtering out IT people I could also provide metrics across groups by level (e.g., senior executive types vs. more junior admin types etc.), divisions within the firm, etc.

Previously this had been done with hard-coded filters in reports which, as you can imagine, changed frequently and was difficult to maintain.

If you think about your question very generically, user is a dimension relevant to audit data so creating a structure for that dimension is worthwhile.

[Arsenal]

Hi Lynn,

For now, we are going the user name route. Agreed about maintainence which is why I wanted to go down the groups route

Your suggestion is very interesting. How did you marry the ETL maintained table to the Cognos audit tables?

[Lynn]

I modified the FM model that included the audit tables to also include the user table.

Volumes were very small so I wasn't concerned about the local processing involved in joining multiple data sources.

It actually worked very nicely and I put a package together for the guy in charge of support so he could use Query Studio.

My favorite was when he told me a user had escalated something demanding IT do these reports for her because it was urgent and she had tried "seven ways to sunday" but couldn't get Cognos to work. He looked everything up and found it had been more than 8 months since she'd even logged in :D

[Arsenal]

Thanks Lynn. I was concerned about using multiple data sources at the back of my mind, hence why I wanted to know how you were handling the "marriage" of etl and cognos audit.

Really irritates me though that we have to jump through hoops to get this done. Why we can't have group name if we can have username is something which beats me

[Lynn]

If you have a good relationship with the database guys and a good DBMS (both things that can be elusive at some clients) then you might find a way to use something like a materialized view or even a view relying on a database link so that the objects are all in one source and avoid the local processing.

Another possibility is to think of the audit data as another fact subject area in a data mart situation. If you care about WHO is running things as well as WHAT is being run then there may be merit to the idea of ETL out of audit so it can be reported aound a conformed organization dimension.

[Steve]

or you could create a Parameter Map in Framework manager and put the User Names and Groups there.

[Arsenal]

Steve, that's a good suggestion but the problem of maintainence will still remain - what happens when users and security groups change, for example?

Lynn, I am going to explore your option. We have more audit requirements, so I think your way is the right way to go unless we want to maintain a lot of things, which we don't  ;)

There is a table that stores some user info including which LDAP group they belong to. Most of our reports have folder level security, although some have report level. So now, I need to figure out a way to match the LDAP group on the DB to the audit's object level security so that when run, the report can list out which reports a user has access to.

Maybe if not the audit then the CS might have this information.