Report Query

[KetanB]

Greetings,

I would like to get some assistance regarding a report generation where I would like to find the list of users who have no accounts in Saviynt EIC. I tried using the users, accounts and user_accounts table from Data Analyzer in order to join the columns and filter out the users whose accountkey has NULL value but there were no null values. So, can you please let me know which tables and columns to use in order to get the intended result?

Query which I used: Select u.firstname, u.lastname, a.accountkey, a.accountid from user_accounts ua left join users u on ua.userkey = u.userkey left join accounts a on ua.accountkey = a.accountkey where a.accountkey=NULL;

Thanks,
Ketan

[smitg · Accepted solution]

Hi @KetanB ,

Try with below query

select u.username from users u where u.userkey not in (select ua.userkey from user_accounts ua)

User key and account key value exists in user_accounts table only if user has a account.

Thanks,
Smitha