How to extract List of Users Vs Groups from Content Store (c8 . Oracle 10g )

Connect to Content Store Schema using Toad or SQL Navigator and execute below SQL

SELECT   v_group_user.user_id, v_user.ldap_id,
            LTRIM (RTRIM (UPPER (v_user.ldap_id))), v_group.cmid,
            v_group.name_en name_en, v_group.name_fr name_fr, v_group.created,
            v_group.modified, v_group.disabled, v_user.last_name,
            v_user.first_name, v_user.email
       FROM dba_group_info v_group,
            (SELECT cmid GROUP_ID, refcmid user_id
               FROM ops$cgs.cmreford1) v_group_user,
            (SELECT c33.cmid user_id, UPPER (c33.NAME) ldap_id,
                    c1.surname last_name, c1.givenname first_name,
                    c1.email email
               FROM cmobjprops1 c1, cmobjprops33 c33
              WHERE c33.cmid = c1.cmid(+)
             UNION
             SELECT cmid user_id, UPPER (name_en) ldap_id, name_en last_name,
                    '' first_name, '' email
               FROM dba_group_info) v_user
      WHERE v_group.cmid = v_group_user.GROUP_ID
            AND v_group_user.user_id = v_user.user_id(+)
   ORDER BY 1, 3

Comments

  1. UnknownFebruary 18, 2011 at 10:42 AM

    You have the same script for SQL sever (Cognos 8)

    ReplyDelete
  2. UnknownMarch 4, 2011 at 8:12 AM

    Hi Atul,

    I tried above query , however it did not work. I was not able to find the table dba_group_info.Could you please let me know

    ReplyDelete
  3. ArulsTEGMarch 24, 2011 at 12:58 PM

    Naveen,

    What exactly you are looking for ? I couldn't understand your question.

    Arul

    ReplyDelete

Post a Comment

Popular posts from this blog

How can you extract all user information from Cognos Access Manager Namespace Report Utility and create Cognos Report out of it?

The AM_NamespaceReport Utility allows you to extract the namespace to an XML file. 1. Use the AM_NamespaceReport Utility to extract all users from the namespace to an XML file. am_namespacereport -h > -p > -r > -n > -D > -w > -f all -o > e.g am_namespacereport -h cognoscamsevr -p 4004 -r o=cognos.com –n devcam -D admin -w admin -f all -o c:\temp\usrlst.xml 2. This will extract all user information to an XML file 3. Import the xml file into XLS spread sheet 4. Create ODBC connection using the ms-excel spread sheet driver 5. Create Data Source Connection and proceed creating FM Model & Publish 6. Create Report using Report Studio. 7. Save & Share the report The Access Manager Administration Guide (Appendix A) contains further information on this functionality.
Read more

RSV-VAL-0032 expression is not valid. If the item exists in a query but is not referenced in the layout, add it to a property list. CRX-API-0005 An error ocurred at or near the position '0'.

Image
Problem: RSV-VAL-0032 The following expression is not valid: [AdjRowsReturned] = 0. If the item exists in a query but is not referenced in the layout, add it to a property list. CRX-API-0005 An error ocurred at or near the position '0'. The variable named '[AdjRowsReturned]' is invalid Resolution: You might have used the calculated data item in the variable, which needs to be added in the page properties. - Click on the page and go to properties. Make sure you see 'page' on the top of the properties. - Click on properties icon under data section, you'll get pop up icon - Check mark the data item which is used for variable calc. You are good to go.Find the snapshot. .
Read more

BME-EX-0047 Unable to read preferences for the requested interface..

Issue When opening or creating a Framework Manager project the following error message is shown. Additionally, when attempting to create or open a report based on a package is made in Report Studio or Query Studio, the same error message is shown. Error BME-EX-0047 Unable to read preferences for the requested interface. Please use another repository and/or ProjectAccess Interface. Resolving the problem This happens due to a corrupt bmt.ini file in <Install Location>/c8/configuration directory. This issue can be resolved by extracting bmt.ini from last known good backup or even from installation files.
Read more