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

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal

Issue/Introduction

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?

Environment

All versions of Identity Suite (Identity Manager)

Resolution

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.