03/20/2023 11:45 PM
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
03/21/2023 12:53 AM
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
03/23/2023 12:29 AM
@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,
03/23/2023 08:28 AM
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,