Set SQL which contains user ID to be executed in every query for selected source

[cognosworkx]

Hi all,

maybe this has been solved already but I was unable to find something here. This is the case:

We run a small core BI team which supports Cognos Analytics 11 on enterprise level. Business users can create reports and some power users are enabled to use framework manager and publish packages (but all running on the same data sourec). Sometimes the DBAs send us database reports which list the "top most resource consuming SQLs of the day" and Cognos SQLs are sometimes listed on top there. Sometimes we can identify the corresponding report and contact the responsible author to help fixing the queries or adjust frameworks to improve performance, but most of the time we do not know who the author actually is!

So this is the idea: we would like to include a simple SQL snippet into each and every query which is created by Cognos using our datasource connection, something like "[WHERE] ... AND (1=1 OR 'user'=<USER_ID>)". Thie would allow us to identify and contact the user if her or his SQL appears in the dba's list. Is there a way to do this?

Thanks a lot and have some nice christmas days!

[MFGF]

Hi all,

maybe this has been solved already but I was unable to find something here. This is the case:

We run a small core BI team which supports Cognos Analytics 11 on enterprise level. Business users can create reports and some power users are enabled to use framework manager and publish packages (but all running on the same data sourec). Sometimes the DBAs send us database reports which list the "top most resource consuming SQLs of the day" and Cognos SQLs are sometimes listed on top there. Sometimes we can identify the corresponding report and contact the responsible author to help fixing the queries or adjust frameworks to improve performance, but most of the time we do not know who the author actually is!

So this is the idea: we would like to include a simple SQL snippet into each and every query which is created by Cognos using our datasource connection, something like "[WHERE] ... AND (1=1 OR 'user'=<USER_ID>)". Thie would allow us to identify and contact the user if her or his SQL appears in the dba's list. Is there a way to do this?

Thanks a lot and have some nice christmas days!

Hi,

Off the top of my head, you could probably do this for the business users using the security filters mechanism in your Framework Manager model. You would set up a single filter for the Everyone group that uses the expression you posted (the user name is available as a session parameter - $account.defaultName for the user's name or $account.personalInfo.userName for the user's ID) and apply this to the highest level namespace.

If power users are creating their own FM models the issue would be in enforcing the use of this in their custom models. I suspect this would need to be a standard you would need to introduce, with some repercussions for those who ignore it.

Good luck!

MF.

[Andrei I]

Modify Configuration files to add application context to Cognos SQL statements

See the documentation:
Using application context in Dynamic SQL
https://www.ibm.com/support/knowledgecenter/en/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_cra.doc/c_asg_appcntxt_dynSQL.html

Database server administrators can log and analyze the dynamic SQL workload generated by IBM Cognos software.

As an IBM® Cognos® administrator, you can define a custom string that includes application context that is added as a comment marker within SQL generated by the application. You can use literals, macros, and session variables, such as a user name, server name, qualified report path, and so on, to customize the comment generated by Cognos software.

The Database administrator should check to see if their database client strips comments from statements prior to sending to the server. This option is probably configurable, check with your database client provider.

By using the applicable session variables, you can configure the format of the string for specific tools and products that can extract comments from dynamic SQL. IBM Cognos software includes the comments within any dynamic SQL it generates to a Relational Database Management System (RDBMS) if the vendor supports this functionality.

Use the CQEConfig.xml.sample file included with the product to customize the string specifications. The macro in this file shows the default entries that IBM Cognos software uses for generating the comments. However, you can add other entries as well.

The following example shows kinds of session variables you can specify in the macro in the CQEConfig.xml.sample file:

<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
#"/>
      </section>
   </component>
</configuration>Copy
At run time, the macro used in the previous example would add the following comment to the automatically-generated SQL, or native SQL:

/* CC user=Anonymous report=REPORT1
start=2008-08-28T01:59:35.403Z modelPath=/content/package
[@name='New Package']/model[@name='model']
reportPath=/content/package[@name='New Package']/report[@name='REPORT1']
queryName=Query1 REMOTE_ADDR=127.0.0.1 HTTP_HOST=localhost
SERVER_NAME=localhost
requestID=wq2lshM9jGhqdMj9h92MqlqvdMlhyMlGq9lyG9sq
sessionID=010:0d159165-745a-11dd-ac9f-b741aeca4631:2789499633
*/
select distinct
       ALL_TIME.CALENDAR_WEEKDAY  as  CALENDAR_WEEKDAY
from
       EAPPS..EAPPS.ALL_TIME ALL_TIMECopy
Not all information in the generated comment is meaningful in all situations. The request and session ID information provides a link to the auditing facility, perfQFS performance information, and other traces in IBM Cognos. However, the name of a query in a report and the report itself may be meaningless, for example, when a user is performing an ad-hoc query or analysis as opposed to running a saved query, analysis or report.

By default, an anonymous user cannot see all session variables in the generated comments.

Adding application context for Dynamic Query Mode
To use comments in SQL for dynamic query mode you can configure the xqe.config.xml file, located in install_location/configuration.

You edit the following elements in the <queryPlanning> element.

<generateCommentsInNativeSQL enabled="true"/>
<NativeCommentMacro value="#'user=' + $account.defaultName + ' reportPath='
+ $reportPath +' queryName=' + $queryName + ' REMOTE_ADDR=' + $REMOTE_ADDR
+ ' SERVER_NAME=' + $SERVER_NAME + ' requestID=' + $requestID#"/>Copy
Add Application Context to Dynamic SQL
Database server administrators can configure the CQEConfig.xml.sample file to log and analyze the dynamic SQL workload generated by IBM Cognos software. For Dynamic Query Mode, administrators configure the xqe.config.xml file.

[dougp]

Bad Request

Your browser sent a request that this server could not understand.
Size of a request header field exceeds server limit.
IBM_HTTP_Server at www-01.ibm.com Port 443

[Andrei I]

Added full doc text

[dougp]

cognosworkx:
I have been doing the same thing.  I found it will work for CQM, but not DQM.

MFGF's suggestion may work globally.  My solution has been to add a filter to every query subject:

Code Select Expand

(1=1 or [Namespace].[QuerySubject].[QueryItem]  = #sq($account.personalInfo.userName)#)
...where QueryItem is a string data type, or ...

Code Select Expand

#sq($account.personalInfo.userName)# = #sq($account.personalInfo.userName)#


CQM Native SQL:

Code Select Expand

WHERE 'cognosworkx' = 'cognosworkx'
or

Code Select Expand

WHERE (1=1 OR 'User' = 'cognosworkx')

DQM IBM Cognos SQL:

Code Select Expand

WHERE 'cognosworkx' = 'cognosworkx'
or

Code Select Expand


WHERE (1=1 OR 'User' = 'cognosworkx')

But DQM detects that the result will always be true and doesn't send it to the database server:
DQM Native SQL:
<nothing>



I see IBM got their web site working again.  The link posted by Andrei I is now working.  I'm going to try that.

[dougp]

I updated xqe.config.xml and restarted the Cognos service.  No love.


Andrei I

The Database administrator should check to see if their database client strips comments from statements prior to sending to the server. This option is probably configurable, check with your database client provider.

How would I know if the "client" strips comments from statements?  (Is Cognos not the client?)
Is the "database client provider" the ODBC driver?  How would this be configurable?

[Andrei I]

I updated xqe.config.xml and restarted the Cognos service.  No love.
Andrei I
The Database administrator should check to see if their database client strips comments from statements prior to sending to the server. This option is probably configurable, check with your database client provider.

How would I know if the "client" strips comments from statements?  (Is Cognos not the client?)
Is the "database client provider" the ODBC driver?  How would this be configurable?

Doug,
This was an official IBM Cognos Doc.
It did work for me. Though at that time I was doing it for CQM packages only.
Please check your specific DB Driver configuration to see if it strips the comments from SQL generated by Cognos.
If you enable Native Query logging and those comments then you should see the SQL with comments in the Cognos log file.
HTH,
Andrei

[cognosworkx]

Hi all,

Thank you all for the great thinking!
I already read something about using command blocks to do this, but no real explanation about how to actually implement it...
Let me see if we can try the Andreis idea using IBMs solution, I'll keep you posted on progress.

See you in 2020!