User bulk delete in Archer
We currently use Archer 6.2 in all of our environments. We are using LDAP sync to automatically create Archer accounts.
Unfortunately, we found out that the filter currently used in our LDAP sync is NOT working properly and thus created a lot (10s of thousand) of unwanted user accounts in Archer.
The question is : is there a way to do a BULK delete of any Archer account that NEVER logged on in Archer in order to remove all those unwanted account WITHOUT affecting historical data in Archer ? (we believe that if we only delete any account that NEVER logged in, we shouldn’t affect record history, which is critical for us).
Obviously, we do not want to delete ALL the users in the database but only unwanted accounts.
Any help would be appreciated
We also ended up with too many accounts when we did a LDAP sync. We used the API to delete the unwanted accounts. Your only challenge as I see it is comprising the list based upon your criteria. Support should be able to provide you with a query to extract the list of user account you are interested in. A value of NULL in the user_last_login_date could be used as a filter to identify the accounts you are looking for. Another item you would want to validate is that those account were not used in any Archer records (user groups lists or record permission fields). If both items were found to be Null I would suspect you should be able to delete those accounts and not impact your record history. I hope this helps.
NOTE: Prior to deleting any accounts be sure to backup your database.
Along with Michael Maas suggestions, check out these links:
- Video Link : 21076
I recommend performing a SQL Backup beforehand in case you delete too much and need to restore. Also, work with your LDAP Administrator to come up with the proper User Filter and test in a non-prod environment.
One of the great features added to the Archer product in release 5 was user reassignment. This is the capability to identify and reassign resources from one user to another.
See Reassigning User Resources https://community.rsa.com/docs/DOC-81801
You can leverage this information to further filter your list of users that have never logged in to Archer. It appears this information is stored in tblIVUserContent.
Here we see the content_id of records where a specific user_id has been listed in a user groups list or record permissions field. The field_id is listed as well which can be used to get more information about the specific record.
Once you have your list of accounts that have never logged in to Archer you can run those userids against this table to identify those that have no resources assigned to them. Try this query.
FROM [Archer].[dbo].[tblIVUserContent] where user_id like 177883
If you come up with zero rows returned that userid has no resources assigned to it.
This account came back with 8 hits.
If you need to dig deeper to identify what module those content ids are part of you can use this query to get details on the field id listed.
FROM dbo.tblIVFieldDef f, dbo.tblIVFieldType ty,
dbo.tbllevel l,dbo.tblModule a
WHERE f.field_type_id = ty.field_type_id
AND f.level_id = l.level_id
AND l.module_id = a.module_id
AND f.field_id = 7574
We see that this record belongs to the Control Procedures application which is module id # 153
Anyway, I hope this helps you narrow down your list of accounts.