Statistics of run report through audit db?

[charon]

Hiho everyone,

some days ago a client asked me if there is a "simple" way to get statistics on how many reports, which reports and by who these reports has been run (maybe even with which parameter.

I imagine to write a small application which will use the sql send from the cognos dispatcher to the database and to write down these informations. In addition, i would love to get information on how many different users requested the data through cognos reports.

Its not a requierement, im just geting curious...is that even possible? Does anybody has tried sth like that? Is the Cognos Audit db (which i have never installed shamefully) able to deliver these informations?

thx in advance for your ports,
best regards
charon  :D

[sunosoft]

No need to write anything.

Cognos itself is havign that capability. Whatever comes to cogserver.log file it will be written to audit database.

You just have to configure one database under logging in cognos content manager configuration.

Download security and admin guide and refer a chapter related to loggin from it. You will get fair idea from it.

[bdbits]

Assuming you enable the logging, I do recommend using the audit extension package you can get from IBM. It includes an FM model that makes the audit db more useful without any effort, and a set of canned reports you can use as is or modify as needed. It is an excellent starting point.

[charon]

Hi guys,
ive been working with log files (even set on highes granularity), but to be honest im not sure yet if the audit will fullfil what im looking for. But il will never know until i have tried, right?
:D
Thx for your responses, im askin our support if i can get a vm with c10 to install the audit db. Lets see if im able to get an installation right ;)
thanks you guys and have a nice day,
br
charon

[Grim]

What your looking for needs to be done in 2 places. Cognos can "Audit" report runs (Who, when, what report, report run times), but the audit DB doesn't capture the SQL/Params. To do this your DBA will need to enable auditing on their side to capture what users are querying from Cognos. You could technically do this as well to a flat file with Cognos IPF tracing, but then your affecting regular logging.

[bdbits]

There is the COGIPF_PARAMETER table in the "Database View" folder that appears to contain parameters. Some other query subjects appear to have the SQL, e.g. [Native Queries].[Request string]. I have never dug into those so I do not know if they are always populated (ours has data available) for every kind of query/report, or only for some types of queries/reports perhaps dependent on logging configuration.

You will probably need to turn on logging and experiment a little.

[charon]

ty guys  :D

[Grim]

There is the COGIPF_PARAMETER table in the "Database View" folder that appears to contain parameters. Some other query subjects appear to have the SQL, e.g. [Native Queries].[Request string]. I have never dug into those so I do not know if they are always populated (ours has data available) for every kind of query/report, or only for some types of queries/reports perhaps dependent on logging configuration.

You will probably need to turn on logging and experiment a little.

Good point. To that I add...
I see the [Native Queries] in the Default Audit package, but not the Params. Not only that, but when I tried to use the [Native Queries].[Request String] object I ran into the following error:

RQP-DEF-0403
Using a blob column in this query requires that the query subject [Database View].[COGIPF_NATIVEQUERY] must have either a key or a unique index.

Going to go into the Audit FM model as per the following fix for the above error. Will poke around and see if there is anything "Parameter" wise in the model.
http://www-01.ibm.com/support/docview.wss?uid=swg21342495

Edit:
Audit FM model has the params table in a "Database View" folder which is hidden in the package. If you expose(unhide) this you should be able to use it. Gonna go play with audit some more to see. :)

[Grim]

The Native queries column is a CLOB. Had to cast it to varchar for it to show up in reports.