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

Need to disable user access if user's last login date is more than 90 days

Rajesh_IAM
Regular Contributor
Regular Contributor

Hi Team,

Requirement:  Need to disable user's access if user's last login date in Saviynt is more than 90 days. I have reviewed the users table, but don't see any attribute in users table which stores user's login date. How can I design/implement this requirement in saviynt.? 

Please review this requirement and help me on resolution.

Thank you in advance.

Regards,

Rajesh

3 REPLIES 3

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @Rajesh_IAM,

You can use the below query in analytics to fetch the details

select u.username , max(logintime) from users u, userlogins l where l.userkey=u.userkey group by u.userkey

For ref:- https://forums.saviynt.com/t5/identity-governance/user-last-login-date/m-p/24472#M12682
Thanks

If you find the above response useful, Kindly Mark it as "Accept As Solution".

Rajesh_IAM
Regular Contributor
Regular Contributor

@sudeshjaiswal  Thank you for response. But here requirement is we have to make external users status to inactive  based on their last login date in saviynt. have prepared this below query.
select u.username, ul.logintime, max(logintime) as LastLoginDate, ul.logoutdate, max(logoutdate) as LastLogoutDate from users u, userlogins ul where u.username like 'GUEST%' and ul.userkey=u.userkey and max(ul.logintime) > (DATE_SUB(NOW(), INTERVAL 90 DAY))

Query seems ok, but not executing correctly.
Please can you review this query and let me know, if any wrong in this query.

Regards,

 

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @Rajesh_IAM ,

Can you try the below query, 
select u.username, max(ul.logintime) as LastLoginDate, max(ul.logoutdate) as LastLogoutDate from users u, userlogins ul where u.userkey=ul.userkey group by u.username having datediff(max(ul.logintime),now()) < -90

Please let us know if it helps.

Thanks,

If you find the above response useful, Kindly Mark it as "Accept As Solution".