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

[Brandon Grimes]

The first thing I would check is whether any 'cmdb_ci' data is actually being audited. If I'm not mistaken, the 'cmdb_ci' table is not audited OOB. You can verify by doing a Personalize->Dictionary on the 'cmdb_ci' list or form. Then, check the dictionary entry of type 'collection' and see if the 'audit' checkbox is checked.

Proceed with caution, as maybe ServiceNow has a reason for not auditing the 'cmdb_ci' table out of box. It also seems that the 'task' table is not audited OOB. However, all tables extended from 'cmdb_ci' are audited such as 'cmdb_ci_computer'.

Maybe try joining a more specific CMDB table in your view?

[AdityaTW]

H Brandon,
In our case, the table is auditable (Audit is checked).
The CI items are showing in the sys_audit table; and I can find the records using their sys_id on both the cmdb_ci and sys_audit table (which is stored in the documentkey field).
It's the where clause in the database view that is not working.
I wonder if this is because of the comparison betwen sys_id (which is the GUID type) and documentkey (which is char type). If so, how do we convert the GUID to char in the where clause?

Thank you.

Aditya

[Brandon Grimes]

I've been doing some testing myself, and I'm getting some strange results when I try to use database views with the 'sys_audit' table. After some more research, it looks like the 'sys_audit' table may not be stable with database views.

Refer to this older forum posting...
database view - incident history

I'm wondering if there is a way that you can utilize the 'sys_history_set' and 'sys_history_line' tables instead? Creating database views on those may be a little safer, although reporting on any system table is always a bad idea. I'm just brainstorming at this point.

[AdityaTW]

Thanks Brandon. I saw that post from 2011 as well. I am surprised that more than 2 years have gone by and there's still no formal guidelines from ServiceNow on how to properly do audit reporting.
The sys_audit table is just a table, so I am not sure what so dangerous about it, and why it doesn't work with database views.

Thank you.

Aditya