How to extract user login information from the Identity Manager runtime database

Document ID : KB000013116
Last Modified Date : 14/02/2018
Show Technical Document Details
Question:

Which table in the IM runtime database contains a list of users who have successfully logged into or failed to log into the IM UI, and how can this be obtained from the database?

Answer:

If auditing of Login/Logout events is enabled for an Identity Manager environment, a list of users who have accessed the system can be obtained from the imsAuditEvent12 table of the IM object store.  For example, on MS-SQL, a query like the following will provide a list of users who have successfully accessed the system:

SELECT *  FROM imsAuditEvent12 where event_name = 'Login' and event_state = 'completed'

Unsuccessful Login attempts can be shown with a query such as:

SELECT *  FROM imsAuditEvent12 where event_name = 'Login' and event_state = 'invalid'

The admin_name column of that table shows the user's login ID.