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

[NishkalaPuli]

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 ?

[naveenss · Accepted solution]

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');

[NishkalaPuli]

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]

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)

[rushikeshvartak]

select * from userrulerundata

[sai_kanumuri]

@NishkalaPuliuserrulerundata table has that information.