Skip to Content
0
Oct 08, 2019 at 10:21 PM

IDM Reporting - Output the IDM User History Report

147 Views

I am writing the SQL script for export user full history report, which is including both Historical Values and Entry Data.

I wrote as following,

select MCATTRNAME as ATTRNAME, EXTVALUE as AVALUE, MCCHANGENAME as Changename , MCModifytime as Modifytime from MXMC_OPER.idmv_VALLINK_EXT2 WHERE MSKEY = 156032
UNION

select ATTRNAME, AVALUE ,Changename, Modifytime from idmv_ovalue_basic_all where MSKEY = 156032 ;

The result look good except "AVALUE" is a number, not a role name.

Then I tried to join the result to other table "MXMC_OPER.IDMV_ENTRY_SIMPLE" based on "AVALUE = MCMSKEY", but failed. (I believe the error because of AVALUE data_type is NVARCHAR2 and MCMSKEY data_Type is NUMBER)

Any one can give me the suggestion how to fix it? Thank you.

Attachments

sql-question.jpg (64.0 kB)
sql-question1.jpg (36.9 kB)
sql-question2.jpg (51.2 kB)