Loading...

Knowledge Center


All ePolicy Orchestrator events fail to parse and eventually get stuck in the Events folder
Technical Articles ID:   KB86011
Last Modified:  4/3/2019
Rated:


Environment

McAfee ePolicy Orchestrator (ePO) 5.x

Problem

All ePO events fail to parse and eventually get stuck in the Events folder.

EventParser.log record the following messages:
 
E #08888 EPOEVENTS epoevents_dao.cpp(776): COM Error 0x80040E31, source=Microsoft OLE DB Provider for SQL Server, desc=Query timeout expired, msg=IDispatch error #3121
E #08888 EPOEVENTS epoevents.cpp(50): COM Error 0x80040E31, source=Microsoft OLE DB Provider for SQL Server, desc=Query timeout expired, msg=IDispatch error #3121
 
When you check the SQL Activity Monitor in SQL Server Management Studio, you might find a query similar to the following that is blocking many other queries, including insert event queries:
 
select count(*) as 'count' datepart( YEAR dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] ) ) as 'EPOEvents.DetectedUTC.year'  datediff(week dateadd(year datediff(year  0 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) 0) dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) + 1 + case when datepart(weekday dateadd(year datediff(year  0 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) 0) + @@datefirst - 7) - 1 < 7 and datepart(weekday dateadd(day @@datefirst - 7 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] ))) - 1 >= 7 then 1 when datepart(weekday dateadd(year datediff(year  0 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) 0) + @@datefirst - 7) - 1 >= 7 and datepart(weekday dateadd(day @@datefirst - 7 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] ))) - 1 < 7 then -1 else 0 end as 'EPOEvents.DetectedUTC.week'  datepart( YEAR dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] ) ) as 'EPOEvents.DetectedUTC.year'  datediff(week dateadd(year datediff(year  0 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) 0) dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) + 1 + case when datepart(weekday dateadd(year datediff(year  0 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) 0) + @@datefirst - 7) - 1 < 7 and datepart(weekday dateadd(day @@datefirst - 7 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] ))) - 1 >= 7 then 1 when datepart(weekday dateadd(year datediff(year  0 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) 0) + @@datefirst - 7) - 1 >= 7 and datepart(weekday dateadd(day @@datefirst - 7 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] ))) - 1 < 7 then -1 else 0 end as 'EPOEvents.DetectedUTC.week'  from [EPOEvents] where ( ( [EPOEvents].[Analyzer] is null or ( [EPOEvents].[Analyzer] <> N'DATALOSS2000' ))  and ( ( [EPOEvents].[Analyzer] is null or ( [EPOEvents].[Analyzer] <> N'DATALOSS2000' ))  and ( ( [EPOEvents].[Analyzer] is null or ( [EPOEvents].[Analyzer] <> N'DATALOSS2000' ))  and ( ( [EPOEvents].[Analyzer] is null or ( [EPOEvents].[Analyzer] <> N'DATALOSS2000' ))  and ( ( [EPOEvents].[Analyzer] is null or ( [EPOEvents].[Analyzer] <> N'DATALOSS2000' ))  and ( ( [EPOEvents].[Analyzer] is null or ( [EPOEvents].[Analyzer] <> N'DATALOSS2000' ))  and ( EPOEvents.AgentGUID IN (   SELECT lnd.AgentGUID   FROM EPOLeafNode lnd   inner join EPOBranchNode bnd on bnd.AutoID = lnd.ParentID   inner join EPONodePermissions npr on npr.NodeID = bnd.AutoID   WHERE lnd.AgentGUID IS NOT NULL   and npr.GroupID in (5  6) ) and ( [EPOEvents].[ThreatCategory] LIKE 'av%' and  ( [EPOEvents].[DetectedUTC] between '2015-07-23T20:10:16.288' and '2015-10-22T20:10:16.288' )  ) ) ) ) ) ) ) ) group by datepart( YEAR dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] ) ) datediff(week dateadd(year datediff(year  0 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) 0) dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) + 1 + case when datepart(weekday dateadd(year datediff(year  0 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) 0) + @@datefirst - 7) - 1 < 7 and datepart(weekday dateadd(day @@datefirst - 7 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] ))) - 1 >= 7 then 1 when datepart(weekday dateadd(year datediff(year  0 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) 0) + @@datefirst - 7) - 1 >= 7 and datepart(weekday dateadd(day @@datefirst - 7 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] ))) - 1 < 7 then -1 else 0 end order by datepart( YEAR dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] ) ) asc datediff(week dateadd(year datediff(year  0 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) 0) dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) + 1 + case when datepart(weekday dateadd(year datediff(year  0 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) 0) + @@datefirst - 7) - 1 < 7 and datepart(weekday dateadd(day @@datefirst - 7 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] ))) - 1 >= 7 then 1 when datepart(weekday dateadd(year datediff(year  0 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] )) 0) + @@datefirst - 7) - 1 >= 7 and datepart(weekday dateadd(day @@datefirst - 7 dateadd( MILLISECOND  -18000000 [EPOEvents].[DetectedUTC] ))) - 1 < 7 then -1 else 0 end asc
 
Reference the following Microsoft article for more information about opening SQL Activity Monitor: https://msdn.microsoft.com/en-us/library/ms175518.aspx#SSMSProcedure.

Cause

This problem can be made worse when many events exist in the EPOEvents table. When an Event query is executed by a non-Global Admin account, ePO must run permission checks against the user account. This action is required to verify that the user has group and product access for each of the queried events. This process is resource-intensive within SQL and can use large amounts of disk I/O, memory, and CPU resources.

If there are millions of events when ePO is executing this type of query, it can cause the query to:
  • take a long time.
  • prevent ePO from accessing the events table when it tries to insert events.

This problem can also occur if there are limited resources on the SQL Server, including:
  • Insufficient memory installed on the SQL Server or insufficient memory allotted to SQL
  • Poor disk I/O performance
  • High CPU usage on the SQL Server*
  • Database fragmentation

Solution

The following best practices are recommended to resolve issues when many events exist and there are limited resources on the SQL Server*:
  • Purge unnecessary events from the EPOEvents table either by using an ePO purge task or manually through SQL Server Management Studio. For details, see KB68961.
  • Add memory to the SQL Server* or see the Microsoft article at https://msdn.microsoft.com/en-us/library/ms178067.aspx for instructions on allotting more memory for SQL use.
  • Fine-tune the configuration of your SQL hardware for your environment, including hard disk space, CPU resources, and memory. See the relevant ePolicy Orchestrator Best Practices Guide for instructions.
  • For environments with more than 10,000 nodes, ensure that SQL is installed on a physical server. For important sizing recommendations for ePolicy Orchestrator, see KB71370.
  • Check for database index fragmentation and rebuild indexes, if needed. For the recommended maintenance plan for ePO databases, see KB67184.

Workaround

To find blocking queries within SQL Activity Monitor:
  1. Expand the Processes pane and find the column labeled Head Blocker. The column labels are truncated. You can see the full name by mousing over the column name or expanding the column size.
  2. Click the drop-down arrow on Head Blocker column and select 1. This action lists the blocking query. If there is no number 1 and only the word All is listed, there is no blocking process and you are not seeing the issue referenced in this article.
  3. Right-click the command, listed in the Command column, and select Details to show the entire query.

    If you see a query similar to the above that is blocking, you can try to kill the query by right-clicking the query and selecting kill. Confirm whether this action allows events to begin to parse. If killing the query temporarily resolves the issue, search the Orion log for an instance of the query found in the SQL Activity Monitor. See KB52369 for instructions to enable Orion Debug logging.

    If you find an instance of the query in the Orion log, you might identify what is generating this query. This type of query is most often a result of a query executed from a user dashboard within ePO. If so, you can try closing all open ePO consoles for all users to check whether this action also temporarily resolves the issue.  

Rate this document

Beta Translate with

Select a desired language below to translate this page.

Languages:

This article is available in the following languages:

English United States
Spanish Spain
Japanese

Glossary of Technical Terms


 Highlight Glossary Terms

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