How to obtain the RSA Identity Governance & Lifecycle Application Last Modified Date, Creation Date and ID (Version)

[#1]

Article Number

000035956

Applies To

RSA Product Set: Identity Governance & Lifecycle
RSA Product/Service Type: Enterprise Data Collector (EDC), Account Data Collector (ADC)
RSA Version/Condition: 6.9.1 and above 

Issue

The RSA Identity Governance & Lifecycle UI can show the Last Modified Date for an Application.  However, underneath the covers (i. e.,  in the database), Applications have Creation Dates and IDs, where the Last Modified Date is (really) the Creation Date for that ID.

So if, for example, it needed to be determined which of the Applications were recently onboarded, it cannot be done from the UI. This is because only the Last Modified Date is available, when an Application may have been onboarded in an earlier Creation Date + ID number.

Further, the ID is a sequential number for each Version of the Application, so we can treat the ID as the Version.  However, please note that the ID does not start at 1 for each Application.

Finally, the Creation Date and ID (Version) is kept to maintain consistency for accounts and entitlements that were collected before a modification is made.
 

Task

As this article will be referring to Identity and Governance Lifecycle database tables, please review the following documents, depending on the version.

• RSA Identity Governance and Lifecycle 7.0.2 Public Database Schema Reference
• RSA Identity Governance and Lifecycle v7.0.1 Public Database Schema Reference
• RSA Via L&G 7.0 Public Database Schema Reference
• Identity Management & Governance 6.9.1 Public Database Schema Reference

For Applications, please review the following tables and columns that are available.  Note that there is no actual Last Modified date, as available in the UI.  Instead, there is a Creation Date and ID (Version).

Applications 

Lists all applications defined in RSA Identity Governance and Lifecycle.

SQL> DESC T_APPLICATIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 CURRENT_VERSION_ID                        NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(256)
 CREATED_BY                                NOT NULL NUMBER
 CREATION_DATE                             NOT NULL DATE
...

SQL> DESC T_APPLICATIONS_VERSIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 APPLICATION_ID                            NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(256)
 CREATED_BY                                NOT NULL NUMBER
 CREATION_DATE                             NOT NULL DATE
...

The Last Modified date shown in the UI is actually the CREATION_DATE column in the T_APPLICATIONS table, for the current version of the Application record.
Historical data for earlier versions of an Application record are stored in the T_APPLICATIONS_VERSIONS table.

Resolution

1. Connect to the RSA Identity Governance & Lifecycle database as the AVUSER, then set the formatting for the query results:

# sqlplus avuser/<password>
SQL> col NAME format A45
SQL> ALTER SESSION SET nls_date_format = 'DD/MM/YYYY HH24:MI:SS';

2. To determine the current Applications and their (Version) ID, issue the following SQLcommand:

SQL> SELECT NAME, ID, CREATION_DATE FROM T_APPLICATIONS;

3. To determine when an Application was actually created (the minimum Version ID), issue the following SQL command:

SQL> SELECT A1.NAME, A1.ID, A1.CREATION_DATE FROM T_APPLICATIONS_VERSIONS A1 WHERE A1.ID = (SELECT MIN(A2.ID) FROM T_APPLICATIONS_VERSIONS A2 WHERE A2.NAME = A1.NAME);