We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Detecting users who do not have an account

JPMac
Regular Contributor
Regular Contributor

We need to detective the list of users who do not have an Azure AD account.
The Azure AD endpointkey is 4.
What query can I write to detective the user?

2 REPLIES 2

pmahalle
All-Star
All-Star

Hi @JPMac ,

You can use below query to get users without Azure AD account. 

select username from users where username not in (Select u.username from users u, accounts a, user_accounts ua where ua.accountkey=a.accountkey and ua.userkey=u.userkey and a.endpointkey=4)


Pandharinath Mahalle(Paddy)
If this reply answered your question, please Accept As Solution to help other who may have a same problem. Give Kudos 🙂

rushikeshvartak
All-Star
All-Star

Please use below SQL query

SELECT USERNAME,
       FIRSTNAME,
       LASTNAME,
       EMAIL,
       CASE STATUSKEY
         WHEN 1 THEN 'Active'
         WHEN 2 THEN 'Inactive'
       END AS user_status
FROM   USERS
WHERE  USERNAME NOT IN (SELECT u.USERNAME
                        FROM   USERS u,
                               ACCOUNTS a,
                               USER_ACCOUNTS ua
                        WHERE  ua.ACCOUNTKEY = a.ACCOUNTKEY
                               AND ua.USERKEY = u.USERKEY
                               AND a.ENDPOINTKEY = 4) 

 

 


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