4 Replies Latest reply on Jan 17, 2013 5:38 AM by diwi

    ePO 3.6.1.255 / Scheduled Tasks / Purge Events ...

    diwi

      Hello,

       

      first of all I know that ePO 3.6.1 is no longer supported. The old ePO version is used here for detection of clients, who still use the old agent with the help of attached IP-ranges at a specific context. Furthermore, around 150 clients are still not migrated, these are WAN-clients, we would like to migrate soon. Around 1800 clients are already migrated and connected to an ePO 4.5.5, which will soon be migrated to 4.6.x !!!

       

      In this new ePO 4.5.5, we sucessfully identified millions of 1095 events (EPOEvents), which increased the database size significantly to around 60GB in size. With the help of KB52452 we did the same identification in our ePO 3.6.1 server and the result was over 70 million events (Events_VSEBehaviourBlock) which inflated our database size to over 60GB as well.

       

      We created a query to identify 1095 events (occurring before 1st June 2012) in our ePO 4.5.5 database and used the 'Purge Threat Event Log' server task to remove them at weekend times during off-peak hours. Repository pull and replication tasks were delayed until today. Because of the experiences I made with the 'Purge Events' task in ePO 3.6.1, I expected a long runtime for the task in ePO 4.5.5 as well. The purge task took around 2 hours only to complete, which really suprised me. This morning I started a database shrink, which took around 30-60 minutes to complete and the database size was reduced from 60 to under 18GB in size. Great result. Further database size reduction planned.

       

      Unfortunately, the same thing didn't work in our ePO 3.6.1 server with the 'Purge events' task, which was scheduled for the same off-peak time, as the taks in our 4.5.5 ePO-server. The 'Purge Events' task was configured to 'Delete events based on event ID' with only Minor (1095) selected and events, which are older than 90 days. This task is running since 84 hours, which doesn't make sense, since it was started at 06/29/2012 at 11:59 PM, which is nearly 61 hours now? Maybe that's the result of canceled 'Purge Events' tasks with the help of reboots, as those tasks cannot be cancelled! In Progress status still says 0% completed! I already read about this behaviour in some KB's here.

       

      So, in the meantime I doubt that I will be able to reduce the database size in my ePO 3.6.1 with the help of the 'Purge Events' task inside of my ePO and I am now looking for SQL-commands to reduce the ePO-database (we use SQL Server 9.0.5057 = SQL 2005). Preferably I would like to get rid of 1095 events only with the possibility to remove only those events before a special date.

       

      So, where are the SQL-specialists, that can provide me with the necessary commands? I hope this is more effective and much faster than using the 'Purge Events' task in ePO 3.6.1 ?

       

      Many thanks for your efforts and best regards,

      DiWi

       

      Message was edited by: diwi on 7/2/12 12:46:38 PM CEST
        • 1. Re: ePO 3.6.1.255 / Scheduled Tasks / Purge Events ...
          pboedges

          i had the same issue, while McAfee strongly urges against anything being done directly against the database itself.  Good luck

           

          I used the following which are written for SQL2008, you should be able to use them for 2005 as is or with some minor tweeks.

           

          WHILE EXISTS (SELECT * FROM EPOEvents WHERE THREATEVENTID = '1095' and ReceivedUTC BETWEEN'2000-01-01 00:00:00.000' AND '2012-06-31 23:59:59.999' )

          BEGIN

            SET ROWCOUNT 1000

            DELETE EPOEvents WHERE THREATEVENTID = '1095'and ReceivedUTC BETWEEN '2000-01-01 00:00:00.000' AND '2012-06-31 23:59:59.999'

            SET ROWCOUNT 0

          END

           

          You may need to play with teh ROWCOUNT to suit your needs.

           

          If you need to purge event id 1092 out here is the sql for that.

           

          WHILE EXISTS (SELECT * FROM EPOEvents WHERE THREATEVENTID = '1092' and ReceivedUTC BETWEEN'2000-01-01 00:00:00.000' AND '2012-06-31 23:59:59.999' )

          BEGIN

            SET ROWCOUNT 1000000

            DELETE EPOEvents WHERE THREATEVENTID = '1092'and ReceivedUTC BETWEEN '2000-01-01 00:00:00.000' AND '2012-06-31 23:59:59.999'

            SET ROWCOUNT 0

          END

           

          Also i would recommend turning off those events in the (4.x) Server Settings Event Filter.

           

          Message was edited by: pboedges on 7/2/12 2:37:03 PM GMT-05:00
          • 2. Re: ePO 3.6.1.255 / Scheduled Tasks / Purge Events ...
            diwi

            Hello pboedges,

             

            first of all many thanks for taking the time to reply and providing a possible solution to my problem. Well, in that case I strongly recommend to ignore McAfee's advice to not do anything directly against the database, as the ePO 3.6.1 'Purge Events' tasks is useless to me. The time for the task that's reported is wrong, the progress remains at 0% and the task never finishes, at least not in 3 days for a database that's 60GB in size. A similar task in ePO 4.5.5 took only 2 hours and 30 minutes to complete with nearly the same size of the database.

             

            I found similar SQL-commands in other threads (not in the Community) and there the 'ROWCOUNT' was used as well. I have another question. I understand the part ...

             

            WHILE EXISTS ... BEGIN

             

            DELETE ...

             

            END

             

            but for what reason is a ROWCOUNT set to 1000 or 1000000 and after the DELETE line set back to 0 again? Can you explain that to me?

             

            Furthermore, maybe you also know, what kind of SQL-commands are necessary to attach to a specific database and to de-attach from that database again, so that the SQL-command is only executed on the correct database, if the selection of the right database (ePO database) wasn't done. Just to be sure.

             

            Best regards,

            DiWi

             

            Message was edited by: diwi on 7/3/12 7:24:39 PM CEST
            • 3. Re: ePO 3.6.1.255 / Scheduled Tasks / Purge Events ...
              pboedges

              What i found is that if you dont use the ROWCOUNT and set the amount ot be purged you risk filling up the transaction log which will kill the process and hinder the operation of your database.  You can remove the ROWCOUNT statement and the script will attempt to purge everything you tell it, again transaction log filling is a potential risk.

              • 4. Re: ePO 3.6.1.255 / Scheduled Tasks / Purge Events ...
                diwi

                Ok, here we go!

                 

                A couple of weeks ago I found time to reduce the ePO database of our old ePO 3.6.1.255 server. It took several days but I managed to reduce the database below 150MB, since there is only one node left, the ePO server itself!

                 

                Let's assume the name of the ePO server is SERVER01.

                 

                The following SQL scripts were used...

                 

                ******************************************************************************** *

                 

                1) Identify largest ePO-tables

                 

                USE ePO_SERVER01

                EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

                 

                2) Identify event categories from the largest ePO-table

                 

                USE ePO_SERVER01

                SELECT TVDEventID, COUNT(*) FROM Events_VSEBehaviourBlock

                GROUP BY TVDEventID

                 

                3) Identify eventcount 1095

                 

                USE ePO_SERVER01

                DECLARE @COUNTER BIGINT

                SET @COUNTER = 0

                SELECT @COUNTER = COUNT(*) FROM Events_VSEBehaviourBlock WHERE TVDEventID = '1095'

                PRINT N'The number of Event 1095 entries in the database = ' + CAST(@COUNTER AS VARCHAR(15))

                 

                4) Delete specific evend id's

                 

                USE ePO_SERVER01

                WHILE EXISTS (SELECT * FROM Events_VSEBehaviourBlock WHERE TVDEventID = '1095')

                BEGIN

                  SET ROWCOUNT 1000

                  DELETE Events_VSEBehaviourBlock WHERE TVDEventID = '1095'

                  SET ROWCOUNT 0

                END

                 

                ******************************************************************************** *

                 

                Why 1095 you may ask? Because it was the event that bloated the database the most!

                 

                I used script nr.4 several times, because I interrupted the script several times to see, if something happens and also shrinked the database several times.

                 

                Best of luck by identifying what bloated your database and shrinking it again

                 

                Best regards,

                DiWi