Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only
Back to discussions
Expand all | Collapse all

Adding ReportName, Path, User to the SQL Query

  • 1.  Adding ReportName, Path, User to the SQL Query

    Posted Wed December 11, 2019 05:16 PM
    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


  • 2.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Thu December 12, 2019 07:37 AM

    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


  • 3.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Thu December 12, 2019 11:17 AM
    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


  • 4.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Fri December 13, 2019 10:28 AM
    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


  • 5.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Mon April 24, 2023 10:08 AM

    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


  • 6.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Tue January 07, 2025 09:26 AM

    I know that this is an old thread, but trying to get it to work. Followed the steps by @chadman hult and restarted Cognos service, but still the comments are not in the SQL passed to MS SQL server. Anyone able to get reportname, path and user added as comments to the generated sql? Also, should i be able to see these comments in the query -> properties -> generate sql within Cognos? 



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

    Original Message


  • 7.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Tue January 07, 2025 09:40 AM

    Hi, 

    If you are using DQM Packages, you must configure ../configuration/xqe.config.xml

    There is a missing line in this file that you should add to work. 

    After line

    <includeMacroExpansionInXqeLogs enabled="false"/>

    Add

    <generateCommentsInNativeSQL enabled="true"/>


    And uncomment tags <NativeCommentMacroAlternative> and <NativeCommentMacro>



    ------------------------------
    JEAM COELHO
    Cognos Solution Architect

    LinkedIn: https://www.linkedin.com/in/jeamcoelho/
    ------------------------------

    Original Message


  • 8.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Tue January 07, 2025 09:55 AM

    Another(better) place to control the generation of comments in Native SQL is from the Admin pages



    ------------------------------
    Kind regards,
    Henk Cazemier
    ------------------------------

    Original Message


  • 9.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Tue January 07, 2025 10:30 AM

    @HENK CAZEMIER - (1) how do you control what comments are added? (2)  does this work for cqm and dqm (3) do i still have to uncomment and edit the CQEconfig.xml.sample and /configuration/xqe.config.xml files?



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

    Original Message


  • 10.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Tue January 07, 2025 03:57 PM

    Hi Brenda,
    Yes, I should have been more specific.
    Enabling/disabling for DQM can be done via the portal.
    DQM content of the message is controlled by the definitions in the xqe.config files.

    For CQM the control is in the CQEConfig.xml file (you need to rename it from .sample) there are a couple of lines that need to be uncommented.




    ------------------------------
    Kind regards,
    Henk Cazemier
    ------------------------------

    Original Message


  • 11.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Thu January 09, 2025 11:50 AM

    Additionally, I'm using this value for .xml config files, instead of default.

    This will tell you the type of object, path, model path, execution timestamp, query name, server, etc.

     value="#'[objectType=' + substitute( '''' ; '' ;substitute( '''' ; '' ; csv(substitute('agentState' ; 'EventStudio' ; grep('agentState' ; split('[' ; split('/' ; $reportPath))))) + csv(substitute('exploration' ; 'Dashboard/Exploration' ; grep('exploration' ; split('[' ; split('/' ; $reportPath))))) + csv(substitute('dataSet' ; 'DataSet' ; grep('dataSet' ; split('[' ; split('/' ; $reportPath))))) + csv(substitute('query' ; 'QueryStudio' ; grep('query' ; split('[' ; split('/' ; $reportPath))))) + csv(substitute('report' ; 'Report' ; grep('report' ; split('[' ; split('/' ; $reportPath))))) + csv(substitute('storeID' ; 'Report/QS' ; grep('storeID' ; split('(' ; $reportPath)))) +  csv(substitute( 'new' ; 'Unsaved/Dashboard/Exploration' ; grep('new$' ;  array('new' + $reportPath)))))) + '] [userLogin=' + $account.personalInfo.userName + '] [userName=' + $account.defaultName + '] [startTime=' + timestampMask ( $current_timestamp ; 'yyyy-mm-ddThh:mm:ss' ) + '] [serverName=' + $machine +  '] [queryMode=DQM] [objectName=' + $report + '] [objectPath=' + $reportPath + '] [queryName=' + $queryName + '] [modelPath=' + $modelPath + '] [requestID=' + $requestID + ']'#"

    I'm using queryMode=DQM for xqe.config.xml and queryMode=CQM for CQEConfig.xml, just to know what type of package that object is using



    ------------------------------
    JEAM COELHO
    Cognos Solution Architect

    LinkedIn: https://www.linkedin.com/in/jeamcoelho/
    ------------------------------

    Original Message


  • 12.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Tue January 14, 2025 12:08 PM

    Here is what I did for 11.2.4 FP2 to enable CQM and DQM SQL Capture.  I have noticed different Cognos versions seem to have/take different syntax??

    You will see results in Framework Manager, Report Studio, Database.  In some instances you may not see all the info.

    I also see some other great ideas for additional comments on this thread.

    Enable SQL Comments in CQEConfig.xml

    install\configuration\CQEConfig.xml

    <!-- 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="#'SERVER_NAME=' + $SERVER_NAME + ' user=' + $account.defaultName + ' user_id=' + $account.personalInfo.userName + ' reportPath=' + $reportPath + ' queryName=' + $queryName + ' requestID=' + $requestID#"/>

                                                    <entry name="CognosCommentMacro" value="#'SERVER_NAME=' + $SERVER_NAME + ' user=' + $account.defaultName + ' user_id=' + $account.personalInfo.userName + ' reportPath=' + $reportPath + ' queryName=' + $queryName + ' requestID=' + $requestID#"/>

    Enable SQL Comments in xqeconfig.config.xml

    Add line to enable Native SQL Comments.

    This line is not in the original xqe.config.xml.

    <generateCommentsInNativeSQL enabled="true"/>

                               

    Enable Native Comment Macro under the NativeCommentMacroAlternative line.

                                    <NativeCommentMacro value="#'SERVER_NAME=' + $SERVER_NAME + ' user=' + $account.defaultName + ' user_id=' + $account.personalInfo.userName + ' report=' + $report + ' reportPath=' + $reportPath + ' queryName=' + $queryName + ' requestID=' + $requestID#"/>

    Restart Dispatcher(s)

    Note that enabling "Generate comments in native SQL" in the admin portal is not required. If you do, keep an eye on the size of your Cognos Audit Database



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

    Original Message


  • 13.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Thu January 09, 2025 09:55 AM

    Hi Amy Rivito, I made the changes but cannot find the reportname, report path and user id in the Generated SQL. Does it need some more changes? Please share if you have any thoughts on this



    ------------------------------
    Ram Posina
    ------------------------------

    Original Message


  • 14.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Thu January 09, 2025 10:12 AM

    Hi Ram,

    The file and code Jeam mentioned above should work for DQM.  My comment was directed at CQM reports. 



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

    Original Message


  • 15.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Fri January 10, 2025 05:47 AM

    Do you have any working solutions for same in CQM?



    ------------------------------
    Ram Posina
    ------------------------------

    Original Message


  • 16.  RE: Adding ReportName, Path, User to the SQL Query

    Posted Mon December 23, 2019 05:37 PM
    Dyamic SQL works fine with DQM and CQM on Cognos 11.x too.

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

    Original Message


Related Content