Need to get all the Entitlement/Role Owners

[rama_roy]

Hi Team,

We are using the below query in analytics and sending the role and entitlement details to individual users. Now there is a requirement to add another column to get the other owners name. Can you please help me on this?

Existing query:

select
ev.DISPLAYNAME as DisplayName,
ev.ENTITLEMENT_VALUE as Description,
u.username as username,
e.endpointname as Application,
eo.UPDATEDATE as Update_Date
from entitlement_values ev,
entitlement_owners eo,
users u,
entitlement_types et,
endpoints e
where
ev. ENTITLEMENT_VALUEKEY=eo.ENTITLEMENT_VALUEKEY and
eo.USERKEY=u.USERKEY and
ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY and
e.ENDPOINTKEY=et.ENDPOINTKEY and ev.status=1 union
select
r.DISPLAYNAME as DisplayName,
r.ROLE_NAME as Description,
u.username as username,
"Enterprise Role" as Application,
r.UPDATEDATE as Update_Date
from
roles r,
role_owners ro,
users u
where
r.ROLEKEY=ro.ROLEKEY
and
ro.USERKEY=u.USERKEY and r.status=1

Regards,

Rama

[rushikeshvartak]

SELECT ev.displayname                       AS DisplayName,
       ev.entitlement_value                 AS Description,
       u.username                           AS username,
       Concat(u.firstname, "", u.lastname) AS owner_full_name,
       e.endpointname                       AS Application,
       eo.updatedate                        AS Update_Date
FROM   entitlement_values ev,
       entitlement_owners eo,
       users u,
       entitlement_types et,
       endpoints e
WHERE  ev. entitlement_valuekey = eo.entitlement_valuekey
       AND eo.userkey = u.userkey
       AND ev.entitlementtypekey = et.entitlementtypekey
       AND e.endpointkey = et.endpointkey
       AND ev.status = 1
UNION
SELECT r.displayname                        AS DisplayName,
       r.role_name                          AS Description,
       u.username                           AS username,
       Concat(u.firstname, "", u.lastname) AS owner_full_name,
       "enterprise role"                    AS Application,
       r.updatedate                         AS Update_Date
FROM   roles r,
       role_owners ro,
       users u
WHERE  r.rolekey = ro.rolekey
       AND ro.userkey = u.userkey
       AND r.status = 1 

[rama_roy]

Hi Rushikesh,

The requirement is not getting the owner full name. We need to get all the owner name in a single column in case of multiple owner.

Regards,

Rama

[dgandhi]

What do you mean by other owners name? Can you elaborate more on this?

[dgandhi]

Use Group_concat function to retrieve all owners in single column.

All owners will be in comma separated value.

[rama_roy]

Hi Devang,

I am using the below analytics to send the role and entitlement details to individual user. Now if entitlement "ABC" is having rank1,rank2 and rank3 as owners then we need to sholw all the owners for every entitlement the user is having.

I am getting 21 entitlements while running without group_concat(u.username). but getting only one row with group_concat.

Regards,

Rama

[dgandhi]

Share the query that you are trying.

[rama_roy]

select
ev.DISPLAYNAME as DisplayName,
ev.ENTITLEMENT_VALUE as Description,
u.username as username,
group_concat(u.username separator ','),
e.endpointname as Application,
eo.UPDATEDATE as Update_Date
from entitlement_values ev,
entitlement_owners eo,
users u,
entitlement_types et,
endpoints e
where
ev. ENTITLEMENT_VALUEKEY=eo.ENTITLEMENT_VALUEKEY and
eo.USERKEY=u.USERKEY and
ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY and
e.ENDPOINTKEY=et.ENDPOINTKEY and ev.status=1 union
select
r.DISPLAYNAME as DisplayName,
r.ROLE_NAME as Description,
u.username as username,
group_concat(u.username separator ','),
"Enterprise Role" as Application,
r.UPDATEDATE as Update_Date
from
roles r,
role_owners ro,
users u
where
r.ROLEKEY=ro.ROLEKEY
and
ro.USERKEY=u.USERKEY and r.status=1

without group_concat getting all the entitlements

[dgandhi · Accepted solution]

Use group by clause in your query