Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field
Hi there,
For every customer the Sys Audit is typically one of the largest tables in their production instance. A good chunk of the Sys Audit records, concern deletions logged. For some customers I have seen this contributing up to more than 30% of their total Sys Audit table. While the deletions logged can be very useful for auditing and compliance, troubleshooting, and security, for several tables deletions are logged for no good reason or you simply cannot do anything with it.
Some of this I did share before in a blog on "Reduce (over)Auditing", though there's more! Always watch your Sys Audit when applying a Patch or Release.
Sys Audit DELETED records
A thing to watch out for regarding the Sys Audit table, deletions recorded which would be visible as "fieldname=DELETED", "oldvalue=DELETED", or "newvalue=DELETED". Such records are also generated for tables that don't have audit enabled. If a user deletes a Task SLA record (out-of-the-box "audit=false"), still a deletion will be logged in the Sys Audit table. In certain cases this is very useful, though in cases where tables are automatically provisioned, maintained, and deleted this can be an overkill. Or what about system related tables, where you wouldn't even be aware of that high frequent deletions occur, that can run in hundreds of millions of records.
New tables
With every Patch, Family release, or Store release, new tables are added to ServiceNow instances. Judge yourself if these tables actually require to have audit enabled and if the "No Audit Delete" Dictionary Entry attribute needs to be added when not already in place. You don't immediately have to do this during the Family release (which would make an Upgrade just a bit bigger again), you could also do such one or two sprints after the release in an Agile way of working. That way you are pretty quickly on top of reducing overauditing and cleaning any generated Sys Audit records up would be a minor task at that moment.
Ofcourse, be aware that adding the No Audit Delete Dictionary Entry attribute will reduce the volume of new Sys Audit records being created, though this does not touch possible existing Sys Audit records. Already existing Sys Audit records you would need to cleanup yourself.
New dictionary entries for No Audit Delete
Similar like new tables, with every Patch, Family release, or Store release, new No Audit Delete Dictionary Entry attributes can be added to a ServiceNow instance. This is never part of Release notes, so something you need to track down yourself. If you look at your last Family release upgrade, whether it was to Vancouver or Washington DC, you should see new Dictionary Entries for No Audit Delete added to your instance. Navigate to the Atrributes table [sys_schema_attribute_m2m] and filter on Attribute is No Audit Delete, Value is true and Created same as the date when you upgraded.
For example see this screenshot for one of my customers who performed their Washington DC release last week. 57 times No Audit Delete has been newly added to Dictionary Entries. Ofcourse a part of this belongs to newly created tables, though some also for already existing tables.
ServiceNow can add the No Audit Delete attribute to tables at any time. Sometimes this will be because they've realized that logging deletions for a certain table is not useful though does generate a lot of Sys Audit 😀. Nice of ServiceNow, though be aware... already generated Sys Audit records are not cleaned up. That's up to the customer, you!
Data cleanup
What you for example can do to cleanup deletions logged in your Sys Audit table, run a script in the background that would query all No Audit Delete attributes and then query the Sys Audit for any existing records and delete those. I prefer to do this in the background (and instead of instantly deleting, first doing a count and confirming the result), because of the size of the Sys Audit table at most customers. Size that causes the Sys Audit table taking a huge amount of time to manually open or just time-out.
For some of the newly added No Audit Delete attributes to Dictionary Entries this might just be a few hundred or a few thousands of records and barely worth the effort. Though I also have seen cases at customers where there were more than 600 million Sys Audit records for deletions logged while the tables this was for did have the No Audit Delete attribute added at some moment in time. Added by ServiceNow or the customer themselves. 600 million records 🙄.
Optimize table
After having the Sys Audit table cleaned up from unnecessary deletions, you do need to have the table optimized. This will increase the performance of the Sys Audit table and will reclaim unused space on your Database Footprint. For more on this read this blog I wrote a few months ago:
- Reclaiming Table Free Space
---
That's it. Hope you like it. If any questions or remarks, let me know!
| C |
If this content helped you, I would appreciate it if you hit bookmark or mark it as helpful.
Interested in more Articles, Blogs, Videos, Podcasts, Share projects I shared/participated in? |
Kind regards,
Mark Roethof
Independent ServiceNow Consultant
9x ServiceNow MVP
---
