Joining CMDB CI and Audit tables via sys_id and documentkey

[AdityaTW]

Hi all,
I am trying to create a database view. In theory it should be simple. Table A is the cmdb_ci table, table B is sys_audit table. I would like to produce a view where it lists the cmdb_ci items with their corresponding audit activities (field name, old value, new value).
I set up the where clause like this: cmdb_sys_id=audit_documentkey (being cmdb the alias for cmdb_ci and audit the alias for sys_audit) with left join. It does produce the list of the cmdb_ci items but the documentkey is always listed as empty!
I verified that I can find the correct cmdb_ci items in the the cmdb_ci table using the documentkey from the sys_audit table, but I can't seem to make the where clause to work.
Any idea?

Thank you.
Aditya

[ravi1_tandon]

I had a similar situation with database views throwing strange results and I ended up writing a background script to pick up the corresponding field with its old and new value using sys_history_line table.

Regards

Ravi Tandon

[mencio_vidal]

You should be aware that sys_history_set unlikely sys_audit does not persist.

For more info please see: http://wiki.servicenow.com/index.php?title=Viewing_History_Sets

[mencio_vidal]

I solved this by creating a script in a UI Page to join both tables.
Be cautious though, IT IS pretty heavy querying against sys_audit.
Probably better to run it after hours, if you really need to.
You have been warned.