How many Queries running in total

[bi4business]

Hi All,

I would like to build a dashboard/report what shows all Queries on the BI Suite.
Searched @google, but no real good hit untill now.

Is there a audit table available for this information?
With the COGIPF_EDITQUERY I can't get the right info.
Looks like only the 'old' Studios are retrieved by this Audit table.
But Report Module (report/queries) is not adding records in this file.

I have set the log-level @AdminConsole to trace for Query info.
We are using C11.0.7.

We would like to build a Dashboard what shows per day how many Queries are on the BI Suite.
Does anyone know how we can retrieve this information ?

We don't want this file based (log file), but would like this inside the Audit Database.

greetz

BI4BUSINESS

[aussieadam]

have you looked into the COGIPF_RUNREPORT table?

[bi4business]

Hi Aussieadam,

COGIPF_RUNREPORT only give you info about the report(s) you have used, but not the queries used by the reports, do you agree ?

https://www.ibm.com/support/knowledgecenter/SSEP7J_10.1.1/com.ibm.swg.ba.cognos.ug_cra.10.1.1.doc/c_asg_table_definitions.html#asg_table_definitions

I am looking for the table / info where the queries are stored.

Looks like the COGIPF_EDITQUERY is only used by the 'old'  Query Studio ?
Perhaps the COGIPF_NATIVEQUERY is the one I am looking for (https://www.ibm.com/support/knowledgecenter/SSEP7J_10.1.1/com.ibm.swg.ba.cognos.ug_cra.10.1.1.doc/c_asg_cogipf_nativequery_asg.html#asg_COGIPF_NATIVEQUERY_ASG)

But it is not filled at our environment..... ?


BI4BUSINESS

[dougp]

What do you mean by

trace for Query info

If you are thinking a Query (Workspace Advanced, Report Studio, Reporting) runs, you've fallen prey to a common misconception.

The query that Cognos sends to the database server is defined by the combination of the design of the visible object (list, crosstab, chart, etc.) and the design of the Query that it uses.  Cognos minimizes what it sends to the database server, so there is not a direct relationship between that and the Query.

Enabling Audit the native query for report service will enable you to see the queries that are actually sent to the database server.  IBM recommends doing this only for short times as needed for troubleshooting as this will take a lot of space in your Audit database.  What you will see is the raw Native SQL code that Cognos sends.  It's pretty ugly.  Good luck parsing it in a way that will be usefully displayed on a dashboard.

[bi4business]

Hi Dougp,

Thank you for your reply.

"trace for Query info" -> I am looking for some Query information used by Cognos inside some Audit tables. I know there is a way to find this at OS level (log files after changing the xqe.diagnosticlogging.xml by adding the level option-> eventGroup name="JDBC" level="info"), but I hope there is some info available at the Audit tables.

I have enabled the Audit the native query for report service, but I can't find any SQL in the tables.
Did a full reboot of the BI Suite, but still no logging, only when I use an old Query Studio product.

What am I doing wrong.

BI4BUSINESS