Re: MS SQL - How to find the most current content store?

I need a way in MS SQL tsql code to find the most current cognos content store. There can be several content stores. Only one is current and the other's are from previous versions of Cognos, basically archives.  We have many customers and their content stores are not always named consistently.

Thinking that I could query each database that has table CMOBJECTS and get the MAX(Created) date from each. The database that has the most recent date would be the current content store. 

Any thoughts or suggestions?

Still looking for a Cognos content store table perhaps that has the cognos version in it? Or as mentioned above the content store that has the most recent created row in cmobjects? any other suggestions?

Hi Brenda,

Look in the CMSYSPROPS table for the CMStoreVersion.

Hi Trevor, I am working for Georgia Tech and we wanted to check for each report when was the list time it run, in order to cleanup the production environment, we are currently running Cognos 11.2.4 and I can't find in the regular audit package a report/table that allow me to do so. I checked new extended audit package capabilities released in Cognos 12.1.0 and they have a report for this. 

Do you know how to have an execution history report created in Cognos 11.2.4, can you help are with this? 

What database do you have - SQL, Oracle, etc? Do you know how to run SQL queries? I have a MS SQL store procedure that will create a table of reports that has the last modified date which should include the date the report was changed or the last time it ran on a schedule. I really am not sure about how the different actions that can change the modified date. But ... if you are not sql savey the sql code will note help you. 

I will add, that i also think that querying objprops33 (account/user table) for max(lastlogin) date might also work. i found one store that has two cm databases both with the same cmstoreversion value. but one has a lastlogin date of several years ago and the other had current dates.  

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