Oracle Query on Content Store database for Capabilities?

[kirkboy]

My workplace is not allowing me to use BSP's free License Auditing tool, so I am looking for a database query I can use to pull capabilities information.  Currently I have a query (pulled from the internet somewhere, sorry to whoever deserves credit for this) that pulls USER->ROLE assignments:

Code Select Expand

-- List of all users and their groups
SELECT v_group.name group_name,
v_user.ldap_id
FROM cognoscon.cmreford1 v_group_user
JOIN-- GET USERS
(SELECT cmid user_id, UPPER(NAME) ldap_id
FROM cognoscon.cmobjprops33) v_user
ON v_group_user.refcmid = v_user.user_id
JOIN--GET GROUPS
(SELECT cmobjnames.cmid cmid,UPPER(cmobjnames.NAME) name
FROM cognoscon.cmobjects,cognoscon.cmobjnames
WHERE cmobjects.classid IN (26, 54)
AND cmobjnames.mapdlocaleid = 24
AND cmobjects.cmid = cmobjnames.cmid) v_group
ON v_group.cmid = v_group_user.cmid
--where v_group.name <> 'SYSTEM ADMINISTRATORS'
--where v_user.ldap_id like 'RO%'
order by 1,2

I modified the query to allow me to categorize my roles by license, but this depends on me having already setup the roles with the proper capabilities, and including a definition of that in the Role Name. (ie. Author license role contains Author in role name, Consumer License role contains 'Consumer' in role name, etc)

Code Select Expand

-- List of all user counts categorized by license
SELECT 'ADMIN' license_type, count(*) license_cnt  -- Select all ADMIN
FROM cognoscon.cmreford1 v_group_user
JOIN-- GET USERS
(SELECT cmid user_id, UPPER(NAME) ldap_id
FROM cognoscon.cmobjprops33) v_user
ON v_group_user.refcmid = v_user.user_id
JOIN--GET GROUPS
(SELECT cmobjnames.cmid cmid,UPPER(cmobjnames.NAME) name
FROM cognoscon.cmobjects,cognoscon.cmobjnames
WHERE cmobjects.classid IN (26, 54)
AND cmobjnames.mapdlocaleid = 24
AND cmobjects.cmid = cmobjnames.cmid) v_group
ON v_group.cmid = v_group_user.cmid
where v_group.name like '%ADMIN%'
UNION ALL
SELECT 'AUTHOR' license_type, count(*) license_cnt   -- Select all Authors
FROM cognoscon.cmreford1 v_group_user
JOIN-- GET USERS
(SELECT cmid user_id, UPPER(NAME) ldap_id
FROM cognoscon.cmobjprops33) v_user
ON v_group_user.refcmid = v_user.user_id
JOIN--GET GROUPS
(SELECT cmobjnames.cmid cmid,UPPER(cmobjnames.NAME) name
FROM cognoscon.cmobjects,cognoscon.cmobjnames
WHERE cmobjects.classid IN (26, 54)
AND cmobjnames.mapdlocaleid = 24
AND cmobjects.cmid = cmobjnames.cmid) v_group
ON v_group.cmid = v_group_user.cmid
where v_group.name like '%AUTHOR%'
UNION ALL
SELECT 'CONSUMER' license_type, count(*) license_cnt  -- select all CONSUMERS
FROM cognoscon.cmreford1 v_group_user
JOIN-- GET USERS
(SELECT cmid user_id, UPPER(NAME) ldap_id
FROM cognoscon.cmobjprops33) v_user
ON v_group_user.refcmid = v_user.user_id
JOIN--GET GROUPS
(SELECT cmobjnames.cmid cmid,UPPER(cmobjnames.NAME) name
FROM cognoscon.cmobjects,cognoscon.cmobjnames
WHERE cmobjects.classid IN (26, 54)
AND cmobjnames.mapdlocaleid = 24
AND cmobjects.cmid = cmobjnames.cmid) v_group
ON v_group.cmid = v_group_user.cmid
where v_group.name like '%CONSUMER%'
or v_group.name = 'DISTRICT TEAM LEADER'
UNION ALL
select 'DUPLICATES' license_type, sum(roles-1) license_cnt  -- List of all users who are duplicated into multiple groups
from (
    SELECT
    v_user.ldap_id, count(*) roles
    FROM cognoscon.cmreford1 v_group_user
    JOIN-- GET USERS
    (SELECT cmid user_id, UPPER(NAME) ldap_id
     FROM cognoscon.cmobjprops33) v_user
    ON v_group_user.refcmid = v_user.user_id
    JOIN--GET GROUPS
    (SELECT cmobjnames.cmid cmid,UPPER(cmobjnames.NAME) name
    FROM cognoscon.cmobjects,cognoscon.cmobjnames
    WHERE cmobjects.classid IN (26, 54)
    AND cmobjnames.mapdlocaleid = 24
    AND cmobjects.cmid = cmobjnames.cmid) v_group
    ON v_group.cmid = v_group_user.cmid
    --where v_group.name <> 'SYSTEM ADMINISTRATORS'
    group by v_user.ldap_id
    having count(*) > 1
     )


This also highlights duplicates, if there are any. (where a user has been assigned to multiple roles).

But now I want to actually pull up the Capabilities assignments through a query.  It would be super helpful for organizing my quarterly audits if I could have a query that pulled all capabilities, so I can easily show that the Roles called 'Consumer' are actually consumer licenses, and not authors.  Right now to prove that includes navigating through Cognos Security and taking ALOT of screenshots :(