Need to run a report on the Sys Audit (Very Aware of the Implications)

[DeepThinker]

My company would like to run a report on the sys_audit table to see the previously "assigned to" value on the cmdb_ci_computer table. We want to do this on CIs assigned to one particular individual. We've updated a property on our instance that allows reporting on the sys_audit table and we are aware of the implications, however it is important to the business that we get this information. Simply running a report on sys_audit with the conditions: Old Value = not empty; New Value = (Person'sName); Table Name = cmdb_ci_computer; Field Name = Assigned to.... is not producing any results. As a matter of fact the report times out. Can anyone suggest a better way to produce this report. 

[Nickels]

We used to report on the sys_audit table for our accounting department to handle tax changes based on locations of devices. When we first created the report, we also got nothing returned. I found that the report could not really be run on demand, as the audit record wasn't truely compiled for a device until someone actually went to the record and viewed the History List. So we setup the report with the filters we needed and I mirrored those filters in a script for a scheduled report. 

So I scheduled the report with this as a condition:

answer = false;
var cis = new GlideRecord('alm_hardware');
cis.addEncodedQuery('sys_updated_onONLast month@javascript:gs.beginningOfLastMonth()@javascript:gs.endOfLastMonth()');
cis.query();
if (cis.hasNext()){
	answer = true;
	while (cis.next()){
		var hs = new GlideHistorySet(cis);
		var sys_id = hs.generate();
	}
}

Hope this helps.

[John VanBruggen]

This is spot on.  I remember when I learned that the history set records didn't exist until a user actually opens the form for a record.  I was so confused by my inconsistent reports.  Results were so random.   

Your suggestion of a scheduled job is great.  However, you can run this on demand if you take your script and use it in a script include.  In the report you'd query the table with a query of sys_id in javascript:yourScriptInclude()

The script would generate the history set and return the needed data.