1 2 Previous Next 10 Replies Latest reply on Oct 7, 2015 9:44 AM by hansolo32

    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.

        • 1. Re: Deleting DLP events from ePO when you have not done this for over a year and have 18 millions events
          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.

          1 of 1 people found this helpful
          • 2. Re: Deleting DLP events from ePO when you have not done this for over a year and have 18 millions events
            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
            • 3. Re: Deleting DLP events from ePO when you have not done this for over a year and have 18 millions events
              romardy

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

              • 4. Re: Deleting DLP events from ePO when you have not done this for over a year and have 18 millions events
                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.

                • 5. Re: Deleting DLP events from ePO when you have not done this for over a year and have 18 millions events
                  pierce

                  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!

                  • 6. Re: Deleting DLP events from ePO when you have not done this for over a year and have 18 millions events
                    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

                    • 7. Re: Deleting DLP events from ePO when you have not done this for over a year and have 18 millions events
                      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

                      • 8. Re: Deleting DLP events from ePO when you have not done this for over a year and have 18 millions events
                        scott.culbertson

                        Taken from KB article 68961:

                         

                        1.Remove older events from the database with the following case-sensitive OSQL commands:
                        a.Click Start, Run, type cmd, and then click OK.


                        b.Type the following command and press ENTER:

                        osql -E -S <servername\instance> -d ePO_<databasename>
                         


                        c.At the command prompt, type each of the following commands and press ENTER after each command:

                        DELETE FROM ePOevents WHERE DetectedUTC < 'yyyy-mm-dd 00:00:00.000'
                        GO

                        NOTE: Ensure that you change yyyy-mm-dd to the appropriate date; everything earlier than the date you specify will be deleted.

                         

                        d.If you have SQL Server Management Studio or SQL Server Management Studio Express installed, run it, and log in with Windows Authentication. Expand the DATABASE node in the Object Browser window, click the ePO database, click new query, and paste the following. This deletes the events without filling up the log file if hard drive space is low.

                        SET rowcount 10000
                        DELETE FROM epoEvents
                        WHERE detectedutc < 'yyyy-mm-dd'
                        WHILE @@rowcount > 0
                        BEGIN
                        DELETE FROM epoEvents
                        WHERE detectedutc < 'yyyy-mm-dd'
                        END
                        SET rowcount 0
                        GO

                        NOTE: Ensure that you change yyyy-mm-dd to the appropriate date; everything earlier than the date you specify will be deleted.

                        • 9. Re: Deleting DLP events from ePO when you have not done this for over a year and have 18 millions events
                          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?

                          1 2 Previous Next