More users in the database
I have been trying to pull data from RSA Archer's API and I noticed something weird. When I call `RSAArcher/platformapi/core/system/user` I get back "all users" from the database, well at least thats what i assumed. I noticed there were more users in the database than what is being returned from the "all users" api. I created a Python dictionary of all UserIds to Usernames that was found from the above route. I then queried a specific group for all Userids and bounced it off of my dictionary. I received a few "KeyErrors" and was confused why...So, I looked in the database (doing a `SELECT * FROM [Instance].[dbo].[tblUser] WHERE user_id = '123123'`) and this kicked back a result. However, the result was a bit strange. The username was a concatenation of the current user_id and an already existing username in the database, like `123123_cribbsky`
So, I decided to do a count test. I did `SELECT count(*) FROM [Instance].[dbo].[tblUser]` the results were ~100,000 and the count of my dictionary (from the all users API) resulted in ~60,000. When I visit Archer and open Admin > Users the list matches the ~60,000. Now, if i do an API query for a specific user like `RSAArcher/platformapi/core/system/user/USERID` it does return the user.
We do use LDAP Sync and we have changed LDAPs before. I am thinking that those old users are from an old LDAP somehow..
Do you think its safe to delete these users? I did look through the list and there seems to be some protected accounts like the admin account you can create through the Archer Configuration Panel that are not listed in the "All Users" API, but there are other accounts that match 123123_username that i can regex to delete.
Why do you think there more users in the database?
Why do they not show up in the API results?
Thanks for any info
EDIT: Oh, and i see that most of them have an account_status of 99999
Archer API and UI does not return so called "deleted" users. These are with status 99999. SQL returns these, but Archer does not see them anymore. I assume that is the difference in your observations.
Also, DO NOT delete these 99999 users if you do not want to corrupt database. You can probably ask RSA Support and engineering on providing cleanup script if you really need these to be deleted though, however, not recommended.
They are returned by Archer via the API, but only when calling on that user id directly.
Where is the documentation on the above statement? I wasn't planning on deleting the 99999 users via the Database, but through the API (which shouldn't corrupt the database, if so Archer has issues with their API). Also, these users are counted towards the "licensed users" via the Archer Control Panel. So there is all kinds of weirdness here.
Deleted or 99999 users are not counted towards the license. And deleting over API means marking as 99999.
If you can call them over user id in API, means these users are not deleted. Also, as they are counted in license, they cannot be inactive too.
But like I said, select * from tblUser and get all users over API will definitely be different due to 99999 case.
And only active/locked users are affecting licensing.
Yeah in our Archer Control panel we have 102,000 users counted as the license.. Total count of users in the database 115k... 53k are 99999 users, 61k are not 99999. So something strange going on there.
If you call the api on a specific user (even 99999 users) it returns data. If you query all users, 99999 users wont return. Also, they are still listed in groups.
If you dont mind trying the same thing: Create a user > Add that user to group > delete user that user > query Group for all users > see if deleted user exist in there > query `RSAArcher/platformapi/core/system/user/USERID` and see if you get a response...
Thanks for you replies, I'll submit a support case and see what they do about fixing it.
Yeah, probably by explicitly calling DELETED ID over API, it would return. Otherwise, will filter out.
Strange that you have 53k 99999 users are counted towards license. That is something to be submitted to support as you did. However, I think that is due to the deleted user still seen in the group. At least cleanup job should take care of such things. Definitely, weirdo.
Regarding your test case:
1. Getting user over API over ID - will always return even if user got deleted.
2. 6.6 P3 - created user > assigned to the group > checked user is in the group > deleted user > checked user IS NOT in group > user IS NOT counted in the license.
I suspect you have issue in the deletion. Maybe API call for deletion has issue.
Also, the cleanup job definitely should deal with such cases, maybe you can also look into failed jobs in JobEngine plugin.