Business Rule on sys_audit Table - Performance Issue?

[Shawn Dowler]

To be able to report on how many users are getting things assigned to them (and to help find users who are not utilizing their ServiceNow licenses) I created a table with Reference fields to sys_db_object and sys_user and a Document ID field to store the record. I have a couple of options. To populate the table initially, I wrote a script like the following:

(function() {
   var grAudit = new GlideRecord('sys_audit');
   grAudit.addEncodedQuery('fieldname=assigned_to^sys_created_on>javascript:gs.daysAgoStart(1000)');
   grAudit.query();
   while (grAudit.next()) {
       var grHistory = new GlideRecord('u_assigned_to_history');
       grHistory.initialize();
       grHistory.u_assigned_to = grAudit.newvalue;
       grHistory.u_task = grAudit.documentkey;
       grHistory.u_audit_timestamp = grAudit.sys_created_on;
       grHistory.u_table = grAudit.tablename;
       grHistory.insert();
   }
})();

Going forward I could modify this script to only query for records from yesterday for example and schedule it to run nightly or I could create a business rule on the sys_audit table to create these records any time a record is inserted into the sys_audit table matching the query criteria.

My question: should I be worried about adding a business rule to the sys_audit table? With the number of records added all the time, would this significantly, negatively impact performance on my instance? It's difficult to load test something like this. If it's not bad for performance, then I'd much rather do it with a business rule. That way the data is always up to date and I reduce the probability of duplicate records in my reporting table.

[Kalaiarasan Pus]

It seems like the business rule doesnt run on insert on audit table. Have you tried that? If updated data is not required, I would go for a scheduled job to run during non business hours and do the same.

[Shawn Dowler]

I have not actually tried to add a business rule to the sys_audit table yet. If I understand you correctly, you are saying that even if I add one, that the system won't actually run it on insert?

Thank you for your reply.

[Kalaiarasan Pus · Accepted solution]

Yes. Exactly. I had tried few days back and it didn't seem to work..

[Shawn Dowler]

Thanks for the info. I shouldn't be too bad off if I run this nightly after midnight and just limit the query to "Yesterday." I suppose it's cleaner that way anyway.