Click HERE to see how Saviynt Intelligence is transforming the industry. |
05/29/2024 03:54 AM - edited 05/29/2024 03:56 AM
Hi,
I have written the below query which gives me the list of users having more than 5 entitlement's for a particular endpoint and it works.
SELECT
U.USERNAME,
group_concat(e.entitlement_value) as 'total'
FROM
USERS U, USER_ACCOUNTS UA, ACCOUNTS A, ACCOUNT_ENTITLEMENTS1 AE, ENTITLEMENT_VALUES E, endpoints ep
WHERE
U.USERKEY = UA.USERKEY AND
UA.ACCOUNTKEY = A.ACCOUNTKEY AND
A.ACCOUNTKEY = AE.ACCOUNTKEY AND
AE.ENTITLEMENT_VALUEKEY = E.ENTITLEMENT_VALUEKEY and ep.endpointkey= a.endpointkey and ep.endpointname='SSM' group by u.username having count(*) > 5
How can I get the list of users having active account in SSM and not having any entitlement ?
Solved! Go to Solution.
05/29/2024 04:02 AM
Hi @NishkalaPuli use the below query
SELECT
u.username, a.name
FROM
users u,
user_accounts ua,
accounts a,
endpoints ed
WHERE
u.userkey = ua.userkey
AND ua.accountkey = a.accountkey
AND ed.endpointkey = a.endpointkey
AND ed.endpointname = 'endpointname'
AND a.accountkey NOT IN (SELECT
ae1.accountkey
FROM
account_entitlements1 ae1
WHERE
ae1.accountkey = a.accountkey)
AND a.status IN ('1' , 'Active', 'Manually Provisioned');
05/29/2024 04:12 AM
Hi,
Thanks, it works!
can you also tell me the table which stores all the rules triggered for that user ? both the technical and user update rules.
Thanks.
05/29/2024 04:13 AM - edited 05/29/2024 04:13 AM
Hi @NishkalaPuli, Try the below query
select a.username from users a join user_accounts b on a.userkey=b.userkey join accounts c on b.accountkey=c.accountkey join endpoints d on c.endpointkey=d.endpointkey where d.endpointname='Active Directory DEV' and c.status=1 and c.accountkey not in (select accountkey from account_entitlements1)
05/29/2024 11:17 PM
select * from userrulerundata
05/29/2024 04:15 AM - edited 05/29/2024 04:19 AM
@NishkalaPuliuserrulerundata table has that information.