Click HERE to see how Saviynt Intelligence is transforming the industry. |
07/29/2024 08:15 AM
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
Solved! Go to Solution.
07/29/2024 08:18 AM
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
07/29/2024 09:03 AM
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
07/29/2024 08:59 AM
What do you mean by other owners name? Can you elaborate more on this?
Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.
07/29/2024 09:10 AM
Use Group_concat function to retrieve all owners in single column.
All owners will be in comma separated value.
Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.
07/29/2024 10:15 AM
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
07/29/2024 10:19 AM
Share the query that you are trying.
Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.
07/29/2024 10:27 AM
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
07/29/2024 10:33 AM
Use group by clause in your query
Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.