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

Report Query

Mortal
Regular Contributor
Regular Contributor

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

1 REPLY 1

smitg
Regular Contributor III
Regular Contributor III

Hi @Mortal ,

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