Pro Tip – StealthINTERCEPT DB Maintenance Best Practices

Pro Tip – StealthINTERCEPT DB Maintenance Best Practices

It is important to monitor the size of the NVMonitorData SQL database that is used by StealthINTERCEPT (SI) to store the event data it collects. In production environments, the event dataset can grow significantly over time.  If left unchecked this DB growth will lead to excessive disk space usage and slowing performance over time inserting new event data.  In addition, users can encounter slow performance reporting data via either the SI Console or the Web Reporting modules.

To assist with managing the data stored in the SQL DB the StealthINTERCEPT product includes a “DB maintenance” system to regularly groom the DB content.  It supports a two-tier storage model.  Tier one or the ‘production’ DB is the SQL DB specified when the SI product was installed.  This is where all data collected in real-time is stored and used for reporting including “Investigate”, “recent events” and “Web reports”.  An optional tier two DB called the “Archive DB” can be defined in the Database Maintenance dialog “Archive DB” tab.  In the SI Console ‘Investigate’ panel users can toggle between the “Production” and “Archive” databases when searching for event data.

The “Database Maintenance” dialog accessed from SI Console Configuration | Database | Maintenance menu is s GUI front end.  It configures a SQL Server jog via the SQL Server Agent to run a series of stored procedures either deletes or moves SI event records based on the age of the event and is run at a specified time either once, daily, weekly or monthly.

Our recommended best practices include the following:

  • Configure and start using DB Maintenance soon after initial product deployment so the size of the DB does not get out of control.
  • Archive DB tab is last as it does not change often but may be the first to configure when starting out.  If an Archive DB is configured then the other tabs will have options bot both ‘move’ and ‘delete’.  With no Archive DB the options will be ‘delete’ only.  We recommend moving events to Archive DB based on age such that the events would no longer need to be accessed via the SI Console ‘Recent Events’ panel.  They can still be accessed via the “Investigate” panel by toggling the radio button from “Production” to “archive”.
Database Maintenance Investigate Panel
  • Using the ‘Schedule’ tab we recommend running ‘Daily’ during off peak business hours.  By running daily only one days’ worth of event data that is older than the specified ages will need to be moved or deleted.  This helps ensure the maintenance job can complete in a timely fashion each night before the next day’s activity competes for DB resources.  In some environments ‘Weekly’ runs may preferred but if doing so be sure the job starts early enough to complete well before the start of the next work week.
  • Refer to DB Maintenance alerts in the SI Console Alerts panel – this provides very useful information about when DB Maintenance starts and stops as well as the number of each type of events moved or deleted.  Referencing the time stamps makes it easy to calculate how much time the overall job needed to run as well as the time needed to process for each event and policy type.  Be sure to check this from time to time.  Changes to polices or policy filters can have a large impact on the volume of events collected and thus eventually to be removed.
  • Policy vs Event Type tab – The Policy tab allows defining fine-grain data retention times on a per-policy basis.  We suggest setting this time to the minimum value the organization needs to retain the data according to the policy for immediate lookup – leverage the archive DB for longer-term secondary storage.  For example, it is likely to want to keep events for something like changes to group membership than say failed authentications. The later probably has little value after a few months.  Note that clicking on the “Event Count” column will sort by these values which may assist in deciding retention times.
  • Event Type – we suggest setting this as a ‘backup’ to settings in the Policy tab with retention times longer than for your policies.   In this way any polices not picked in the Policy tab or added to the system after Maintenance setup but not update in Maintenance will still have their old data removed.  When Maintenance is run the rules for each tab are processed independently so don’t set s shorter time in “Event Type” than in “Policy” else the more general delete/move by type will remove data sooner than the time in the more specific by policy setting.
  • Analytics event data is stored in separate tables from policies so this operates independently of any settings in the “Event Type” and “Policy” tabs.

Archive DB management

It is currently up to the customer to manage content in the Archive DB to limit size.  One approach is to periodically delete all events older than a specified date using native SQL queries.

Another is to periodically (i.e. yearly) create and point the SI DB Maintenance feature to a new Archive DB.  Keep the ‘old’ Archive DB as is but ‘offline’ relative to SI.  If you need to later get at data in one of these ‘old’ DB’s it can be done in one of three ways:

  1. In the SI DB Maintenance Archive DB tab change the DB values to temporally point to the ‘old’ database needed. Then use the SI Console “Investigate” panel with “Archive” selected to search for the needed events.  Note: Don’t leave it this way when a scheduled DB Maintenance job will run as event data will then be added to this ‘old’ Archive DB which is probably not the wanted behavior.
  2. Install on a standalone machine another copy of the SI Server + Console (don’t deploy agents from or point agents to this 2nds copy).  Put a static copy of the ‘production’ SI Configuration DB on this machine (so it knows what policies exist). Point DB Maintenance Archive Tab to the desired ‘old’ archive DB.  Don’t configure the DB Maintenance Schedule tab.  Now can use the SI Console Investigate panel to ‘query’ this DB indefinitely without concerns about ‘new’ data being moved to this ‘old’ archive dataset.
  3. Use your own SQL queries to retrieve event data from any SI Event DB (archive or production)

What if I never used DB Maintenance and my DB is so huge that DB Maintenance will not complete?

Assuming you don’t want to drop the DB and start over… If you never used DB Maintenance but want to after the production DB has become huge (i.e. many TB) you may find that attempts to move or delete events can run for days or even never finish.  In this case, one may have to ‘ease’ into it.  By that, we mean to configure the type tab picking every type and set to retain data at first for only a few days less than your oldest event.  For example, if you oldest event data is 700 days then set ‘retention period’ to 695 days.  Then run DB Maintenance either that night or ‘now’.  This first run is more about verifying the job runs w/o errors than deleting any real amount of old data.  Take note of how long the job runs (look at DB Maintenance start/end alerts in SI Console).  If all ran well then try setting ‘retention period’ to 10 days less than the now oldest event and repeat looking at total job run time.  Find a ‘retention period’ value relative to the now oldest event that takes less than say 6 hours to complete. Repeat until you are able to get down to the desired “retention Period”.  Then set DB Maintenance to run nightly so thereafter you never have events older than your ‘longest’ retention period value by Type or policy left in the production DB.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Start a Free Stealthbits Trial!

No risk. No obligation.