IDM Reporting - Output the IDM User History Report

[former_member243324]

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.

[former_member358098 · Accepted solution]

Hi Shunji

Looks like second part you're trying to achieve have to look like following

added to_char, looks like you're using Oracle studio, and this worked for me

select ATTRNAME, mcmskeyvalue ,Changename, Modifytime from idmv_ovalue_basic_all , idmv_entry_simple
where MSKEY = 156032
and AVALUE = to_char(MCMSKEY)

[former_member243324 · Accepted solution]

Work!!! Thank you Artem!