how to get list of data sources details using audit package/content store

[sateesh tata]

hi,
In cognos development environment ,we need list of cognos applications/projects which are pointing to production databases.we are asked to get an automated report based on audit package /content store.
tried by executing queries on content store but can't get a proper output.
please help me on this .
thanks in advance

[Nimirod]

Hi,
The following query is for MS sql server and returns all the reports and datasources on the content store.
Now you have to play with the CMID and PCMID to construct the hierarchie Report <-> Model  <->Datasource.

Good luck and lots of patience.

Code Select Expand

SELECT 
[CognosContentStore].[dbo].[CMOBJECTS].CMID, 
[CognosContentStore].[dbo].[CMOBJECTS].PCMID,
[CognosContentStore].[dbo].[CMOBJECTS].CREATED,
[CognosContentStore].[dbo].[CMOBJECTS].MODIFIED,
[CognosContentStore].[dbo].[CMOBJECTS].DISABLED,
[CognosContentStore].[dbo].[CMOBJECTS].CLASSID,
[CognosContentStore].[dbo].[CMOBJNAMES].ISDEFAULT,
[CognosContentStore].[dbo].[CMOBJNAMES].NAME,
[CognosContentStore].[dbo].[CMCLASSES].NAME as TypeObjet
  FROM [CognosContentStore].[dbo].[CMOBJECTS], [CognosContentStore].[dbo].[CMOBJNAMES], [CognosContentStore].[dbo].[CMCLASSES] 
  where [CognosContentStore].[dbo].[CMOBJECTS].CMID= [CognosContentStore].[dbo].[CMOBJNAMES].CMID
  and [CognosContentStore].[dbo].[CMOBJECTS].[CLASSID] = [CognosContentStore].[dbo].[CMCLASSES].CLASSID
  and [CognosContentStore].[dbo].[CMOBJNAMES].ISDEFAULT=1
  and ([CognosContentStore].[dbo].[CMCLASSES].name like 'report'
  or [CognosContentStore].[dbo].[CMCLASSES].name like 'datasource')


[Andrew Weiss]

Sateesh,

We have a mechanism in MetaManager to do this.  Using our Content Documenter modules you can document all of the reports and what packages they tie to.  You can also document all of the packages and what datasources they tie to.  If you want you can easily stitch the two together in Excel to tie reports to datasources, but it seems that may be more than you need. 

There's a writeup and a podcast showing the technique here:
  http://www.bspsoftware.com/knowledgebase/user-usage-report-of-packages-and-datasources/

And you can download MetaManager here:
  http://www.bspsoftware.com/products/metamanager/Download/

Thanks!,
-Andy

[MFGF]

Sateesh,

We have a mechanism in MetaManager to do this.  Using our Content Documenter modules you can document all of the reports and what packages they tie to.  You can also document all of the packages and what datasources they tie to.  If you want you can easily stitch the two together in Excel to tie reports to datasources, but it seems that may be more than you need. 

There's a writeup and a podcast showing the technique here:
  http://www.bspsoftware.com/knowledgebase/user-usage-report-of-packages-and-datasources/

And you can download MetaManager here:
  http://www.bspsoftware.com/products/metamanager/Download/

Thanks!,
-Andy

Andy, I love your Avatar! Did you by any chance once have a job with Homepride Bakeries under the pseudonym "Fred"? :)



MF.

[Andrew Weiss]

That's hysterical!  Yes I write the code by day and bake the scones by night.  Don't sleep much.

Cheers,
-Andy

[sateesh tata]

Hi Nimirod,
thank q very much for your help.
we are getting only  the list of data sources.but our requirement is list of data sources along with pointing database details .
is it possible to get our requirement without using any other tools like motio etc...

thanks in advacne
sateesh

Hi,
The following query is for MS sql server and returns all the reports and datasources on the content store.
Now you have to play with the CMID and PCMID to construct the hierarchie Report <-> Model  <->Datasource.

Good luck and lots of patience.

Code Select Expand

SELECT 
[CognosContentStore].[dbo].[CMOBJECTS].CMID, 
[CognosContentStore].[dbo].[CMOBJECTS].PCMID,
[CognosContentStore].[dbo].[CMOBJECTS].CREATED,
[CognosContentStore].[dbo].[CMOBJECTS].MODIFIED,
[CognosContentStore].[dbo].[CMOBJECTS].DISABLED,
[CognosContentStore].[dbo].[CMOBJECTS].CLASSID,
[CognosContentStore].[dbo].[CMOBJNAMES].ISDEFAULT,
[CognosContentStore].[dbo].[CMOBJNAMES].NAME,
[CognosContentStore].[dbo].[CMCLASSES].NAME as TypeObjet
  FROM [CognosContentStore].[dbo].[CMOBJECTS], [CognosContentStore].[dbo].[CMOBJNAMES], [CognosContentStore].[dbo].[CMCLASSES] 
  where [CognosContentStore].[dbo].[CMOBJECTS].CMID= [CognosContentStore].[dbo].[CMOBJNAMES].CMID
  and [CognosContentStore].[dbo].[CMOBJECTS].[CLASSID] = [CognosContentStore].[dbo].[CMCLASSES].CLASSID
  and [CognosContentStore].[dbo].[CMOBJNAMES].ISDEFAULT=1
  and ([CognosContentStore].[dbo].[CMCLASSES].name like 'report'
  or [CognosContentStore].[dbo].[CMCLASSES].name like 'datasource')


[sateesh tata]

hi Andrew
thanks for your information.
but sorry,our business team is not allowing to using any tools .

Sateesh,

We have a mechanism in MetaManager to do this.  Using our Content Documenter modules you can document all of the reports and what packages they tie to.  You can also document all of the packages and what datasources they tie to.  If you want you can easily stitch the two together in Excel to tie reports to datasources, but it seems that may be more than you need. 

There's a writeup and a podcast showing the technique here:
  http://www.bspsoftware.com/knowledgebase/user-usage-report-of-packages-and-datasources/

And you can download MetaManager here:
  http://www.bspsoftware.com/products/metamanager/Download/

Thanks!,
-Andy

[Nimirod]

Hi Sateesh,

Did you try a select distinct on the

Code Select Expand

[CognosContentStore].[dbo].[CMCLASSES].NAME as TypeObject
There is other types as 'datasourceConnection' or 'dataSourceSignon' that could give you more details about your data source.
But there is a Parent-Child relationship on this tables that add complexity on queries, so you'll need to compose the hierarchy using the CMID and PCMID  to get the information Report – Package – Model – Datasource.
And as I said before it could take long to do. Otherwise you can use Cognos SDK (if you are a Java programmer it would be easier) or as said by A.W. to get a tool including that feature.

I dont give you the entire query because I dont have it, but I rember doing something like that so time ago so I know you can get the information needed.

Cheers.

N