How to purge data from the ePO database using the ePO console

Technical Articles ID:   KB79561
Last Modified:  6/25/2020

Environment

McAfee ePolicy Orchestrator (ePO) 5.x

Summary

If your ePO database is large and needs to be reduced in size, determine which tables need to have some data purged.

The easiest way to determine which are the largest tables is to look in the ePO consoleconsoleconsoleA physical or virtual terminal attached to an appliance that is used to monitor and control an appliance.console:
  1. Log on to the ePO consoleconsoleconsoleA physical or virtual terminal attached to an appliance that is used to monitor and control an appliance.console.
  2. Navigate to Menu, ConfigurationServer Settings.
  3. Select Server Information.
  4. Scroll down, and locate the section labeled, Largest Tables.
The Largest Tables section of the Server Information page displays the top 20 largest tables in the ePO database in desending order. When you know which tables have the most data, you can use the appropriate solution below.

Prepare the SQL Server:
Before implementing any of the solutions below, or making an ePO upgrade, you might need to prepare SQL. If your company has a database administrator (DBA) who manages the SQL Server, DBA involvement is likely needed for the following steps:
  1. If you changed the database to Full Recovery Mode (default is Simple), perform a backup first. Then set it to Simple Recovery Mode. This option cleans the transaction log, and limits the growth of the transaction log size.
  2. Make sure the disk volume that contains the transaction log has extra free space. The amount of free space needs to be greater than the size of the ePO database MDF file. If that is not possible, make sure it has enough space to hold the largest of the tables listed above.
  3. Make sure:
    • The transaction log is set to Auto-grow.
    • The disk volume that contains the ePO database MDF file has significant free space for duplicating your largest tables.
    • That you have set aside significant time for the upgrade to complete. It could take hours or potentially days, depending on the size of the tables.

Problem

This problem statement only applies if you are upgrading from ePO 5.3.3 to ePO 5.9.0 or later. After you have upgraded to ePO 5.9.0 or later, the ePO DB schema has been migrated. Subsequent upgrades do not require the tables in question to be updated.

A pre-upgrade check during the ePO 5.x upgrade displays a warning message similar to the following:

The SQL database needs more free space or setup will fail.

It needs two times the current database size to continue.


The ePO 5.x installation makes a schema change on these tables. This change could take up significant more disk space on the SQL Server, and migrating this data could take a long time to complete.

The ePO 5.x installer looks at the size of unbounded tables in the ePO database.

When you upgrade from the 5.x version, it verifies only a subset of the following tables. But, when you upgrade from earlier 4.x versions, it includes checks for all following tables:
  • EPOEvents
  • EPOProductEvents
  • EPORollup_Events
  • EPORollup_ProductEvents
  • OrionSchedulerTaskLogDetail
  • OrionAuditLog
NOTES:
  • If the ePO installer changes any of the tables listed above, and they have over one million rows, a warning message is displayed before the installation starts.
  • The SQL Server needs significant reserve disk space to apply the schema changes to these tables.
  • The changes take significant time to complete, which causes the ePO upgrade to potentially run for many hours or fail.
IMPORTANT: Create a full backup of the ePO server and database as directed in the release notes.

The time and space needed are directly proportional to the number of rows in the table.

Supporting data:
The following data comes from our internal testing of the ePO 5.x table schema upgrade.

SQL Server specifications:
  • Single SATA 2 spindle
  • 4 Core 2.8 GHz Xeon
  • LDF and MDF on the same disk. The MDF file is the primary file in SQL Server database. The LDF is a supporting file.
Test Table Row
Count
(
million)
Time MDF
Growth
LDF
Growth
Large Client Event count. Modify Identity column while preserving time stamps. EPOProductEvents 60 2 hours 15 minutes 15 GB 36 GB
Large AuditAuditAuditA method of collecting and storing information that can be used to track system activity, such as authentication attempts, configuration modifications, and stopping and starting of services.Audit Log count. Addition of int column with default constraint. OrionAuditLog 14.5 23 minutes 5 GB 5 GB

Let the installation continue after the warning:
  • When you have performed the steps above
    And
  • Have adequate disk space on the SQL Server for the upgrade to occur

Solution

How to purge the threat event log
This solution applies if you need to remove data from the following tables:
  • ATD_Events
  • EPOEvents
  • EPCertEventMT
  • EPExtendedEventMT
  • HIP8_EventInfo
  • HIP8_IPSEventParameter
  • JTIClientEventInfo
  • VSECustomEvent
  • WP_EventInfoMT
You have many options for selecting which events to purge. Here are instructions on how to purge events older than a specified time frame:
  1. Log on to the ePO consoleconsoleconsoleA physical or virtual terminal attached to an appliance that is used to monitor and control an appliance.console.
  2. Navigate to Menu, Automation, Server Tasks.
  3. Click New Task.
  4. Give the task a name, and click Next. For example, you might name the task Threat Events Purge.
  5. Select Purge Threat Event Log from the Actions drop-down list.
  6. Enter information in the Purge records older than radial selection. Which conforms with your companies data retention policypolicypolicyA set of rules that govern communications.policy. If you don't have one, try using 90 days.
  7. Click Next.
  8. Schedule the task to run regularly. For example, once a day at a nonpeak time such as 1 a.m. often works well.
  9. Click Next then Save.
  10. Click Run next to the task you created.

Solution

How to purge the server task log
This solution below applies when you need to remove data from the following tables:
  • OrionSchedulerTaskLog
  • OrionSchedulerTaskLogDetail
  • EPOCoreLogMessage
  • EPORepositoryLogMessage
To purge server task log entries older than a specified time frame:
  1. Log on to the ePO consoleconsoleconsoleA physical or virtual terminal attached to an appliance that is used to monitor and control an appliance.console
  2. Navigate to Menu, Automation, Server Tasks.
  3. Click New Task.
  4. Give the task a name and click Next. For example, you might name the task Server Task Log Purge.
  5. Select Purge Server Task Log from the Actions drop-down list.
  6. Enter information in the Purge records older than radial selection which conforms with your companies data retention policypolicypolicyA set of rules that govern communications.policy. If you don't have one, try using 90 days.
  7. Click Next.
  8. Schedule the task to run regularly. For example, once a day at a nonpeak time such as 1 a.m. often works well.
  9. Click Next, then Save.
  10. Click Run next to the task you created.

Solution

How to purge the auditauditauditA method of collecting and storing information that can be used to track system activity, such as authentication attempts, configuration modifications, and stopping and starting of services.audit log
This solution applies when you need to remove data from the  OrionAuditLog  table.

Here are instructions on how to purge server task log entries older than a specified time frame:
  1. Log on to the ePO consoleconsoleconsoleA physical or virtual terminal attached to an appliance that is used to monitor and control an appliance.console.
  2. Navigate to Menu, Automation, Server Tasks.
  3. Click New Task.
  4. Give the task a name and click Next. For example, you might name the task AuditAuditAuditA method of collecting and storing information that can be used to track system activity, such as authentication attempts, configuration modifications, and stopping and starting of services.Audit Log Purge.
  5. Select Purge AuditAuditAuditA method of collecting and storing information that can be used to track system activity, such as authentication attempts, configuration modifications, and stopping and starting of services.Audit Log from the Actions drop-down list.
  6. Enter information in the Purge records older than radial selection which conforms with your companies data retention policypolicypolicyA set of rules that govern communications.policy. If you don't have one, try using 90 days.
  7. Click Next.
  8. Schedule the task to run regularly. For example, once a day at a nonpeak time such as 1 a.m. often works well.
  9. Click Next, then Save
  10. Click Run next to the task you created.

Solution

How to purge Product Events
This solution applies when you need to remove data from the following tables:
  • EPOProductEventsMT
  • EPEEventParameters
To purge product events older than a specified time frame:
  1. Log on to the ePO consoleconsoleconsoleA physical or virtual terminal attached to an appliance that is used to monitor and control an appliance.console.
  2. Navigate to Menu, Automation, Server Tasks.
  3. Click New Task.
  4. Give the task a name, and click Next. For example, you might name the task Purge Client Events.
  5. Select Purge Client Events from the Actions drop-down list.
  6. Enter information in the Purge records older than radial selection which conforms with your companies data retention policypolicypolicyA set of rules that govern communications.policy. If you don't have one, try using 90 days.
  7. Click Next.
  8. Schedule the task to run regularly. For example, once a day at a nonpeak time such as 1 a.m. often works well.
  9. Click Next, then Save.
  10. Click Run next to the task you just created to execute it.

Solution

How to purge rollup data
This solution applies when you need to remove data from the following tables:
  • EPORollup_Events
  • EPORollup_ProductEvents
To roll up events older than a specified time frame:
  1. Log on to the ePO consoleconsoleconsoleA physical or virtual terminal attached to an appliance that is used to monitor and control an appliance.console.
  2. Navigate to Menu, Automation, Server Tasks.
  3. Click New Task.
  4. Give the task a name and click Next. For example, you might name the task Rollup Events Purge. 
  5. Select Purge Rolled-up Data from the Actions drop-down list.
  6. Select Threat Events from the Data Type drop-down list.
  7. Enter information in the Purge records older than radial selection which conforms with your companies data retention policypolicypolicyA set of rules that govern communications.policy. If you don't have one, try using 90 days. 
  8. Click the + sign on the right side of the Actions line.
  9. Select Purge Rolled-up Data from the new Actions drop-down list.
  10. Select Client Events from the Data Type drop-down list.
  11. Enter information in the Purge records older than radial selection which conforms with your companies data retention policypolicypolicyA set of rules that govern communications.policy. If you don't have one, try using 90 days.
  12. Click Next.
  13. Schedule the task to run regularly. For example, once a day at a nonpeak time such as 1 a.m. often works well. 
  14. Click Next, then Save
  15. Click Run next to the task you just created to execute it.

Solution

Shrinking the database
It is important to note that purging data does not actually reduce the database size on disk. To complete that task, you must run the shrink command against the database. Normally a shrink operation on the ePO database is not needed. Consider the following before proceeding:
  • Shrinking the ePO database is not recommended or needed for ePO to function.
  • Shrinking the database files ( .MDF ) and ( .NDF ) can increase index fragmentation and cause queries to run slowly.
  • After you shrink the database files, if the database needs to expand to accommodate new data, the SQL Server locks the files during the growth. The result can be performance issues with the application that uses the database while the new data is inserted.
Use the following instructions if:
  • Your database has grown unusually large for a reason which you have since corrected.
    And
  • You need to do a one-time shrink operation to get the database back down to normal size.
  1. Open SQL Server Management Studio.
  2. Open a queryqueryquery
    The tabular or graphical report results of a customized search of the database records of a report definition’s Log Source or Log Sources. A report query is generated by a query definition. Each report definition includes one or more query definitions.
    query window and select the database you are trying to shrink. For detailed instructions, see KB67591.
    NOTE: Most tables reside in the primary ePO database, but in ePO 5.10 the ePOEvents table (only) is located in the events database.
     
  3. Paste the following SQL statement into the queryqueryquery
    The tabular or graphical report results of a customized search of the database records of a report definition’s Log Source or Log Sources. A report query is generated by a query definition. Each report definition includes one or more query definitions.
    query window:

    WARNING: The SQL transaction log ( .LDF ) can grow up to five times the size of the database while running the command. Make sure that you have sufficient free disk space before you use this command.

    dbcc shrinkdatabase ('ePO_DatabaseName')
    go

  4. Click Execute, or press F5.

Glossary of Technical Terms


 Highlight Glossary Terms

Please take a moment to browse our Glossary of Technical Terms.