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

Need SQL query/analytics to get list of Active users in Saviynt but not yet provisioned to AD

Diwakar
Regular Contributor
Regular Contributor

Hello I am using below query to fetch the users records for my requirement but I am getting no data found. 

select u.username, u.firstname, u.lastname, u.statuskey as status_key, accounts.name as account_name, endpoints.ENDPOINTNAME, accounts.STATUS as account_status, et.displayname as entitlement_type, ev.entitlement_value from accounts left join user_accounts on accounts.ACCOUNTKEY=user_accounts.ACCOUNTKEY left join endpoints on endpoints.ENDPOINTKEY = accounts.ENDPOINTKEY left join users u on u.userkey = user_accounts.userkey left join account_entitlements1 ae on ae.accountkey = accounts.ACCOUNTKEY left join entitlement_values ev on ev.entitlement_valuekey = ae.entitlement_valuekey left join entitlement_types et on et.entitlementtypekey = ev.entitlementtypekey where endpoints.ENDPOINTNAME in ('Active Directory') and accounts.status not in ('1','2','Active','Manually Provisioned','SUSPENDED FROM IMPORT SERVICE')

Please help to get the query for my requirements.

20 REPLIES 20

pmahalle
All-Star
All-Star

Hi @Diwakar ,

You can use below query to get active users without Active Directory account

SELECT USERNAME, FIRSTNAME, LASTNAME, STATUSKEY AS STATUS_KEY FROM USERS WHERE STATUSKEY=1 AND USERKEY NOT IN (SELECT U.USERKEY FROM USERS U, USER_ACCOUNTS UA, ACCOUNTS A, ENDPOINTS E WHERE U.USERKEY = UA.USERKEY AND UA.ACCOUNTKEY = A.ACCOUNTKEY AND A.ENDPOINTKEY= E.ENDPOINTKEY AND E.ENDPOINTNAME='Active Directory')


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

Diwakar
Regular Contributor
Regular Contributor

Hi Paddy,

Thanks for your response, following query is working and I am able to get the active list of users where AD account is not provisioned but its not giving such active users list for which AD account is showing status as SUSPENDED FROM IMPORT SERVICE. Hence please help to provide the query to get active users list from both the cases.

Diwakar.

Hi @Diwakar ,

Try below query and check

SELECT USERNAME, FIRSTNAME, LASTNAME, STATUSKEY AS STATUS_KEY FROM USERS WHERE STATUSKEY=1 AND USERKEY NOT IN (SELECT U.USERKEY FROM USERS U, USER_ACCOUNTS UA, ACCOUNTS A, ENDPOINTS E WHERE U.USERKEY = UA.USERKEY AND UA.ACCOUNTKEY = A.ACCOUNTKEY AND A.ENDPOINTKEY= E.ENDPOINTKEY AND E.ENDPOINTNAME='Active Directory')
UNION
SELECT USERNAME, FIRSTNAME, LASTNAME, STATUSKEY AS STATUS_KEY FROM USERS WHERE STATUSKEY=1 AND USERKEY IN (SELECT U.USERKEY FROM USERS U, USER_ACCOUNTS UA, ACCOUNTS A, ENDPOINTS E WHERE U.USERKEY = UA.USERKEY AND UA.ACCOUNTKEY = A.ACCOUNTKEY AND A.ENDPOINTKEY= E.ENDPOINTKEY AND E.ENDPOINTNAME='Active Directory' AND UPPER(A.STATUS) IN ('SUSPENDED FROM IMPORT SERVICE'))


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

Diwakar
Regular Contributor
Regular Contributor

Thanks Paddy, but unfortunately this query is giving the result of both the cases as well. The users which are having AD as Active and as well as 'SUSPENDED FROM IMPORT SERVICE'(rehire scenarios). We wanted to have that list of active users as well where AD status is only 'SUSPENDED FROM IMPORT SERVICE'. Attaching screenshot of one such user which I got from your query.

Diwakar
Regular Contributor
Regular Contributor

Hi, Paddy, any further suggestions to filter our users as per above requirement.

Regards,

Diwakar.

armaanzahir
Valued Contributor
Valued Contributor

Hi @Diwakar ,

select username,firstname,lastname,statuskey from users where userkey not in (select ua.userkey from user_accounts ua left join accounts a on ua.accountkey=a.accountkey and a.endpointkey=6 and a.status in ('1','2','Active','Manually Provisioned','Manually Suspended','Inactive'))

Please replace the endpointkey with the endpointkey of your app.

Regards,
Md Armaan Zahir

dgandhi
All-Star
All-Star

Can you elaborate more on the requirement? What is the expected outcome that you want?

 

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

Diwakar
Regular Contributor
Regular Contributor

Hi Devang,

My requirement is to get the list of all such active users in saviynt for which AD account is not at all provisioned that includes both cases where either AD account is not provisioned or its either Deleted(i.e. Account status having SUSPENDED FROM IMPORT SERVICE).

Try below

select username,firstname,lastname,statuskey
from users where userkey not in (select ua.userkey from user_accounts ua left join accounts a on ua.accountkey=a.accountkey and a.endpointkey=6 and a.status in ('1','2','Active','Manually Provisioned','Manually Suspended','Inactive','SUSPENDED FROM IMPORT SERVICE'))

Replace the endpointkey of AD 

 

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

Diwakar
Regular Contributor
Regular Contributor

Thanks Gandhi your provided query is partially suffice our requirement but we wanted to have that list of active users where AD status is only 'SUSPENDED FROM IMPORT SERVICE'. Attaching screenshot of one such user which is Active and AD is 'SUSPENDED FROM IMPORT SERVICE' and no other account state.

select username,firstname,lastname,statuskey
from users where statuskey =1 and  userkey not in (select ua.userkey from user_accounts ua left join accounts a on ua.accountkey=a.accountkey and a.endpointkey=6 and a.status in ('1','2','Active','Manually Provisioned','Manually Suspended','Inactive','SUSPENDED FROM IMPORT SERVICE'))

 

This is base query, you can always modify with what status you want in the output.

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

Diwakar
Regular Contributor
Regular Contributor

Yes Devang, I already tried by modifying the status based on my requirement but its still giving me not the correct output. Attaching output screenshot for one of the user where both AD Account is showing as Active and another AD account SUSPENDED FROM IMPORT SERVICE. How to get the list which is only SUSPENDED FROM IMPORT SERVICE for Active users?

SELECT USERNAME, FIRSTNAME, LASTNAME,EMAIL, STATUSKEY,CUSTOMPROPERTY13 AS 'IT_USER_MAPPING' FROM USERS STATUSKEY=1 AND USERKEY IN (SELECT U.USERKEY FROM USERS U, USER_ACCOUNTS UA, ACCOUNTS A, ENDPOINTS E WHERE U.USERKEY = UA.USERKEY AND UA.ACCOUNTKEY = A.ACCOUNTKEY AND A.ENDPOINTKEY= E.ENDPOINTKEY AND E.ENDPOINTNAME='Active Directory' AND UPPER(A.STATUS) IN ('SUSPENDED FROM IMPORT SERVICE') and a.status not in ('1','2','Active','Manually Provisioned','Manually Suspended','Inactive'))

Hi @Diwakar,

Please validate and let us know if the below query works for you.

SELECT
U.USERNAME,
U.FIRSTNAME,
U.LASTNAME,
U.EMAIL,
U.STATUSKEY,
U.CUSTOMPROPERTY13 AS 'IT_USER_MAPPING'
FROM
USERS U
JOIN
USER_ACCOUNTS UA ON U.USERKEY = UA.USERKEY
JOIN
ACCOUNTS A ON UA.ACCOUNTKEY = A.ACCOUNTKEY
JOIN
ENDPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY
WHERE
E.ENDPOINTNAME = 'Active Directory'
AND UPPER(A.STATUS) = 'SUSPENDED FROM IMPORT SERVICE'
AND A.STATUS NOT IN ('1', '2', 'Active', 'Manually Provisioned', 'Manually Suspended', 'Inactive')
AND U.STATUSKEY = 1;

Hi Dixshant, Thanks for your help but unfortunately this query also giving me the same output. Attaching output screenshot of one of the user where both AD Account is showing as Active and another AD account SUSPENDED FROM IMPORT SERVICE. 

Any other way to get only SUSPENDED FROM IMPORT SERVICE status users list?

Regards,

Diwakar.

Hi @Diwakar,

Please validate:-

SELECT
u.username,
u.firstname,
u.lastname,
u.statuskey AS status_key,
accounts.name AS account_name,
endpoints.ENDPOINTNAME,
accounts.STATUS AS account_status,
et.displayname AS entitlement_type,
ev.entitlement_value
FROM
accounts
LEFT JOIN user_accounts ON accounts.ACCOUNTKEY = user_accounts.ACCOUNTKEY
LEFT JOIN endpoints ON endpoints.ENDPOINTKEY = accounts.ENDPOINTKEY
LEFT JOIN users u ON u.userkey = user_accounts.userkey
LEFT JOIN account_entitlements1 ae ON ae.accountkey = accounts.ACCOUNTKEY
LEFT JOIN entitlement_values ev ON ev.entitlement_valuekey = ae.entitlement_valuekey
LEFT JOIN entitlement_types et ON et.entitlementtypekey = ev.entitlementtypekey
WHERE
endpoints.ENDPOINTNAME = 'Active Directory'
AND accounts.STATUS = 'SUSPENDED FROM IMPORT SERVICE';

 

Its still the same result Dixshant, getting both the status, one for older AD account and another Active status for newer account. Unable to find any query which will show this Status only 'SUSPENDED FROM IMPORT SERVICE'. Please suggest next.

Please validate:-

SELECT
u.username,
u.firstname,
u.lastname,
u.email,
u.statuskey,
u.CUSTOMPROPERTY13 AS 'IT_USER_MAPPING'
FROM
users u
WHERE
u.statuskey = 1
AND u.userkey IN (
SELECT
U.USERKEY
FROM
USERS U
JOIN USER_ACCOUNTS UA ON U.USERKEY = UA.USERKEY
JOIN ACCOUNTS A ON UA.ACCOUNTKEY = A.ACCOUNTKEY
JOIN ENDPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY
WHERE
E.ENDPOINTNAME = 'Active Directory'
AND UPPER(A.STATUS) = 'SUSPENDED FROM IMPORT SERVICE'
)
AND u.username NOT IN (
SELECT
U.USERKEY
FROM
USERS U
JOIN USER_ACCOUNTS UA ON U.USERKEY = UA.USERKEY
JOIN ACCOUNTS A ON UA.ACCOUNTKEY = A.ACCOUNTKEY
JOIN ENDPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY
WHERE
E.ENDPOINTNAME = 'Active Directory'
AND UPPER(A.STATUS) NOT IN ('SUSPENDED FROM IMPORT SERVICE')
);

Hi Dixshant,

Unfortunately output still the same, I think something is wrong with this status not able to filter right data when using account status('SUSPENDED FROM IMPORT SERVICE'). Please suggest next.

Regards,

Diwakar.

Hi @Diwakar,

I kindly request that you initiate the process of raising a Freshdesk ticket for the current issue. This is essential as the matter requires additional triage and investigation to comprehensively address and resolve. 

Diwakar
Regular Contributor
Regular Contributor

Hi Team,

Any further suggestion on this, still unable to find any way to check the Active users for which AD account is only  SUSPENDED FROM IMPORT SERVICE and no other status.

Regards,

Diwakar.