Definitely reporting on sys_audit is almost impossible, but you can use Metrics to get your answers. There is a little setup involved, but it works nice.
Step 1: create new Metric, on Incident table, with Type Script Calculation but leave Script area blank. Field doesn't matter but note sys_id of new Metric.
Step 2: create a before business rule on Incident table like below
Step 3: add the following script in the advanced section of your BR. Update the 2 sys_ids of var MetricSysID to match your Metric from Step 1
{
//sys id of the metric definition
var metricSysID = '3c9eb5770f4a9600c2498f8ce1050ea5';
var mi= new GlideRecord('metric_instance');
mi.addQuery('id',current.sys_id);
mi.addQuery('definition',metricSysID);
mi.query();
//if mi.next will insert another if exists.
//if !mi.next, will insert new entry
if(!mi.next()){
insertMetrics();
}
// since !mi.next inserts new, this will insert updates.
else if(current.active == true && current.operation() == 'update'){
insertMetrics();
}
}
function insertMetrics() {
var mi= new GlideRecord('metric_instance');
//sys id of the metric definition
var metricSysID = '3c9eb5770f4a9600c2498f8ce1050ea5';
mi.initialize();
mi.definition = metricSysID;
mi.start = previous.sys_updated_on;
mi.end = gs.nowDateTime();
mi.duration = gs.dateDiff(mi.start, mi.end);
mi.id = current.sys_id;
mi.value = gs.getUser().name;
mi.calculation_complete = true;
mi.insert();
}
gs.log('Metric trigger');
Step 4: (last one) Build a report of the newly captured data. (Doesn't not work on existing data, just new data after this is implemented
It might seem like a lot but screen shots are better than a bunch of words!
I use this all the time.
