How to find SQL based report not using FM

[Cognos8]

To create a report there is 2 ways, one is by pulling query item from model and another one is simply adding SQL to create a report.
is there a way to find what are all the reported executed using only SQL  using audit reports?

[PolzovatelCognos]

We also have our Report Authors creating reports via the two following methods:
1.) (The Preferred and More Widely Used Method) Create reports utilizing the designed FM models
2.) Create reports by utilizing User-Defined SQL straight against a chosen data source

I believe you can actually find all of the reports that utilize the 2nd technique by querying the Cognos Content Store: CMOBJPROPS7 table.

This table stores the XML of all of the reports & models. In particular, see the SPEC column in this table. 

Take a look at some of the other tables stored in the Cognos Content Store. The following document describes what each table captures:
http://www.cogknowhow.com/index.php/library/48-cognos-content-store-database-tables-description-

[PolzovatelCognos]

One more thing to note:
In the SPEC column, please note the following XML tags to help you find what you need:

<queries> <query name="User_Defined_Query_Name"> <source> <sqlQuery name="SQL2" dataSource="Data Source X">
<sqlText> Here you will actually see the full SQL select statement, which may be quite large </sqlText>

.....
</query></queries></report>

You may need to utilize Regular Expressions, however, to dynamically parse the records. Hope this helps.

[meeecog]

Hello - We are trying to get all the reports which use only Custom SQL / Transact SQL from our production system . Is there any other way without touching content store database like free tools like Motio etc .. Please share more information around this.

Thanks