I want to generate Report any entitlement owner terminated.

[Community_User]

Originally posted on September 21 2020 at 06:04 UTC

I am getting error with SQL below. Could you please advise how I can query?

select e.REQUESTOWNER AS 'GroupOwner', ev.ENTITLEMENT_VALUE AS 'Group Name', u.companyname AS 'Companyname', u.customproperty17 AS 'Displayname'

from account_entitlements1 ae1, accounts a , entitlement_values ev, endpoint e, user_account ua, user u

where a.accountkey = ae1.accountkey and ae1.accountkey = ua.accountkey and ev.ENTITLEMENT_VALUEKEY = ae1.ENTITLEMENT_VALUEKEY and a.endpointKey = e.endpointKey and ua.userkey = u.userkey

AND u.statuskey = 0

Thank you

[Community_User]

Originally posted on September 21 2020 at 06:50 UTC

Hello,

The table names used in the above query are incorrect. For eg. There is no table called 'endpoint', the correct table name is 'endpoints'.

Please correct them by going through the schema guide - https://saviynt.freshdesk.com/support/solutions/articles/43000521404-saviynt-security-manager-ssm-sc...

Regards,

Saparja

[Community_User · Accepted solution]

Originally posted on September 21 2020 at 07:22 UTC

Thanks for letting me know. I was able to submit it but not getting any results..

select u.statuskey AS 'User_Status', u.username AS 'Employee No.', e.REQUESTOWNER AS 'GroupOwner', ev.ENTITLEMENT_VALUE AS 'Group Name', u.companyname AS 'Companyname', u.customproperty17 AS 'Displayname'

from account_entitlements1 ae1, accounts a , entitlement_values ev, endpoints e, user_accounts ua, users u, organization_owners oo

where a.accountkey = ae1.accountkey and ae1.accountkey = ua.accountkey and ev.ENTITLEMENT_VALUEKEY = ae1.ENTITLEMENT_VALUEKEY and a.endpointKey = e.endpointKey and ua.userkey = u.userkey and oo.userkey = u.userkey

AND u.statuskey = 0

AND oo.rank = 1