Query the history content of an asset

[#1]

I would like to know if there is a way to perform a query on the history of the assets, for example I would like to find all the assets that have at some time in their life-cycle been assigned to A N Other.

As I am a novice to Service-Now, if such a query if possible I would appreciate a step by step explanation of how to build the query.

Many thanks in advance

[tony_fugere]

EDIT: After posting this, I realized you may want this in a report instead. If that is the case, look into Metrics instead of this ad-hoc button.

The changes of an asset (or CI) are tracked in a special table called sys_audit. This table contains the SYS_ID (unique ID) of the record that is being audited (in this case our asset or CI, which are the same thing), the field that changed, the old value and the new value.

You would want to query the sys_audit table for the CI in question and the field at play (assigned_to). Try this code (it is untested):

UI Action
Name: Check for Previous Assignments
Table: Configuration Item [cmdb_ci]
Action name: check_prev_assign
Active: true
Show insert: false
Show update: true
Client: false
Form button: true
Condition: leave this blank
Script:

var audit = new GlideRecord('sys_audit');
audit.addQuery('documentkey', current.sys_id);
audit.addQuery('fieldname', 'assigned_to');
audit.query();
if(audit.getRowCount() > 0) {
  gs.addInfoMessage('This asset has been assigned to someone else in the past.');
} else {
  gs.addInfoMessage('This asset has NEVER been assigned to anyone else.');
}
action.setRedirectURL(current);

[#3]

Thanks for a quick response Tony. Your right I was think about a report, but your code is also informative.

[tony_fugere]

If/when you look into metrics, also realize you need the Metric firing mechanisms that come OOB with Task. Find the Business Rule that fires metric updates and copy it to the Configuration Item [CMDB_CI] table in order to utilize on CI's.

[#5]

Thanks again Tony