I need to create a job that can find inactive users and send notification that their access is being remove. The finding and notifying is pretty easy. The removing part was what I am a little unsure of from a job.
I see that when I uncheck the "Active" checkbox in TAC for a user, it sets the DELETEDATE and DELETED column to 1. This is pretty much what I would like to do so that if the user responds with some outrage at being removed, we can just go back in and check the "Active" box again.
There are some exceptions to this where we have users that have never logged in once in a year and I am thinking we can just remove them entirely. I am unsure about this since I can see that the USERS table is referenced by a lot of other tables though if a user has never logged into TAC or into Studio the chances their user id is in any of those other tables is pretty slim.
I am really just wondering what other folks are doing to handle this scenario and if I am way off base here. Thanks!
The query I am using currently to find these users looks like this where I check TAC, studio, and to make sure it is not an id we are using in a metaservlet:
select login, to_char(creationdate,'Mm/DD/YYYY')
from sys_talend71_dev.users
where LASTADMINCONNECTIONDATE is null
and LASTstudioconnectiondate is null
and creationdate < add_months(sysdate,-6)
and login not in
(select substr(b.triggeredby, instr(b.triggeredby,'''',1,1)+1,instr(b.triggeredby,'''',1,2)-instr(b.triggeredby,'''',1,1)-1)
from SYS_TALEND71_DEV.TASKEXECUTIONHISTORY b)
and rownum < 5
