How to find the top 10 threat events and purge events

Technical Articles ID: KB68961
Last Modified: 2023-03-21 04:44:19 Etc/GMT

Environment

ePolicy Orchestrator (ePO) 5.x

Microsoft SQL Server Express

For details of ePO and SQL-supported environments, see KB51569 - Supported platforms for ePolicy Orchestrator.

Problem

The Microsoft SQL database has reached the allowed maximum size. There isn't enough space remaining to write new data or temporary data needed to perform certain functions.

The database often reaches a practical limit at just under the stated size limit for the SQL Server.

The following limits apply:

10 GB for SQL Server 2008 R2 Express and SQL Server 2012 Express
4 GB for SQL Server 2008 Express

Solution

Contents
Click to expand the section you want to view:

Find the ten most common threat events

The following query helps determine which events are using the most space in the ePO database.

Use SQL Server Management Studio to open a query window, and select the primary ePO database.
Related article: KB67591 - How to run a SQL script provided by Technical Support against the ePolicy Orchestrator database

IMPORTANT: If you're using ePO 5.10, do not select the events database. The script does not work properly unless it's run against the primary database.

Paste the following SQL statement into the query window:

select top 10
Count(e.ThreatEventID) as [Count], e.ThreatEventID as EventID,
f.[Description] as 'Event Description', e.AnalyzerName as 'Event Source'
from EPOEvents e (nolock) left join EPOEventFilterDesc (nolock) f
on e.ThreatEventID = f.EventId where f.[Language] = '0409'
group by e.ThreatEventID, f.[Description], e.AnalyzerName
order by [Count] desc

Click Execute or press F5.

NOTES:

If you want to stop generating one or more of these events, you can deselect the events in the ePO console. To complete the task, go to Menu, Configuration, Server Settings, Event Filtering.
The query returns the top ten threat event IDs that have occurred the most frequently in your ePOEvents table. If your ePOEvents table is taking up too much space on your SQL Server, purge them by moving on to the next section.
You might also need to consider how to reduce the occurrence of these events. For assistance, contact the product team that generates the events. They can provide tips on how you can configure the product to reduce these events.

You might decide that you don't want any of these events forwarded to ePO. If you want to stop generating one or more of these events, follow these instructions:

Log on to the ePO console.
Go to Menu, Configuration, Server Settings, Event Filtering.
Click Edit.
Deselect the event IDs that you no longer want to have forwarded to ePO.
Click Save.

To purge threat events from the ePO database based on the event ID

Based on the output of the query above, you can purge individual events from the ePO database based on the event ID.

NOTE: If you need to purge many events or if you need to purge based on criteria other than the Event ID, see KB92098 - How to purge large amounts of event data from ePolicy Orchestrator using an SQL query.

Use SQL Server Management Studio to open a query window, and select the primary ePO database.
Related article: KB67591 - How to run a SQL script provided by Technical Support against the ePolicy Orchestrator database

IMPORTANT: If you're using ePO 5.10, do not select the events database. The script does not work properly unless it's run against the primary database.
Paste the following SQL statement into the query window:

declare @EventID int = <InsertEventIDHere>;
declare @Is510 bit; declare @rowcount int = 1;
if OBJECT_ID('tempdb.dbo.#PurgeEvents', 'U') IS NOT NULL drop table #PurgeEvents;
create table #PurgeEvents (AutoID bigint);
if exists (select 1 from sys.objects where [name] = 'EPOEventsMT' and type = 'SN')
set @Is510 = 1; else set @Is510 = 0;
while (@rowcount > 0)
begin
begin transaction;
delete top(4900) EPOEvents output deleted.AutoID into #PurgeEvents where ThreatEventID = @EventID;
set @rowcount = @@ROWCOUNT;
if (@Is510 = 1) delete A from EPOEventsReference A join #PurgeEvents B ON A.AutoID = B.AutoID;
truncate table #PurgeEvents;
commit transaction;
if @rowcount > 0 waitfor delay '00:00:05';
end
drop table #PurgeEvents;
Update the first line of the script and reference the event ID that you want to purge. For example, if you want to purge all instances of Event ID 1095, the first line would look like this:

declare @EventID int = 1095;
Click Execute or press F5.

Shrinking the ePO database

Normally a shrink operation on the ePO database isn't needed. Consider the following before proceeding:

Shrinking the ePO database isn't 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 in the following scenarios:

If your database has grown unusually large for a reason, which you have since corrected.
You need to do a one-time shrink operation to get the database back down to its normal size.

Use SQL Server Management Studio to open a query window, and select the primary ePO database.
Related article: KB67591 - How to run a SQL script provided by Technical Support against the ePolicy Orchestrator database
Paste the following SQL statement into the query window:

WARNING: The SQL transaction log (.ldf file) 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

Click Execute or press F5.

Related Information

For more information, see the following related links:

How to use the command DBCC SHRINKFILE
How to use the command sp_spaceused

Affected Products

Languages:

This article is available in the following languages:

Knowledge Center

How to find the top 10 threat events and purge events

Environment

Problem

Solution

Related Information

Affected Products

Languages:

Title

Title

Knowledge Center

How to find the top 10 threat events and purge events

Environment

Problem

Solution

Related Information

Affected Products

Languages:

Choose your region

Title

Title