Running Offline Event Pruning

Products

• Carbon Black App Control

Issue/Introduction

Steps to execute the Offline Pruning for the dbo.events table when an excessive number (billions) of Events exist.

Environment

• App Control Server: All Supported Versions
• Microsoft SQL Server: All Supported Versions

Cause

Typically these steps are not required and should only be followed as directed by Support when both

• SQL Server is underperforming and
• An excessive number (over 1 billion) of Events exist.

Resolution

ATTENTION: Typically these steps are only required when both SQL Server is underperforming and An excessive number (over 1 billion) of Events exist. These steps are not required under normal circumstances. This process will retain only the Events that meet the criteria in Settings > System > Events Any Event older than Delete Events Older Than will not be retained. Newest Events (based on Date Received) are retained first. Any Event (regardless of age) over Delete If More Than will not be retained. Before continuing, it is recommended to revert the Event Retention Settings to defaults. Delete Events Older Than: 4 weeks Delete If More Than: 10000000 Events (10 million) On Limit, Delete Oldest: 10% of Events

 

1. Before proceeding with the steps
   • Open a case with Support and request the relevant Offline Pruning Script for the table/Server being pruned.
   • Plan for a maintenance window while the Pruning Script executes.
     • The time for this window will vary on the number of Events to retain and the underlying SQL Server performance.
     • Typically we suggest planning for a 24-hour maintenance window, but it may complete much faster.
   • Take a known-good, full backup of the DAS database.
2. Prepare for Offline Pruning Execution
   1. Review the current Event Retention Settings in the Console > Settings > System > Events
      • Returning these settings to defaults is encouraged to reclaim the most space and complete the fastest.
   2. Log in to the application server hosting the Console as the Carbon Black Service Account.
   3. If an Agent is installed on the application server, temporarily stop and unload the Agent to prevent Tamper Protection blocks.
   4. Stop (or temporarily disable) the services for the App Control Reporter and App Control Server.
   5. Any external application that accesses the das database should be temporarily stopped.
      • This includes things like the SQL Job Agent, backups, reporting, etcetera.
      • The SQL Server service itself should remain running, however.
3. Initiate the Offline Pruning
   1. Extract the OfflineEventPruning.zip locally (ex: C:\Temp\)
   2. Double click the RunOfflinePruning.bat (Do not use "Run as Administrator")
   3. Input the SQL Server\SQL Instance name
      • Single Tier (local database): Enter a single dot  .
      • Two Tier (remote database): Enter the SQLServer\SQLInstance name, example: SomeSQL\SomeInstance
   4. Wait for Connection Tests to complete
   5. Once successful, and prompted, enter Y to proceed.
   6. IMPORTANT: Do not interrupt the process or access the das database during this time.
      • Doing so may corrupt the events table (or related tables) requiring a database restore.
4. Reclaim space, if necessary, by completing the Offline Shrink process.
5. Start services for the App Control Server and App Control Reporter.
   1. Verify the Console is accessible and Agents are beginning to reconnect.
   2. Restart the App Control Agent (if stopped) and any other SQL tasks, jobs, etc.

Additional Information

• Total Events in Console Less Than Expected