To get the list of 'Termed' users who has termination date as blank

SriRanga
Regular Contributor
Regular Contributor

Hi Team,

We need to get the report which will give the list of user(termed or terminated users) who has termination date as blank in saviynt.

 

Can you please help with the query.

 

Thanks,

Amit Aware

9 REPLIES 9

RakeshMG
Saviynt Employee
Saviynt Employee

select username,TERMDATE ,STATUSKEY from users where TERMDATE is null and STATUSKEY is 0


​Regards

Rakesh M Goudar

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @SriRanga,

Please use the below query for your above use case: 

SELECT username,
       termdate,
       statuskey
FROM   users
WHERE  statuskey = 0
       AND termdate IS NULL 

Thanks,

 

@sudeshjaiswal thanks Sudesh.

I am getting the data but slight change in requirement here is- 

We need to get the report which will give the list of termed user(terminated users) who has termination date as blank in saviynt and customproperty27 from user account is blank and user is associated with 'ServiceNow' endpoint.

Here CP27 holds termination date value of user on servicenow account profile.

Thanks,

Amit Aware

SELECT u.username,
a.NAME
FROM accounts a
left JOIN user_accounts ua
on a.accountkey=ua.accountkey
left JOIN users u
on ua.userkey=u.userkey
WHERE a.endpointkey = (select endpointkey from endpoints where endpointname='ServiceNow') and u.termdate is null and (a.customproperty27 is null or a.customproperty27 ='')

Just replace highlighted part with your endpointname 

Thanks,
Devang Gandhi
If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

SriRanga
Regular Contributor
Regular Contributor

@dgandhi - Does above query checking for INACTIVE users only ? because i have appended condition and u.statuskey=0 and getting 3 records still.

 

Thanks

Add one more condition to check for inactive users

SELECT u.username,
a.NAME
FROM accounts a
left JOIN user_accounts ua
on a.accountkey=ua.accountkey
left JOIN users u
on ua.userkey=u.userkey
WHERE a.endpointkey = (select endpointkey from endpoints where endpointname='ServiceNow') and (u.termdate is null or u.termdate ='') and (a.customproperty27 is null or a.customproperty27 ='') and u.statuskey = 0

Thanks,
Devang Gandhi
If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

SriRanga
Regular Contributor
Regular Contributor

@dgandhi - Thanks for sharing this and is working now.

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @SriRanga,

Am assuming customproperty27is from ServiceNow Account table?

SELECT u.username,
a.NAME
FROM accounts a
JOIN user_accounts ua
using (accountkey)
JOIN users u
using (userkey)
WHERE a.endpointkey IN ('EndPointKey of Service Now Endpoint') and u.termdate is null and a.customproperty27 is null

if cp27 is the cp value on your snow account


SELECT u.username,
a.NAME
FROM accounts a
JOIN user_accounts ua
using (accountkey)
JOIN users u
using (userkey)
WHERE a.endpointkey IN ('EndPointKey of Service Now Endpoint') and u.termdate is null and u.customproperty27 is null



Note:- Please replace the placeholder text ('EndPointKey of Service Now Endpoint') with the actual numeric value of the ServiceNow Endpoint Key.

Thanks.

 

@sudeshjaiswal -

Query is running but getting 3 records only and we have more records in saviynt.

We need termed users whose termination date is blank and belongs to servicenow account and cp27 of servicenow account is blank.

 

Thanks,

Amit Aware