C2 audit log trimming

  • I have what I think is a stupid question, but it's driving me crazy. I'm trying to create a weekly job that will get rid of the C2 audit files in my d:\mssql.1\mssql\data directory, but am having trouble getting it to work. It's fairly simple:

    DECLARE @expiration datetime

    DECLARE @today datetime

    SET @today = getdate()

    SET @expiration = dateadd(day,-15,@today)

    EXECUTE master.dbo.xp_delete_file 1,N'D:\MSSQL.1\MSSQL\Data',N'trc',@expiration,1

    From what I know, this should remove any files with the 'trc' extension older than 2 weeks. I'm guessing that the files 'are still in use' and can't be deleted, am I going about this all wrong?

    TIA

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • xp_delete_file has some strange restrictions. I think it only deletes certain types of files, regardless of extension.

    You ought to script this with filesystemobject/VBScript or Powershell

  • Thanks Steve, that makes sense.

    So next newb question: has someone done this with Powershell/VBScript?

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • Have a look at Microsofts' LogParser.

    It works like charm.

    One line of code will generate a text file with CMD extension, composed of DEL lines with file names

    The other will execute this CMD.

    Another way to do that is to use ROBOCOPY with PURGE by date parameter

  • A bit off-topic, but I am interested in knowing what benefits you see in C2 logging, and who in your organisation is requesting that you do this.

    The DOD TCSEC standard that formulated the various security levels became officially obsolete over a decade ago, being replaced by the Common Criteria standard. The C2 definition crosses a number of CC boundaries, and does not fully satisfy any of the CC categories.

    Many security professionals now only have a historical interest, if any, in the TCSEC standard. Hence my interest in why you are using this.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply