Revoke user access from selected groups in no activity found for 90days.

[NirupamaN]

Here is the Problem statement: 

1. I have list of roles for ex: role1, role2, role3....
2. I have list of tables for ex: table1, table2, table3,table4....

3. List of user groups for ex: group1, group2

If any user who has one of the roles stated in above listed roles has not  modified the records in above listed tables in the past 90 days then the user access should be revoked from given groups. 

Note: I have used updated_by field in scripts and tried scheduled jobs but it will not work as updated by field only records last updated on/ updated_by on the table but not the previous user who have modified records within given time. 
So I am trying other way: 
I created one custom filed in sys_user table to capture the last update time of the user if they have modified any of the given tables. This will work for one table by creating a BR on that table. The challenge is I need to validate 30+ tables. 

By looking in to all the limitations , any suggestions would be appreciated!

[NirupamaN]

Thanks for the response! 

The above solution will not work as I have tried similar script multiple times. Updated by field will fetches only the latest update of the record. For example if A has updated incident and after 2 days B has updated same incident then updated by field stores the latest updated by as B. Even though A has updated Incident table within past 90 days that will not be considered and A will fall under inactive user and his access will be removed. 

I am trying to  create a BR and create one custom field where it displays the the last updated time of the user if they update any table. By creating scheduling job with that custom field we can remove access. 

The problem here is as we need to check multiple tables, on which table we need to create BR. In my case i need to check 40 tables and its not recommended to create 40 BR's. I am expecting some way to update the updated field on user form. 

Any suggestions on this will be appreciated. 
Thank you!
 

[Mani A]

@NirupamaN 

i have tested below code in my instance and working fine ..just replace dummy data with real data and test it

var tables = ['table1', 'table2', 'table3', 'table4'];

var groups = ['group1', 'group2'];

var gr = new GlideRecord('sys_user_has_role');

gr.addEncodedQuery('userISNOTEMPTY^user.active=true^role=role1^ORrole=role2^ORrole=role3');

gr.query();

while (gr.next()) {

    var uID = gr.user.user_name.toString();

    var flag = false;

    for (var i = 0; i < tables.length; i++) {

        var tableGr = new GlideRecord(tables[i]);

        tableGr.addEncodedQuery('sys_updated_by='+uID+'^sys_updated_on>=javascript&colon;gs.beginningOfLast90Days()');

        tableGr.query();

        if (tableGr.hasNext()) {

            flag = true;

            break;

        }

    }

    if (!flag) {

        for (var k= 0; k < groups.length; k++) {

            var gGr = new GlideRecord('sys_user_grmember');

            gGr.addQuery('user', gr.user.toString());

            gGr.addQuery('group.name', groups[k]);

            gGr.query();

            while (gGr.next()) {

                gGr.deleteRecord();

            }

        }

    }

}