Cognos Analytics

I want to add ReportName, Path, User to the SQL Queries that are sent to MS SQL. Currently all users access SQL using the same generic SQL login. So when there are really crappy reports, we don't know anything about the report except for the MS SQLl.

The 11.0 Administration and Security Guide, on page 132 talks about "Using application context in Dynamic SQL". It seems that this might work. But it says "Dynamic SQL", does this include saved reports? Has anyone done this, any gotchas?

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

#CognosAnalyticswithWatson

Both CQM and DQM can generate a comment for queries generated as reports etc execute.

This feature existed in the product circa 8.4.

In most cases, 3rd party client drivers/servers support inline comments.

A few do not. Hence, any logged Cognos SQL statements would include the comment but the vendor specific SQL would not. 

See also chapter 6

http://www.redbooks.ibm.com/redbooks/pdfs/sg248121.pdf

------------------------------
NIGEL CAMPBELL
------------------------------

Original Message:
Sent: Wed December 11, 2019 05:15 PM
From: brenda grossnickle
Subject: Adding ReportName, Path, User to the SQL Query

I want to add ReportName, Path, User to the SQL Queries that are sent to MS SQL. Currently all users access SQL using the same generic SQL login. So when there are really crappy reports, we don't know anything about the report except for the MS SQLl.

The 11.0 Administration and Security Guide, on page 132 talks about "Using application context in Dynamic SQL". It seems that this might work. But it says "Dynamic SQL", does this include saved reports? Has anyone done this, any gotchas?

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

#CognosAnalyticswithWatson

Hello @brenda grossnickle -

Are  you trying to put something in the Framework so that every report has this information in the SQL?
I did that for report name using #sq($report)# as the definition of the field.
You can use other parameters the same way using the documented names in the Chapter 4 of the document that @Nigel​ mentioned.
Good luck.

------------------------------
Regards,
Opher
------------------------------

Original Message:
Sent: Wed December 11, 2019 05:15 PM
From: brenda grossnickle
Subject: Adding ReportName, Path, User to the SQL Query

I want to add ReportName, Path, User to the SQL Queries that are sent to MS SQL. Currently all users access SQL using the same generic SQL login. So when there are really crappy reports, we don't know anything about the report except for the MS SQLl.

The 11.0 Administration and Security Guide, on page 132 talks about "Using application context in Dynamic SQL". It seems that this might work. But it says "Dynamic SQL", does this include saved reports? Has anyone done this, any gotchas?

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

#CognosAnalyticswithWatson

I think you can modify the ..\configuration\CQEConfig.xml file to include this in the QueryEngine section.

<!-- Generation of comments in native sql and cognos sql.-->
   <!-- entry name="GenerateCommentInNativeSQL" value="1"-->
   <!-- ( default(off)=0, on=1) -->
   <!-- entry name="GenerateCommentInCognosSQL" value="1"-->
   <!-- ( default(off)=0, on=1) -->
   <!-- The content of the comments is controlled with two entries, their defaults are specified in the value attribute -->
   <!-- entry name="NativeCommentMacro" value="#'user=' + $account.defaultName + ' reportPath=' + $reportPath + ' queryName=' + $queryName + ' REMOTE_ADDR=' + $REMOTE_ADDR + ' SERVER_NAME=' + $SERVER_NAME + ' requestID=' + $requestID#"/-->
   <!-- entry name="CognosCommentMacro" value="#'user=' + $account.defaultName + ' reportPath=' + $reportPath + ' queryName=' + $queryName + ' REMOTE_ADDR=' + $REMOTE_ADDR + ' SERVER_NAME=' + $SERVER_NAME + ' requestID=' + $requestID#"/-->
   
    <entry name="GenerateCommentInNativeSQL" value="1"/>

We use a third party tool, Attunity, to capture this info for additional reporting.  FYI most of our sources are Oracle.
 ​

------------------------------
Amy Rivito
------------------------------

Original Message:
Sent: Wed December 11, 2019 05:15 PM
From: brenda grossnickle
Subject: Adding ReportName, Path, User to the SQL Query

I want to add ReportName, Path, User to the SQL Queries that are sent to MS SQL. Currently all users access SQL using the same generic SQL login. So when there are really crappy reports, we don't know anything about the report except for the MS SQLl.

The 11.0 Administration and Security Guide, on page 132 talks about "Using application context in Dynamic SQL". It seems that this might work. But it says "Dynamic SQL", does this include saved reports? Has anyone done this, any gotchas?

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

#CognosAnalyticswithWatson

Yes you can enable Native SQL and Cognos SQL Comments by using the CQEconfig.xml.sample.
First rename to CQEconfig.xml.
Be careful as when enabling this file you inadvertently enable options as they are not commented out by default.

<component name="CQE">

                                <section name="DBConnectionPool">

                                                <!-- Description: Database connection timeout. Default is 900 seconds (15 minutes) -->

                                                <entry name="Timeout" value="900"/>

                                                <!-- -->

                                                <!-- Description: Database connection pool size. -->

                                                <!-- Maximum number of connections managed by the report server process. Default=40 -->

                                                <entry name="PoolSize" value="40"/>

                                                <!-- -->

                                </section>

                                <section name="QueryEngine">
                                                <!-- Description: queryReuse feature -->

                                                <!-- value="0" means disable the feature -->

                                                <!-- default is value="5" which means cache up to 5 result sets per session -->

                                                <entry name="queryReuse" value="5"/>

<!-- to the beginning and  --> ti the end

Change to comment out/disable

<component name="CQE">

                                <section name="DBConnectionPool">

                                                <!-- Description: Database connection timeout. Default is 900 seconds (15 minutes) -->

                                <!-- <entry name="Timeout" value="900"/> -->

                                                <!-- -->

                                                <!-- Description: Database connection pool size. -->

                                                <!-- Maximum number of connections managed by the report server process. Default=40 -->

                                                <!-- <entry name="PoolSize" value="40"/> -->

                                                <!-- -->

                                </section>

                                <section name="QueryEngine">

                                                <!-- Description: queryReuse feature -->

                                                <!-- value="0" means disable the feature -->

                                                <!-- default is value="5" which means cache up to 5 result sets per session -->

                                <!-- <entry name="queryReuse" value="5"/> -->

 

CQE Config modifications

<configuration company="Cognos" version="0.1" rendition="cer2">

                <component name="CQE">

                                <section name="QueryEngine">

                                                <entry name="GenerateCommentInNativeSQL" value="1"/>

                                                <!-- ( default(off)=0, on=1) -->

                                                <entry name="GenerateCommentInCognosSQL" value="1"/>

                                                <!-- ( default(off)=0, on=1) -->

                                                <!-- The content of the comments is controlled with two entries, their

defaults are specified in the value attribute -->

                                                <entry name="NativeCommentMacro" value="# 'NC user=' + $account.defaultName + 'report=' + $report + 'start=' + $startTime + 'modelPath='  + $modelPath + 'reportPath=' + $reportPath + ' queryName=' + $queryName + ' REMOTE_ADDR=' + $REMOTE_ADDR + 'HTTP_HOST=' + $HTTP_HOST + 'SERVER_NAME=' + $SERVER_NAME +' requestID=' + $requestID + 'sessionID=' + $sessionID #"/>

                                                <entry name="CognosCommentMacro" value="# 'CC user=' + $account.defaultName + 'report=' + $report + 'start=' + $startTime + 'modelPath='  + $modelPath + 'reportPath=' + $reportPath + ' queryName=' + $queryName + ' REMOTE_ADDR=' + $REMOTE_ADDR + 'HTTP_HOST=' + $HTTP_HOST + 'SERVER_NAME=' + $SERVER_NAME +' requestID=' + $requestID + 'sessionID=' + $sessionID #"/>

------------------------------
Chadman Hult
------------------------------

Original Message:
Sent: Fri December 13, 2019 10:27 AM
From: Amy Rivito
Subject: Adding ReportName, Path, User to the SQL Query

I think you can modify the ..\configuration\CQEConfig.xml file to include this in the QueryEngine section.

<!-- Generation of comments in native sql and cognos sql.-->
   <!-- entry name="GenerateCommentInNativeSQL" value="1"-->
   <!-- ( default(off)=0, on=1) -->
   <!-- entry name="GenerateCommentInCognosSQL" value="1"-->
   <!-- ( default(off)=0, on=1) -->
   <!-- The content of the comments is controlled with two entries, their defaults are specified in the value attribute -->
   <!-- entry name="NativeCommentMacro" value="#'user=' + $account.defaultName + ' reportPath=' + $reportPath + ' queryName=' + $queryName + ' REMOTE_ADDR=' + $REMOTE_ADDR + ' SERVER_NAME=' + $SERVER_NAME + ' requestID=' + $requestID#"/-->
   <!-- entry name="CognosCommentMacro" value="#'user=' + $account.defaultName + ' reportPath=' + $reportPath + ' queryName=' + $queryName + ' REMOTE_ADDR=' + $REMOTE_ADDR + ' SERVER_NAME=' + $SERVER_NAME + ' requestID=' + $requestID#"/-->
   
    <entry name="GenerateCommentInNativeSQL" value="1"/>

We use a third party tool, Attunity, to capture this info for additional reporting.  FYI most of our sources are Oracle.
 ​

------------------------------
Amy Rivito

Original Message:
Sent: Wed December 11, 2019 05:15 PM
From: brenda grossnickle
Subject: Adding ReportName, Path, User to the SQL Query

I want to add ReportName, Path, User to the SQL Queries that are sent to MS SQL. Currently all users access SQL using the same generic SQL login. So when there are really crappy reports, we don't know anything about the report except for the MS SQLl.

The 11.0 Administration and Security Guide, on page 132 talks about "Using application context in Dynamic SQL". It seems that this might work. But it says "Dynamic SQL", does this include saved reports? Has anyone done this, any gotchas?

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

#CognosAnalyticswithWatson

Dyamic SQL works fine with DQM and CQM on Cognos 11.x too.

------------------------------
JEAM COELHO
------------------------------

Original Message:
Sent: Wed December 11, 2019 05:15 PM
From: brenda grossnickle
Subject: Adding ReportName, Path, User to the SQL Query

I want to add ReportName, Path, User to the SQL Queries that are sent to MS SQL. Currently all users access SQL using the same generic SQL login. So when there are really crappy reports, we don't know anything about the report except for the MS SQLl.

The 11.0 Administration and Security Guide, on page 132 talks about "Using application context in Dynamic SQL". It seems that this might work. But it says "Dynamic SQL", does this include saved reports? Has anyone done this, any gotchas?

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS
------------------------------

#CognosAnalyticswithWatson