Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

SQL Query to get the list of users having active accounts without any entitlements

NishkalaPuli
New Contributor II
New Contributor II

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 ?

5 REPLIES 5

naveenss
All-Star
All-Star

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');
Regards,
Naveen Sakleshpur
If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

NishkalaPuli
New Contributor II
New Contributor II

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.

sai_kanumuri
Honored Contributor
Honored Contributor

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)


Regards,
Sai Kanumuri
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

select * from userrulerundata

rushikeshvartak_0-1717049821028.png

 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

sai_kanumuri
Honored Contributor
Honored Contributor

@NishkalaPuliuserrulerundata table has that information.

Regards,
Sai Kanumuri
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.