Want to view Endpoint & Entilement details

[Community_User]

Originally posted on December 1 2021 at 07:09 UTC

Hi Team,

We are using Saviynt v5.5SP3.

We want a SQL query to show endpoint and entitlement details.

Anybody can suggest please.

Thanks jaya

[Community_User · Accepted solution]

Originally posted on December 1 2021 at 09:38 UTC

Hi Jaya

Please find the sample query.. You can modify as per your requirement( inlcuding appropriate custom properties etc). Please let me know if it helps.

select

ev.entitlement_valuekey,

ev.entitlement_glossary,

ev.entitlementid,

ev.entitlement_value,

et.entitlementname ,

ep.endpointname

from entitlement_values ev

left join entitlement_types et ON

ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY

left join endpoints ep ON

et.endpointkey = ep.ENDPOINTKEY

where ep.endpointname IN ('####');

Thanks

Shyam Sundar

[Community_User]

Originally posted on December 1 2021 at 11:08 UTC

Hi Shyam,

Thanks for the help.

I also want to view user details with all these details can you help with that also. 

Thanks 

Jaya

[Community_User]

Originally posted on December 1 2021 at 13:32 UTC

Hi Jaya

I hope you are looking to find the accounts of the users linked to the entitlements correct?  I believe you have to add join statements appropriately 

Can you try this.

I hope query shared earlier worked..Please let us know.

select ep.endpointname as "ENDPOINTNAME", u.username as "USERNAME"

from

users u

left join user_Accounts ua on

u.userkey = ua.userkey

left join accounts acc on

acc.accountkey = ua.accountkey

left join endpoints ep on

ep.endpointkey = acc.endpointkey

where u.username in ('####')

[Community_User]

Originally posted on December 1 2021 at 19:46 UTC

Thanks Shyam for your answer.

Regards,

Avinash Chhetri

[Community_User]

Originally posted on December 2 2021 at 06:10 UTC

Thanks Shyam for your Answer. It's working.

Regards

Jaya

[Community_User]

Originally posted on December 2 2021 at 12:53 UTC

Hi

Can anyone help me with this query.

I want unique users record. And this query is giving duplicate users with different entitlement_value.

select ep.endpointname as "ENDPOINTNAME",ev.entitlement_value, u.username as "USERNAME" ,u.STATUSKEY,acc.STATUS
from users u
left join user_accounts ua on u.userkey = ua.userkey
left join accounts acc on acc.accountkey = ua.accountkey
left join endpoints ep on ep.endpointkey = acc.endpointkey
left join entitlement_types et ON et.endpointkey = ep.ENDPOINTKEY
left join entitlement_values ev ON ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY
where ep.endpointname in ('Netsuite Application Endpoint')

Regards

Jaya

[Community_User]

Originally posted on December 2 2021 at 16:02 UTC

Hi Jaya,

The user, their accounts and entitlements are one of the most widely queried tables in Saviynt. It is important to understand the underlying schema to help you build more/efficient queries in the future.

Here's the document link for the Db Schema : https://saviynt.freshdesk.com/a/solutions/articles/43000521404

Now coming back to your question, If a user account has two distinct entitlements, then they will show up twice, once for each entitlement. That is not a duplicate. 

To find if your entries has true duplicates, also add the account name in Select statement, it could be that a user has two accounts and both these accounts has the same entitlements. Without the account name, it might seem like a duplicate which it is not.

Regards,

Avinash Chhetri

[Community_User]

Originally posted on December 3 2021 at 04:43 UTC

Ok.

Thankyou so much.

Regards

Jaya

[Community_User]

Originally posted on December 16 2021 at 12:20 UTC

Hi

This query is working fine for the users who have entilement value but I also want those users who doesn't have entitlement value.

Can anyone help in this.

SELECT

U.USERNAME,

U.FIRSTNAME,

U.LASTNAME,

E.Endpointname AS 'Endpoint',

e.endpointKey,

A.NAME AS 'ACCOUNTNAME',

A.STATUS as 'ACCOUNT STATUS',

ev.entitlement_value

FROM

ACCOUNTS A,

USER_ACCOUNTS UA,

USERS U,

ENDPOINTS E,

Entitlement_values ev,

account_entitlements1 ae1

WHERE

A.ACCOUNTKEY = UA.ACCOUNTKEY

AND U.USERKEY = UA.USERKEY

AND A.ENDPOINTKEY = E.ENDPOINTKEY

AND U.STATUSKEY = 1

and ae1.accountkey = a.accountkey

and ae1.entitlement_valuekey = ev.entitlement_valuekey

and e.endpointname='CreditLens Application Endpoint'

Thanks 

Jaya