Fetching data out of sys audit table

[hakansuri]

Hi,

we want to fetch historic incident data from sys audit table. moving forward we have implemented metrics which is populated by a BR on incident table as per business requirements.

but we still need to fetch /report on historic audit data when the metric was not created.

specifically we are looking for all update made to the incident table by members of certain group.

two possible solutions that came to my mind, create database view on audit table join them with sys user table etc, this could be still very slow.

or create a u_sys_audit table with exact same fields as audit table and populate that with some sort of back scripts.

thanks

HS

[Mike Allen]

I would take a clone copy down from prod to a lower environment and write the metrics that did not exist until recently with a background script.   Once those metrics are written, export them and import them into PROD during a down period.   I used to have the script to write the metric instance, but I don't know what I did with it.

[Mike Allen]

The other thing you can do, and this is a performance hog, but you can do it, is add the sys_history_line table to glide.ui.permitted_tables properties and report on it.   I wouldn't, but you could.

[Mike Allen]

The general idea is that you take your metric script (assuming it is a script) like so:

// variables available
// current: GlideRecord -   target incident
// definition: GlideRecord -   (this row)
if (!current.active) {
       if (current.problem_id.problem_state == '2')
               createMetric();
}


function createMetric() {
   var mi = new MetricInstance(definition, current);
   if (mi.metricExists()) 
       return;


   var gr = mi.getNewRecord();
   gr.field_value = true;
   gr.calculation_complete = true;
   gr.insert();
}

and just change the top if statement to a glide record query that will return your set of data.

[hakansuri]

Hi Mike, thanks for the reply, we got a live instance where the update incident metric and business rule(on incident table) which populates the metrics exists.

are you suggesting that when we take a clone of live instance and copy it over to test instance with audit data switched on the metric will get populated?

or after copying the data we need to run the back script to populate the data, we dont need to re populate the metric for live, even if we manage to get that working in test with the historic data from live that should still be fine