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
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.
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,
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
Quote from: Andrew Weiss on 25 Mar 2015 09:53:25 AM
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"? :)
(http://www.homeprideflour.co.uk/images/products/gif_man.gif)
MF.
That's hysterical! Yes I write the code by day and bake the scones by night. Don't sleep much.
Cheers,
-Andy
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
Quote from: Nimirod on 25 Mar 2015 07:37:32 AM
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.
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')
hi Andrew
thanks for your information.
but sorry,our business team is not allowing to using any tools .
Quote from: Andrew Weiss on 25 Mar 2015 09:53:25 AM
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
Hi Sateesh,
Did you try a select distinct on the
[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