Generate report from History log

shuguang2
Kilo Expert

Hi all

we have a need to generate a report on modifications happened to a certain field (of a CI class) during a certain time period. This information can be seen on individual CI record, but what we need is to generate a report on the whole CI class.

What I have found is that there is an sys_audit table that captures the Updates to tables/fields. I.e. I can filter on Table Name is cmdb_ci_computer and Field Name is assigned_to. This returns me all audit entries on the field Assigned to of the Computer CI class.

However when I try to filter on a certain time period, the Updated field is all empty (see attached), so that I can't narrow down to a certain time period.

Since in history entries of individual CI, the time stamps of each Updates are captured, I wonder where/which table captures the this information.

any suggestions welcome!

Shuguang

3 REPLIES 3

Mark Stanger
Giga Sage

Search the forums for 'sys_audit' and you'll find what you need. You shouldn't report on 'sys_audit'. Start your study here :).

http://community.service-now.com/node/1001248


I have found in the meantime what I need:
sys_audit entries are created by system whenever an Update is made to a table. So the Created date of the sys_audit entry represents the date of the Update. Udpated date of sys_audit entry is empty, since there is no mechanism to update the sys_audit entry.

I also succeeded in creating database view which links the sys_audit table with the table that was updated, so that I can generate good report (filtering) that shows what has happened to the Updated table.


I know it's been a long time since this reply, and nothing lasts forever on the web, but I have a need to diagnose a problem and the sys_audit table is my only hope.   I can (sometimes) get a list from the sys_audit table, but the entries are a couple of years old.   When I try to sort the table by created date, or filter by table or any other operation, I get a timeout.



I tried to read the document linked in Mark's reply, but it 404's out on me.



Can anyone give me advice on how to see the recent history for a record in the sys_user table?