Data Center Security

 View Only
  • 1.  SQL Express 2014 - Used all 10GB space limit

    Posted May 22, 2016 11:45 AM

    Hi,

    Symantec Data Center Security - Server Advanced v6.5 MP1 has full used up all of the SQL Express 2014 10GB database limit. I cannot find where to clear some data / history / log to free up database space. Please advise. Thank you.



  • 2.  RE: SQL Express 2014 - Used all 10GB space limit
    Best Answer

    Broadcom Employee
    Posted May 23, 2016 02:19 PM

    You can connect to the database using Microsoft SQL Management Studio

    You can then run the stored procedures to remove event data.
    https://support.symantec.com/en_US/article.TECH116227.html

    Or remove events manually 
    https://support.symantec.com/en_US/article.TECH112966.html

     



  • 3.  RE: SQL Express 2014 - Used all 10GB space limit
    Best Answer

    Posted May 23, 2016 02:22 PM

    That is the disadvantage of using the Express database . . .

    Enable event purging in the console.  Admin > Setting s > System Settings > Event Management.  Note that the default setting for this is to run the purge once every 23 hours and limit the purge to 200,000 events.

    To manually purge, you will need to run this query:

    USE SCSPDB
    DELETE FROM CSPEVENT WHERE EVENT_DT<= '2006-03-10 23:59:59:999'

    Change date and time accordingly.  You can do this with SQL Studio, or from an advanced query in DCS (Reports > Queries > New.  Check the 'Advanced Query' checkbox).  Note that if you use the advanced query in DCS, you will need to add a SELECT statement after the purge statement, as DCS does a check to see if you are running a select statement -- it won't let you run an advanced query without one.  Like this:

    SELECT * FROM CSPEVENT WHERE EVENT_DT<= '2006-03-10 23:59:59:999'

     



  • 4.  RE: SQL Express 2014 - Used all 10GB space limit

    Posted Nov 21, 2016 07:28 PM

    I am trying to achieve the same thing as my SQL database has grown quite large.  I need to remove anything older than 90 days every 24 hours.

    How would I accomplish this with DCS 6.5 MP1 

    Is this something that can be scripted to run daily at say 6:00 AM ?

    Also after this is done, what type of maintenance would need to be done on the SQL end ?  Would I then rebuild the index, followed by updating the statistics ?

    I am not a DBA.

     

    Regards,

    Ian



  • 5.  RE: SQL Express 2014 - Used all 10GB space limit
    Best Answer

    Posted Nov 23, 2016 11:54 AM

    Hi Ian,

    If you have under 500 Assets and the DB greater than 100GB then you're monitoring more than you need. You'll need to figure out what you're trying to achieve in regards to monitoring. Are you trying to meet a regulation? The Baseline detection policy is noisy out of the box. It is best to highlight what an audit is asking for and meet those items first. After that then you can add some more items as "nice to have's" but not required.

    If you are also using Prevention, those policies are also noisy out of the box as well. Best to start with a couple of lab systems to generate the events that need to be tuned (Allowed Exceptions). Then you can slowly start to expand the policy to additional systems.

    There is a job that runs by default every 23 hours and acts according to the settings you have in Admin > Settings > System Settings > Event Management. Changing the default amount to something higher can help. But it can cause issues if it is set too high (USE EXXTREAM CAUTION in PROD). Its best to move upward slowing to get to a happy medium.

    In the directoty: "C:\Program Files (x86)\Symantec\Data Center Security Server\Server\tomcat\conf" look for a file called "sis-server.properties"

    Uncomment (remove the #) from these lines:

    #sisdbcleanup.runtime=23
    #sisdbcleanup.event.purge.limit=100000

    The runtime is in every # of hours and the purge limit is the number of events. This again is tied to the setting in the console Admin > Settings > System Settings > Event Management for realtime events. So if you have this set to 90 days this setting says: every 23 hours, delete 100,000 events older than 90 days.

    What I would recommend is to do is first run the query from Chuck's comment to determine the number of events you have generated in a 90 day period. Then divide that total to get what's generated on average every day. This is a rough estimate of course.

    90 days ago from today is 2016-08-25

    Open a new query in SQL Studio:

    SELECT * 
    FROM CSPEVENT 
    WHERE EVENT_DT between '25-Aug-16 12:00AM' AND '22-Nov-16 12:00AM'

    The total rows returned give you the number of events generated in the last 90 days.

    Let's say the total is 90,000,000 or 1M events per day.

    Break out your calculator to figure out the best setting.

    In a 24 hour period you could run the job every 6 hours and remove 250,000 events equaling 1M events.

    It would look like this:


    sisdbcleanup.runtime=6
    sisdbcleanup.event.purge.limit=250000


    ->Save the sis-server.properties file and restart the DCS Manager service for the new settings to take effect.
    ->Monitor SQL and the DCS Manager for any performance degradation.

    After a week, note the DB size. Then shrink the DB and note the new size.

    Definatley be careful with this but I've done this for customers to help keep DB growth in check and it works well.

    --> shane at conventus.com