Accounts Having Access v/s Accounts not Having Access

[rambhan]

Hi Team,

We have 17 active accounts and 1 7 active entitlements in an endpoint, we are getting the data of accounts having access from account_entitlement1 table using the below query after provisioning using technical rules:

select * from users u, user_accounts ua, accounts a, account_entitlements1 ae, entitlement_values ev,
entitlement_types et, endpoints e
where u.USERKEY=ua.USERKEY
and ua.ACCOUNTKEY=a.ACCOUNTKEY
and a.ACCOUNTKEY=ae.ACCOUNTKEY
and ae.ENTITLEMENT_VALUEKEY=ev.ENTITLEMENT_VALUEKEY
and ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY
and et.ENDPOINTKEY=e.ENDPOINTKEY
and e.ENDPOINTKEY=33273

In order to acheive the accounts which don't have access we are not getting accurate results using below query:

select u.USERNAME, a.name from users u
join user_accounts ua on u.USERKEY=ua.USERKEY
join accounts a on ua.ACCOUNTKEY=a.ACCOUNTKEY
join account_entitlements1 ae on a.ACCOUNTKEY=ae.ACCOUNTKEY
where a.ENDPOINTKEY=33273 and (select 1 not in (select ae1.ENTITLEMENT_VALUEKEY from account_entitlements1 ae1, entitlement_values ev
where ev.ENTITLEMENT_VALUEKEY=ae1.ENTITLEMENT_VALUEKEY))

Goal is to provision the access to remaining possible accounts which don't have access and also to get 17*17 possible access provision tasks from actionable analytics.

[rushikeshvartak]

SELECT DISTINCT u.USERNAME, a.name
FROM users u
CROSS JOIN accounts a
LEFT JOIN user_accounts ua ON u.USERKEY = ua.USERKEY
LEFT JOIN account_entitlements1 ae ON a.ACCOUNTKEY = ae.ACCOUNTKEY
WHERE a.ENDPOINTKEY = 33273
AND ua.USERKEY IS NULL;

[rambhan]

Hi @rushikeshvartak the above query is not providing accurate results. Can you let me know a not equal to query for the below query.

select * from users u, user_accounts ua, accounts a, account_entitlements1 ae, entitlement_values ev,
entitlement_types et, endpoints e
where u.USERKEY=ua.USERKEY
and ua.ACCOUNTKEY=a.ACCOUNTKEY
and a.ACCOUNTKEY=ae.ACCOUNTKEY
and ae.ENTITLEMENT_VALUEKEY=ev.ENTITLEMENT_VALUEKEY
and ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY
and et.ENDPOINTKEY=e.ENDPOINTKEY
and e.ENDPOINTKEY=33273

[rambhan]

Hi @rushikeshvartak  the below query is providing all results which includes having access and not having access to accounts.

select distinct a.name, ev.ENTITLEMENT_VALUE from accounts a
left join account_entitlements1 ae on a.ACCOUNTKEY=ae.ACCOUNTKEY
cross join entitlement_values ev
join entitlement_types et on ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY
and a.ENDPOINTKEY=33273 and ev.ENTITLEMENTTYPEKEY=52474 and a.ACCOUNTTYPE='user' and a.STATUS=1;

[rushikeshvartak]

Do you need further help ?