Steps to cleanup the user-device association and device information for mobile channel

Products

• CA Risk Authentication
• CA Advanced Authentication - Risk Authentication (RiskMinder / RiskFort)
• CA Advanced Authentication

Issue/Introduction

Steps to cleanup the user-device association and deviceID information for all the users in bulk for mobile channel. Same steps can be applied for other channels ( default ), just the table names will be different in that case.

Environment

Symantec Risk Authentication 9.1.x

Resolution

Follow these steps to cleanup user-device association and device information for mobile channel:

1. Run the following query to identify all user-to-device mappings older than a set date. Replace datelogged with the date from which to search, in the format DD-MMM-YYYY. The query searches for mappings that were created earlier than this date.
   
   create table DeviceID_DeleteList as (select deviceidseqid from ARRFDEVICEINFO where deviceid in (select distinct(httpdeviceid) from ARRFSYSAUDITLOG_MOBILE where datelogged <= '31-DEC-2023'))
   
2. Run the following query to identify all user-to-device mappings for a particular organizaton. Replace Org_Name with the organization name for which the devices to be deleted. The query will create a table with the DeviceIdSeqIds of that particular organization.
   
   create table DeviceID_DeleteList_<Org_Name> as ( select deviceidseqid from DeviceID_DeleteList where deviceidseqid in (select deviceidseqid from ARRFDEVUSERASSO where orgname=<Org_Name>))
   
   
3. Run the following queries to delete device information that is associated with the organization returned in the previous query.
   
   delete from ARRFDEVUSERASSO where deviceidseqid in (select deviceidseqid from DeviceID_DeleteList_<Org_Name>);
delete from ARRFDEVICECONTEXT where deviceseqid in (select deviceidseqid from DeviceID_DeleteList_<Org_Name>); 
delete from ARRFDEVICEINFO where deviceidseqid in (select deviceidseqid from DeviceID_DeleteList_<Org_Name>);
commit;
   
   
4. When completed, run the following query to delete the table created by the first and second query:

drop table DeviceID_DeleteList; 
drop table DeviceID_DeleteList_<Org_Name>;