Database view-sys_audit table old and new value shows sysid

[sushma vunnam]

I have created a database view(and report also) for u_application table and sys_audit table. I am able to view the selected fields data correctly but with one issue, for the reference fields the old value and new value is showing as sysid's instead of display name.

Can I anyone please help here if we can achieve this?

Example:

                                                 old value   new value 

description- string type             App1      App2  

user-Reference type                   sysid1    sysid2

But here i dont want sysid of the user I want the username here. Please suggest if this is possible in database view.

[Ankur Bawiskar]

@sushma vunnam 

you can use the user column from sys_audit table and it will give the userid but not the name

If my response helped please mark it correct and close the thread so that it benefits future readers.

[sushma vunnam]

HI @Ankur Bawiskar , Thanks for the reply!

but our report should show the enduser old value and new value in real value(Ravi and shankar) not as sys id. So ideally my report should not contain USER field but the old and new values itself should show real value.

[Ankur Bawiskar]

@sushma vunnam 

then no such field is present on sys_audit table

If my response helped please mark it correct and close the thread so that it benefits future readers.

[DrewW]

You would have to create additional Left Joins with the user table and the old/new value fields.  But the report will be confusing and have lots of extra rows.

It would be helpful to know what exactly you are trying to do so other options could be given.

An alternative option would be to use a Remote Table.  A Remote Table is essentially a scripted table.  It has its limitations but it may offer an option to get what you are looking for.

Another option that you could try would be to create another table that will hold the values you are looking for then use a scheduled job that will run once an hour to get records from sys_audit that are for the user fields, look up the values and store them in the new table.  You could then use a database view left join between sys_audit and the new table.  Not sure how feasible this one is.