0 Replies - 2050 Views - Last Post: 12 November 2018 - 04:15 PM

#1 alapee   User is offline

  • Stressed Out Programmer

Reputation: 239
  • View blog
  • Posts: 2,536
  • Joined: 24-October 13

Cognos 11 Oracle Get Users and Groups

Posted 12 November 2018 - 04:15 PM

Finding Cognos Users and Groups can be a pain, the below coding should get you the user name and the groups associated with that user name.



--Cognos 11 Oracle Groups and Users
SELECT
    USER_GROUP.USER_NAME,
    LISTAGG(TRIM(USER_GROUP.GROUP_NAME), ', ') WITHIN GROUP (ORDER BY USER_GROUP.GROUP_NAME) "USER_GROUPS"
FROM
(
    SELECT
        CMOBJPROPS33.USER_NAME,
        CMOBJNAMES.GROUP_NAME   
    FROM
    (
        SELECT
            CMID,
            REFCMID
        FROM
            COGNOS.cmreford1 
    )CMREFORD1
    INNER JOIN
    (
        SELECT
            CMID
        FROM
            COGNOS.cmobjects
        WHERE
            classid in (26, 54)
    )CMOBJECTS ON CMOBJECTS.CMID = CMREFORD1.CMID
    INNER JOIN
    (
        SELECT
            CMID,
            MAPDLOCALEID,
            NAME GROUP_NAME
        FROM
            COGNOS.cmobjnames
    )CMOBJNAMES ON CMOBJNAMES.CMID = CMREFORD1.CMID
    INNER JOIN
    (
        SELECT
           MAPDLOCALEID 
        FROM
            COGNOS.cmlocales
        WHERE
            locale = 'en'
    )CMLOCALES ON CMLOCALES.MAPDLOCALEID = CMOBJNAMES.MAPDLOCALEID
    INNER JOIN
    (
        SELECT
            CMID,
            NAME USER_NAME
        FROM
            COGNOS.cmobjprops33
    )CMOBJPROPS33 ON CMOBJPROPS33.CMID = CMREFORD1.REFCMID
    GROUP BY
         CMOBJPROPS33.USER_NAME,
         CMOBJNAMES.GROUP_NAME
    ORDER BY
        CMOBJPROPS33.USER_NAME,
        CMOBJNAMES.GROUP_NAME
)USER_GROUP
GROUP BY
    USER_NAME



Is This A Good Question/Topic? 0
  • +

Page 1 of 1