Evaluate user has performed any activitiy in given tables if not remove the user from given groups.

[Nirupama Reddy]

Hi All, I have the below requirement. Can you please provide a solution for this?

1. Evaluate all of the users that have the following roles AND where the Role was granted to them >90 days ago:

• sn_bcm.admin
• sn_bcm.contributor
• sn_bcm_contributor
• sn_bcm.core_viewer
• sn_bcm.planner
• sn_bcm.program_manager
• sn_bcm.recovery_team_member
• sn_bcm.viewer
• sn_bcp.plan_viewer
• sn_bia.bia_contributor
• sn_bia.bia_viewer
• sn_crisis_ebn.viewer
• sn_recovery.event_member
• sn_recovery.event_viewer

2. Evaluate if the user is the Created By or Updated By user of the following Tables in the past 3 months:

• BCM Choice [sn_bcm_choice]
• Documentation Section [sn_bcm_document]
• Element Definition [sn_bcm_element_definition]
• Element variable [sn_bcm_element_variable]
• Grid configuration [sn_bcm_grid_configuration]
• Grid column configuration [sn_bcm_grid_column_configuration]
• Impact analysis question [sn_bcm_impact_analysis_question]
• Impact Category[sn_bcm_impact_category]
• Impact Rating [sn_bcm_impact_rating]
• Loss Scenario [sn_bcm_loss_scenario]
• Recovery Tier [sn_bcm_recovery_tier]
• Recovery Timeframe [sn_bcm_timeframe]
• Recovery Tasks [sn_bcp_recovery_task]
• Related Plan [sn_bcp_plan_plan]
• Plan Documentation [sn_bcp_document]
• Plan [sn_bcp_plan]
• Plan Assets [sn_bcp_plan_asset]
• Related Asset Dependency [sn_bcp_plan_asset_dependency]
• Plan Loss Scenario [sn_bcp_plan_loss_scenario]
• Plan Task [sn_bcp_plan_task]
• Recovery Strategy [sn_bcp_recovery_strategy]
• Recovery Team [sn_bcp_recovery_team]
• Plan Template [sn_bcp_template]
• Dependency report source [sn_bia_dependency_report_source]
• Impact Analysis [sn_bia_analysis]
• Impact Category Result [sn_bia_category_result]
• Dependency [sn_bia_dependency]
• Impact Dependency Group [sn_bia_dependency_group]
• Result [sn_bia_result]
• BIA Template [sn_bia_template] Score timeframe mapping [sn_bia_score_timeframe_mapping]
• Activated Plan [sn_recovery_activated_plan]
• Event [sn_recovery_event]
• Event Assets [sn_recovery_event_asset]
• Event Task [sn_recovery_event_task]
• Impacted Asset to Activated Plan [sn_recovery_impacted_asset_to_activated_plan]
• Approval [sn_recovery_approval]
• Approval Configuration [sn_grc_appr_approval_configuration]
• Approval Level [sn_grc_appr_approval_level]
• Approval Rule [sn_grc_appr_approval_rule]

3. If they are not, then remove them from all of the following Groups:

• BCM Administrator
• BCM Contributor
• BCM Contributor
• BCM Core Viewer
• BCM Planner
• BCM Program Manager
• BCM Recovery Team Member
• BCM Viewer
• BCP Plan Viewer
• BIA Contributor
• BIA Viewer
• Crisis Viewer
• GRC Approval Config. Admin
• GRC Approver
• Recovery Event Member
• Recovery Event Viewer