Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
10/25/2023 01:19 AM
Hi All,
Thanks
10/25/2023 01:35 AM
Hi @void0703
Could you please clarify the requirement (point 2) mentioned above. It is to display the entitlements assigned to those users matching the criteria or just to check whether those users have entitlements assigned or not in yes/no format.
Please refer to the query in the below post for entitlements assigned. to a user.
Solved: SQL Query to retrieve all the entitlements - Disp... - Saviynt Forums - 55403
Please let us know if you have any other requirements.
Regards,
Dhruv Sharma
If this reply answered your question, please accept it as Solution to help others who may have a similar problem.
10/25/2023 01:43 AM
SELECT U.USERNAME,U.DISPLAYNAME,U.COMPANYNAME,U.ORGUNITID,U.EMAIL,
CASE
WHEN EV.ENTITLEMENT_VALUE LIKE 'CN=sec_demo%'
THEN 'YES' ELSE 'NO'
END AS 'GROUP PRESENT'
FROM ACCOUNTS A
JOIN USER_ACCOUNTS UA ON A.ACCOUNTKEY = UA.ACCOUNTKEY
JOIN USERS U ON UA.USERKEY = U.USERKEY
JOIN ENDPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY
INNER JOIN ACCOUNT_ENTITLEMENTS1 AE1 ON A.ACCOUNTKEY = AE1.ACCOUNTKEY
LEFT JOIN ENTITLEMENT_VALUES EV ON AE1.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY
WHERE E.ENDPOINTNAME IN ('SYS_PRD')
AND U.EMPLOYEETYPE IN ('1','2','3') AND U.ORGUNITID = 'demo123' AND U.COMPANYNAME = 'demo' AND U.STATUSKEY= 1
GROUP BY U.USERNAME
SO if the user has the entitlment vlaue 'CN=sec_demo%' then i want to print "YES" in separate column other wise "NO" as the entitlement_value has multiple values i think so i am facing issue
10/31/2023 01:34 AM
Hi @void0703
Please run the below query and analyse the output of ENTITLEMENT_VALUE column to understand in what format it is giving the ENTITLEMENT_VALUE output. Accordingly, we can advise you further. I don't think LIKE syntax will work in multivalued attribute. You can also try IN ('Exact value of entitlement') instead of LIKE 'CN=sec_demo%'
SELECT U.USERNAME,U.DISPLAYNAME,U.COMPANYNAME,U.ORGUNITID,U.EMAIL, EV.ENTITLEMENT_VALUE
FROM ACCOUNTS A
JOIN USER_ACCOUNTS UA ON A.ACCOUNTKEY = UA.ACCOUNTKEY
JOIN USERS U ON UA.USERKEY = U.USERKEY
JOIN ENDPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY
INNER JOIN ACCOUNT_ENTITLEMENTS1 AE1 ON A.ACCOUNTKEY = AE1.ACCOUNTKEY
LEFT JOIN ENTITLEMENT_VALUES EV ON AE1.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY
WHERE E.ENDPOINTNAME IN ('SYS_PRD')
AND U.EMPLOYEETYPE IN ('1','2','3') AND U.ORGUNITID = 'demo123' AND U.COMPANYNAME = 'demo' AND U.STATUSKEY= 1
GROUP BY U.USERNAME
Regards,
Dhruv Sharma