Can we fetch all user accounts and orphan accounts for a particular application using a single query

[Kanchukuri]

Hi Team,

Can we fetch all user accounts and orphan accounts for a particular application using a single query. If so, please provide. thanks in advance.

regards,

mahesh

[rushikeshvartak · Accepted solution]

Orphan Accounts

SELECT a.name 'Account Name', e.displayname 'Application Name', CASE WHEN a.status = 1 THEN 'Active' ELSE a.status END AS 'AccountStatus', e.endpointKey, a.accountclass, a.ACCOUNTTYPE FROM accounts a INNER JOIN endpoints e ON a.endpointKey = e.endpointKey AND a.status IN (1 , 'Active', 'Manually Provisioned') LEFT JOIN user_accounts ua ON ua.accountkey = a.accountkey WHERE ua.USERKEY IS NULL;

User Accounts 

SELECT a.name 'Account Name', e.displayname 'Application Name', CASE WHEN a.status = 1 THEN 'Active' ELSE a.status END AS 'AccountStatus', e.endpointKey, a.accountclass, a.ACCOUNTTYPE FROM accounts a INNER JOIN endpoints e ON a.endpointKey = e.endpointKey AND a.status IN (1 , 'Active', 'Manually Provisioned') LEFT JOIN user_accounts ua ON ua.accountkey = a.accountkey WHERE ua.USERKEY IS NOT NULL;

[dgandhi · Accepted solution]

Can you try this?

If Username is null then its orphan and not associated with any identity.

[Kanchukuri]

Hi Gandhi,

Thanks for the above query and it is working fine. please also share the query if we want to extract entitlement values for both user & orphan accounts

regards,

mahesh

[rushikeshvartak]

Refer https://forums.saviynt.com/t5/identity-governance/analytics-query-to-fetch-accounts-under-a-particul...