Custom SQL query for Data Analyzer

[void0703]

Hi All,

1. An extract of username, displayname, companyname, orgunitid and email which matches the criteria (can't mention) - i can do this  
2. Please also add a column and indicate if they already have the group (entitlement) assigned - need help in this as it contains multiple entitlement_values so not able to achieve this.

Thanks 

[Dhruv_S]

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.

[void0703]

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 

[Dhruv_S]

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