Reporting on field change which was made in the past

[lukasrudaitis]

Hi Dear Community,

I would like to know how to get report regarding field change which was made in the past.

Example: We have table with a input field Service Manager. After some time manager field is changed to new manager. How can I catch this change and to what value it was changed?

Thank you.

[Pradeep Sharma]

Hello Lukas,

You can track this via metrics.

Metrics

Ideally all those values are stored in sys_audit, sys_history_line tables in servicenow.

http://wiki.servicenow.com/index.php?title=Viewing_a_Record%27s_Change_History#gsc.tab=0

http://wiki.servicenow.com/index.php?title=Understanding_the_sys_audit_Table

[lukasrudaitis]

Hi Pradeep,

Thanks I was able to locate history lists for table entries and can see the requried data there.

What I am not sure how to create a report on it.

Basically I have a table of 2k entries and on each entry I am only interested in Old and New value of one specific field.

Still not sure how to proceed from here.

[Josh Cooper]

The sys_audit table is a tough one to report on - if you're wanting to know for a group of articles, instead of just one, then it's better to use metrics.   Starting I believe in Jakarta, there are two fields for values, where you can capture the field value and additionally the person who changed it - other than that, it's less than ideal, but you may need to add another field to the metrics table to hold that second parcel of information.

Note that you would need to use a scripted metric rather than a field value duration, because of that additional bit of data you want to capture.   It shouldn't be a tough configuration though, and there are several out of the box examples so that you can see how to do it.

Another note - if your table isn't extended from task, then you'll need to copy over the business rule from the task table that tells it to check metrics after each update.

Once you've got the metrics working and collecting instances, then you can report against the metric_instance table to get old and new values, and the Number of the table record that it refers to - if you need more information from the number, then you'll need to make a new Database View, that merges the Table itself with the Metric Definition and the Metric Instance.   Take a look at the incident_metric database view and it should show exactly how to do it.  

Once that's done, you can just report on that new database view just as if it were a table that has all the fields of all 3 table.