Articles, Blogs, Videos, Podcasts, Share projects - Experiences from the field
Hi there,
Keeping your instance database footprint tidy? Reducing instance database footprint? An important subject, though actually a subject that barely gets attention. Barely if you search for any content, hardly any replies when reaching out on ServiceNow Community/LinkedIn/Slack/etcetera, and to be honest: also if you look at customers in general... so much focus on Development, so little (or almost no) focus on Platform or System Administration. Also there is not a single ServiceNow course that goes in depth on this subject.
So why is this such an important subject? ServiceNow runs in the cloud, right? Sure (unless you are on-prem of course), though that doesn't mean ServiceNow does everything for you! Keeping your instance database footprint tidy and reducing the database footprint, will have several positive effects: Performance gain, slimmer and optimized tables, less future manageability or technical depth, shorter system clone time (or time to place backups back in case of emergencies!), avoiding licensing implications if your database footprint gets over 4 TB, etcetera.
In the upcoming weeks, I will share several blogs regarding reducing your instance database footprint and keeping your instance database footprint tidy. All based on experiences gained in the field, at several customers, after collaborating with ServiceNow Support, investigating myself, etcetera.
If you have any thoughts yourself on this subject, don't hesitate to share!
Topics in this series of blogs (I will update this section when publishing new blogs):
- Maintain Audit Delete and Audit Relation
- Maintain Attachments and Attachment Documents
- Activating/adding Table cleaners
- Reduce (over)auditing
- Lower duration Table Rotation
- Drop syslog_trans_prejakarta* tables
- Cleanup Shadow tables
Audit
For most customers the Audit table [sys_audit] will be one of the biggest tables in their instance or simply the biggest table. Oke some customers added Table Extension on the Audit table, which might have you think that the table is not that big. Is a big table a bad thing? Not immediately. Though a big table might mean something regarding what was already mentioned in the introduction of this blog: performance, manageability, technical depth, or even licensing!
Just looking at size, it's not uncommon for the Audit table (or the sum of all Audit tables on Table Extension) to be several hundreds of Gigabytes or even more than a Terrabyte. For one customer I encountered that their Audit table (on Table Extension) was good for a total of 1.7 Terrabytes and 2.7 Billion records. After looking very closely at the Audit records and reducing the overauditing, we managed to bring the Audit table down to "only" 300 Gigabytes and 500 Million records.
Who is the Audit for
Regarding the Audit table, ask yourself who are the Audit records for? Are you auditing for Auditing regulations, Governing bodies, Instance debugging, Journal on records, Process Mining? Or ask yourself, was the Audit functionality on all the different tables within your ServiceNow instance (and their individual fields) purposely configured? Or is this more by accident, or because it's out-of-the-box a certain way, or perhaps because of an Implementation Accelerator?
What I'm experiencing out there, at every single customer I've ever been at so far, if at all, the Audit functionality was only purposely configured for a small number of tables and their fields within their ServiceNow instance. Knowing that, arguments like the Audit is for Auditing regulations can be thrown in a bin immediately!
I do want to stress, looking at the Audit functionality and if it's purposely configured isn't a one-time thing. New custom tables and fields are being created, new tables and fields are created through family upgrade releases, new tables and fields are created through Store plugins, etcetera. That all can have a significant impact on your Audit table.
How Audit works
Out-of-the-box several tables have Audit turned on. You can also turn on the Audit functionality yourself on existing tables or custom tables. An example would be turning on the Audit functionality on the Task SLA table [task_sla] or the User table [sys_user], I see this at multiple customers. The most common way to enable or disable the Audit functionality is on Dictionary Entry level, by having the Audit field on the Collection Dictionary Entry of a table marked as true or false. Enabling the Audit functionality this way, does enable it on every single field on the specific table. This will also be the case for newly added (custom) fields, they will automatically generate new Audit records.
When the Audit functionality is enabled, every single change made will be stored as a single record in the Audit table. For any following changes new records will be stored in the Audit table. The specific Audit records would amongst others hold the old value, the new value, and the user triggering the change for the Audited record concerned.
The above is just a very brief explanation of how the Audit functionality works. Want to know more? Have a look at the ServiceNow Product Documentation:
- Explore Auditing
Specific Fields to include/exclude to/from Audit
With the Istanbul release ServiceNow introduced the possibility of including/excluding fields to/from the Audit for a table. Basically exclude a field from being audited or include a table field in auditing.
When wanting to exclude specific fields from Audit for a table, you would open the Dictionary Entry of the specific Field and add an Attribute "no_audit". When wanting to include specific Fields to Audit for a Table, you would add to the Collection Dictionary Entry of the table an Attribute "audit_type=whitelist" AND open the Dictionary Entry of the specific field and add an Attribute "audit=true".
The above is just a very brief explanation of how configuring Audit for a table works. Want to know more? Have a look at the ServiceNow Product Documentation:
- Configuring auditing for a table
Out-of-the-box Audit
What exactly is Audited on an instance depends on a variety of things. Amongst them the family upgrade release, what plugins are installed, and Store Application versions.
Spinning up a fresh out-of-the-box Vancouver instance (Patch 0):
- 5,094 Tables on the instance
- 1,123 Tables have the Audit functionality enabled
- 3 Tables have inclusion for Audit set (audit_type=whitelist)
Some interesting bits of the out-of-the-box Setup:
- Audit for both the User [sys_user] and Task SLA [task_sla] Tables is not enabled out-of-the-box, something you do see at a lot of customers
- From the 902 Tables that contain "cmdb", 609 have Audit enabled and 293 have no Audit enabled
- Opening the Audit table there are 20,580 left-over Audit records which are just garbage
- Removing Demo Data would generate 19,775 Audit records which should be considered for cleaning
Overauditing
CMDB
Out-of-the-box there are 902 Tables containing "cmdb". 609 of these have the Audit functionality enabled and 293 don't have this enabled. There's no good explanation available about the why behind the difference in Auditing specific CMDB tables or not, and looking in-depth there are several tables that serve similar purposes where some are and some aren't Audited. What I'm experiencing out there, is that the Audit functionality was only purposely configured for a small number of CMDB tables. So far I've seen no customer at all where Audit on specific CMDB fields was purposely configured.
When just looking at numbers, also tables containing "cmdb" will contribute massively to a customers full Audit table. For one customer I encountered "cmdb" contributing more than 700 Million records to their full Audit table.
Audit on CMDB can definitely be valuable, especially on manual created and/or updated records. Though what about automatically created/updated/deleted records, automatically imported records, records that are fully maintained through Discovery, etcetera. Would all such records actually need Auditing or can you reduce your Audit on CMDB? The answer is simply yes, almost every customer out there can reduce the Audit functionality on CMDB.
To give a specific CMDB example. For one customer I noticed a lot of Audit records for IP Address [cmdb_ci_ip_addres]. Basically all of the IP Addresses were fully maintained through Discovery. The creation, the updates, the deletes. Looking at its size, IP Address were generating 20 Million records monthly in the Audit table (almost 30% of all monthly Audit for that customer!). After discussing Audit on IP Address with the customer, quickly it was agreed that this was unnecessary and did not hold any value for them.
Just turning off Audit for IP Address only is not enough though. That would prevent Audit on IP Address, though not yet on possible CI Relationships [cmdb_rel_ci] or Affected CIs [task_ci] records. I haven't found a sweet solution for this, so if you know of one, feel free to share.
Task SLA
Out-of-the-box, the Task SLA Table is not Audited. For the majority of customers I've been at, I did see that the Audit functionality was turned on for this table. I can imagine doing so for specific cases. Though in most cases, this just was done as part of an implementation and applying an accelerator or best practice package of the implementation partner.
For one customer I encountered the Task SLA table contributing more than 600 Million records to their full Audit table. Asking why the customer would need this... they didn't! 600 Million of unnecessary records, records you will come across maintaining your instance, records that will decrease the performance of the table, records that will contribute to over-licensing on the Database Footprint size.
Audit specific fields
When working with the Audit functionality, the most common I see out there is simply enabling Audit on a table. With that, automatically enabling Audit on every single field on the table concerned. If you would enable the Audit functionality for example on User, all 66 fields would generate new Audit records on every update of each field. Is that really useful for all of the fields? Short answer: No, that's not useful 🙂
Same goes for tables where out-of-the-box the Audit functionality is already enabled. For example Incident, and it's 98 fields. Does every field need to create new Audit records? Fields that are perhaps automatically maintained? Fields which are deprecated(...)? Fields that are not in use at your company though which do generate data? Fields that hold a default value inherited from the parent table though are actually not in use on Incident(...)? Fields that are not reported on/cannot be manually edited and hold no value for regulations/reporting/process mining/debugging?
Frequently updated fields
Out-of-the-box some tables have fields that are updated a massive amount of times. User for example, has fields like Last login [last_login], Last login time [last_login_time], Last login device [last_login_device]. Or it's not uncommon to see custom fields on CMDB, regarding "Last discovery date", "Last seen in AD" or something similar. Fields like these can impact the number of Audit records massively. Consider if there is any value in Auditing fields like these. Some definitely might have value, though a lot don't. This does not only count for new custom fields, also for out-of-the-box fields like for example the Last login ones that I mentioned. For example, analyzing your Audit records for "tablename sys_user and fieldname contains last_", don't be surprised if this is already near (or even way over) 50% of all the Audit records for the User table. And that's only for 3 out of the 66 out-of-the-box fields on the Incident table!
Or ask yourself for fields like Last login and Last login time on the User table, if there isn't already different out-of-the-box functionality to keep track of this, like the ISC Events table. Why would you keep track of this multiple times on your instance?
Audit for Deleted records
When disabling Audit on a table, there actually might still Audit be generated for that table, Audit regarding deletions. Keeping track of deletions can be useful, though as already mentioned regarding Audit, often this is not configured purposely. Also out-of-the-box how this is configured is all over the place. There's no good explanation available about the why behind out-of-the-box some tables do have the "No audit delete" attribute added and some tables don't.
Audit for Deleted records is also a huge contributor to the total Audit and one that you should definitely configure purposely. Because out-of-the-box Audit for Deleted records might be generated that most likely won't have any value for your company to keep track of. Though also keep track off Audit for Deleted records while performing family release upgrades or activating plugins. New tables might be introduced, tables where Audit is not turned on, though Audit for Deleted records also has not been turned off. Or ServiceNow might turn the Audit for Deleted records for some tables off after a few releases, which is nice when ServiceNow "solves" this out-of-the-box... though the already generated Audit for Deleted records will still be on your instance, ServiceNow won't clean that up for you! For several customers I've seen situations where there were hundreds of millions of such records (even one case where there were more than 400 million records regarding the Audit Results table [cert_audit_result], which has no Audit turned on, though Audit for Deleted records for this table is being generated).
You can disable keeping track of Deleted records in the Audit by adding an Attribute "no_audit_delete=true" on the table. Want to know more? Have a look at the ServiceNow Knowledge Base:
- Disable auditing of deleted records on a specific table
Rebuild Audit Table
After having the Audit table cleaned up, you do need to have the table optimized. ServiceNow Support can do this for you through a Support Case. They will optimize the table from the backend, and would likely opt to actually rebuild the table. Having the table optimized would improve performance on the Audit table and lower the Database Footprint size.
If your Audit table is on Table extension, you will have tens or maybe even more than a hundred sys_audit0* tables. When requesting ServiceNow Support to optimize those tables, then request for example 15 tables at the time. When requesting all sys_audit0* tables having optimized in one go... the Change Request will be rejected 😀.
Also you might need to push the ServiceNow Support engineer to have the tables optimized. It's not uncommon to get a response that the gain in GB is very limited and might not be worth it. Though from your point of view: it's about the sum of all sys_audit0* tables that matters, it's about performance gain, and lowering the Database Footprint gigabytes might be needed regarding licensing.
Result
Looking into (over)Auditing is a task that costs a lot of time and is not an easy task. At the same time, poor performance on tables costs valuable time of your end users and developers as well, and over licensing on your Database Footprint is also not cheap 😀.
Especially when your Audit table is a bit larger, the possible gains will be massive. As mentioned for one customer I encountered that their Audit table was good for a total of 1.7 Terrabytes and 2.7 Billion records. After looking very closely at the Audit records and reducing the overauditing, we managed to bring the Audit table down to "only" 300 Gigabytes and 500 Million records.
---
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
4x ServiceNow Developer MVP
4x ServiceNow Community MVP
---
3 Comments
