Deleting DLP events from ePO when you have not done this for over a year and have 18 millions events

[pierce]

So trying to upgrade my ePO from 4.6.4 to 5.1, got the warning about over a million events in a table.

see that the DLP events table is 18 million events.

Ran the 'delete older than 90 days' task in the DLP policy screen, this took out my SQL server as it wrote tons of data to the logs and filled a drive.

Managed to get everything running, ran delete older than the oldest event - 1 day and same thing happened.

How else can i delete this data?

All the events are taken out every few minutes into my SIEM so I can afford to be drastic and cut this back, just need a way to do it without breaking everything again.

[djjava9]

You have to delete it gradually.  If your oldest event is a year old, than delete everything over 11 months, then 10 months, etc.

[pierce]

So oldest event is  6/March/2013

I set it to delete anything older than 7th of March, same issue. It wrote 5GB of data to our log directory before we caught it and stopped it.

Any other ideas?

I have also opened a case with McAfee. The two tables are:

DLP_EvidenceTypeAndValue

and

DLP_EVENTINFO

I was given a script to delete the data but it only worked on the events table and not dlp events.

Message was edited by: pierce on 4/25/14 10:46:55 AM CDT

[romardy]

You need to these on DLP console Database Administration. Use Delete Events by Date or Delete Events by Number of Days.

[pierce]

I'm affraid that option does not work for me, deleting past a certain date always crashes even if i select a date that will only delete a single event.

Going to backup my database and then drop both tables completely unless I hear back from support for a better method.

I think as DLP has gone through 2 or 3 version changes with all this data that could be the issue.

[pierce · Accepted solution]

So in the end we shut down everything touching the database (ePO, Agent handler, Splunk our SIEM).

Then our DBA ran the storred procedure for small periods and slowly increased, currently going through 1 month at a time and clearing the data.

Stored procedure is:

DLP_sp_DeleteEvents_before'MM/DD/YYYY'

hope this helps!

[bretzeli]

We have around 900MB 0.9GB with EPo 4.6 and DLP 9.1 and we have seen following:

The largest thing is "DLP_EvidenceTypeAndValue" with around 716MB from the 810 of Total SQL 2005 (EPO and DLP)

I see no function in GUI or SP which could delete that Evidence Database (Mainly fully Hardware Info about any USB device or device attached)

Any help welcome to reduce that size. I know its good data but we don't need it from the years because not productive.

TableName indexName RowCounts TotalPages UsedPages DataPages TotalSpaceMB UsedSpaceMB DataSpaceMB

• DLP_EventInfo PK_DLP_EventInfo 10080 625 591 585 4 4 4
• DLP_Events_Rollup PK_DLP_Events_Rollup 0 0 0 0 0 0 0
• DLP_EventType PK_DLP_EventType 55 2 2 1 0 0 0
• DLP_EventViewColumnsTable NULL 50 2 2 1 0 0 0
• DLP_EvidenceTypeAndValue NULL 7163794 75137 75114 75113 587 586 586

Sample from table:
EventRowID EvidenceType EvidenceValue

506986 PRODUCT_ID 8919

506986 SERIAL_NUMBER 0301609319

506986 USB_CLASS 8

506986 IO_OPTIONS READ_WRITE

506983 VENDOR_ID 0BDA

506983 PRODUCT_ID 0181

506983 SERIAL_NUMBER 20060413092100000

506983 USB_CLASS 8

506983 IO_OPTIONS READ_WRITE

506983 VOLUME_SERIAL_NUMBER FFFFFFFF

506984 DEVICE_CLASS_GUID 4D36E967-E325-11CE-BFC1-08002BE10318

506984 CLASS_DISPLAY_NAME Laufwerke

[hansolo32]

I've currently got over 11 million events because the DLP admins are requiring that we keep 6 months worth of events.  I'd like to figure out how to take these events and get them offline from ePO with the incident data AND the actual evidence.  My current hang up on cleaning up the old is one specific record (suspected to be corrupted).  I'm working with support to get a supported SQL script to wipe that record so I can turn back on the purge older than 6 months.  Purging anything before or after (within a range) purges fine.  It's just the one specific time back in February.

Anyone got any solutions to suggest for archiving the data and evidence to be used in analysis and investigations later on as needed?

- Eric

[pierce]

Hey Eric,

we were in the same boat but keeping 12 months of data, once we got Splunk setup as our SIEM the logs were kept in there for 12 months and the application retention could be reduced.

Maybe look into the McAfee SIEM or even the Splunk free tier as another option of somewhere to keep log data and get it out of your production system?

[hansolo32]

The DLP Admins already have SPLUNK.  The problem is the evidence and the link with the incidents.  One of our McAfee sales engineers has an idea where we have another ePO server strictly for their stuff and just roll up the info they'll be doing analysis on to it and only keep a short amount on the prod database.  That way they'll still have their links to the encrypted evidence files as well as their analysis tool of all of that data.

Thanks for the suggestion.