Cognos report listing folders and their permisisons

[JP0128]

I am looking to create a report (or run an existing one if such a beast exists) that will list all the folders under Team Content and the associated permissions and AD groups.

We are currently running Cognos 11.1.7

Many thanks
John

[cognostechie]

Do you have access to the Content Store DB? If yes then you can probably use a SQL to get this info.

[dougp]

cognostechie's advice leads to...

Code Select Expand

s e l e c t   ( s e l e c t   c . P O L I C I E S   a s   ' * '   f o r   x m l   p a t h ( ' ' ) )   a s   P o l i c i e s
f r o m   c m r e f n o o r d 2   r e f
  i n n e r   j o i n   c m p o l i c i e s   c   o n   c . c m i d   =   r e f . r e f c m i d
w h e r e   r e f . c m i d   =   1 2 3 4     - -   t h e   C M I D   o f   y o u r   f o l d e r

This will give you a Base64 encoded value that you'll need to decode.  Once decoded, the results appear not very tidy, but fairly easy to decode visually.  It will require more processing to present more cleanly.  But while you can get Cognos users (and groups, and roles?) and AD users (with a lookup to CMOBJPROPS33?), I don't see a way to identify the name of an AD security group that is referenced this way in Cognos.  Maybe the GUID-like value in Cognos is an actual value somewhere in AD, but I don't have that expertise.

A solution would be more likely to be supported is to use the Cognos SDK.

Motio PI may also provide this information for you, although I don't have much experience with that product.

MetaManager may also be able to produce this.

[MFGF]

cognostechie's advice leads to...

Code Select Expand

s e l e c t   ( s e l e c t   c . P O L I C I E S   a s   ' * '   f o r   x m l   p a t h ( ' ' ) )   a s   P o l i c i e s
f r o m   c m r e f n o o r d 2   r e f
  i n n e r   j o i n   c m p o l i c i e s   c   o n   c . c m i d   =   r e f . r e f c m i d
w h e r e   r e f . c m i d   =   1 2 3 4     - -   t h e   C M I D   o f   y o u r   f o l d e r

This will give you a Base64 encoded value that you'll need to decode.  Once decoded, the results appear not very tidy, but fairly easy to decode visually.  It will require more processing to present more cleanly.  But while you can get Cognos users (and groups, and roles?) and AD users (with a lookup to CMOBJPROPS33?), I don't see a way to identify the name of an AD security group that is referenced this way in Cognos.  Maybe the GUID-like value in Cognos is an actual value somewhere in AD, but I don't have that expertise.

A solution would be more likely to be supported is to use the Cognos SDK.

Motio PI may also provide this information for you, although I don't have much experience with that product.

MetaManager may also be able to produce this.

I reached out to the site admins and MetaManager is indeed a good solution for this. Hopefully one of the MetaManager gurus will be along to offer some advice shortly.

Cheers!

MF.