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:
-- 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)
-- 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 :(