McAfee EPO Integration causing DB locks when re-indexing

[Anonymous]

I've been informed by our DBA that when the re-indexing SQL jobs attempts to run on our McAfee EPO SQL server it is being hindered by database locks caused by the netwitness user that's been configured so we can pull EPO events into NetWitness.

The netwitness user only has read access to the database

The re-index job runs nightly

NetWitness is pulling events based on the default instructions from RSA

Has anyone one come across this before? 

Any suggestions for a fix or troubleshooting?

[sravan.koneti]

Jeremy Kerwin‌,

Are you talking about Netwitness appliance re-indexing impacting your McAfee DB server?

[Anonymous]

No, NetWitness executing SQL requests pulling events from the EPO SQL database is causing problems with the MSSQL Reindexing process

[HamedTorabi]

This is very typical . When tables on database being indexed, DML (Data Manipulation Language - Insert , updates Deletes) and DDL (Data Definition Language - select , truncate..etc) operations are halted until whol indexing is finished . From NW perspective there is nothing can be done, however, the MSSQL DBA can adjust the indexing schedule outside the collection times . Or you can ask the DBA to avoid indexing specific tables that are used for pulling the events. If you share the actual SQL query , I can tell you which tables to exclude during the MSSQL DB Indexing. 

[NaushadKasu1]

Jeremy,

I've had this issue at my customer long ago and we fixed it as follows:

1. Copy the XML collection configuration under /etc/Netwitness/ng/logcollection/content/odbc/<your collection XML file> to a custom version like customer_epolicy…xml

2. Edit the new custom XML and after the FROM anywhere in the FILE put in "WITH (NO LOCK)" e.g. …. FROM X WITH (NOLOCK) WHERE …. There should be 2-3 places, one is in the main query and the other is in the maxTrackingQuery.

3. Restart the nwlogcollector service

4. Go into the UI and add the new Source on your LC/VLC and re-add the host under the new custom source

5. Test/Validate

The reason we do step 1 above is to ensure that future upgrades do not replace your changes, so we have to create the new custom XML file for editing.

[HamedTorabi]

using NOLOCK option in Select statements in highly transnactional environment , such continuos data reads from LogCollection with ODBC, can lead to dirty data or even inconsistent ones to be retrieved as you are retrieving uncommited data from the database. See below :

https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/ 

[Anonymous]

HI Hamed, 

We're just using the default epo template that came with NetWitness so whatever tables it's using are the ones we are.

You said that the DBA can adjust indexing outside of the collections times. How is this changed in NetWitness? we've just got the defaults so I thought it's collecting constantly.