000039413 - How to get the list of users, theirs corresponding groups and last login using SQL script in Archer

Document created by RSA Customer Support Employee on Nov 5, 2020
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000039413
Applies ToProduct Set: Archer
Product/Service Type: Access Control
Version/Condition: 6.X
O/S Version: Windows
IssueThe "Members by Group" Access Control Report in Archer does not show all groups on the same page. Administrators have to go through each group page by page.
ResolutionPlease coordinate with your Database Administrator and request to run the following query against the Archer Instance Database in order to get the full list of users and their corresponding groups, and the last login time of that user:

SELECT tblLogin.user_id as USER_ID, tblUser.user_display_name as DISPLAY_NAME, tblUser.user_username as USERNAME, tblGroup.group_name as GROUP_NAME, tblLogin.last_attempt as LAST_LOGIN_TIME
FROM tblLogin
INNER JOIN tblUser ON tblUser.user_id=tblLogin.user_id
INNER Join tblXGroupsUsers ON tblUser.user_id=tblXGroupsUsers.user_id
INNER JOIN tblGroup ON tblXGroupsUsers.group_id=tblGroup.group_id