none
Script for export Security Event Log on Monthly Basis

    Question

  • Hi, I need help on script that can enable me to export only the Security Event Log on monthly basis.

    Windows OS is MS 2003 Server.

    Basically I will need to login to the server every starting of the month and extract last month security event log, but it is too tedious to login to 15 servers and do the filtering on each and every machine.

    So I am looking for a script that I can click and it will export the last month log into a csv file. I am fine with either setting the date manually or automated.

    Friday, February 17, 2012 7:45 AM

Answers

  • Here is an example of a monthly extraction to a Csv file.

    get-eventlog application -after 01-01-2012 -before 02-01-2012 |
         select Time,EntryType,Source,InstanceID,Message |
         Export-Csv archive-01-2012.csv -NoType


    ¯\_(ツ)_/¯

    Friday, February 17, 2012 3:25 PM
  • Why not just backup and clear the desired logs every month?

    wevtutil cl Application /bu:C:\admin\application.evtx

    You could run this in Powerhshell, in order to give the backups names relvant to the month/year.


    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Friday, February 17, 2012 4:53 PM

All replies

  • I checked the repository and found a script that seems to do what you want:

    http://gallery.technet.microsoft.com/ca316eb1-7b06-47ce-aa94-2b5fff5abb61

    Friday, February 17, 2012 8:58 AM
  • What do I need to change in the script? And does it purge cos I do not want it to purge.


    • Edited by awbrab Friday, February 17, 2012 9:58 AM
    Friday, February 17, 2012 9:57 AM
  • If you do not want it to clear the eventlog just remove these lines:

            #Clear the event logs 
            Write-Host "    - Clearing $LogName Log" 
            Clear-EventLog -LogName $LogName 

    Friday, February 17, 2012 10:11 AM
  • That does not backup the previosu month.  It always dumps the whole log.

    You need to extract from the log by date range and dump that to a CSV file.  It is a very truckyscript to write because you cannot dump many fields to a fCSV without breaking the CSV so you need to decode fields.

    If you ae on WS2008 then this is somewhat easier but won't work if yuo havw pre WS2008 hosts.

    Log extraction is such a specialty that it has bred a whole cottage industy of log backup software.

    If you are not good at scripting I recomment purchasing a solution.


    ¯\_(ツ)_/¯

    Friday, February 17, 2012 3:20 PM
  • Here is an example of a monthly extraction to a Csv file.

    get-eventlog application -after 01-01-2012 -before 02-01-2012 |
         select Time,EntryType,Source,InstanceID,Message |
         Export-Csv archive-01-2012.csv -NoType


    ¯\_(ツ)_/¯

    Friday, February 17, 2012 3:25 PM
  • Here is an example of a monthly extraction to a Csv file.

    get-eventlog application -after 01-01-2012 -before 02-01-2012 |
         select Time,EntryType,Source,InstanceID,Message |
         Export-Csv archive-01-2012.csv -NoType


    ¯\_(ツ)_/¯

    One side-note I would like to make to that is that jrv is using American notation of date, make sure you use the correct date notation to match your regional settings.


    Friday, February 17, 2012 4:15 PM
  • Here is an example of a monthly extraction to a Csv file.

    get-eventlog application -after 01-01-2012 -before 02-01-2012 | select Time,EntryType,Source,InstanceID,Message | Export-Csv archive-01-2012.csv -NoType


    ¯\_(ツ)_/¯

    One side-note I would like to make to that is that jrv is using American notation of date, make sure you use the correct date notation to match your regional settings.


    Ultimately we would use a date object which would be calculated.  I just wanted to demonstrate how basically simple this is and to point out where it is weak.

    The OP still has to make a choice.


    ¯\_(ツ)_/¯

    Friday, February 17, 2012 4:47 PM
  • Why not just backup and clear the desired logs every month?

    wevtutil cl Application /bu:C:\admin\application.evtx

    You could run this in Powerhshell, in order to give the backups names relvant to the month/year.


    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Friday, February 17, 2012 4:53 PM
  • Why not just backup and clear the desired logs every month?

    wevtutil cl Application /bu:C:\admin\application.evtx

    You could run this in Powerhshell, in order to give the backups names relvant to the month/year.


    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Security logs should never be cleared (BP).  They need to be kept online for breakin analysis by many tools.  We need two months or more depenfing on what is being analyzed.

    All BP recomendations require wrap around evenlogs that are big enough to hold all records for the required period.  This causes the log to be fixed in size and position which is easier on the disk storage and reduces head movement on the log file (prevents fragmentation).

    Logs can be extremely large and extreacting ny month or by week is usually the best approach.  Archiving is required for many of the new federal regulations and many law firms are asking their clients ro archive all of teh log data regularly.

    I prefer either daily or weekly archinving as it puts less stress on resources.  Logging to a database is recommended.


    ¯\_(ツ)_/¯

    Friday, February 17, 2012 5:03 PM
  • Logging to a database is recommended.


    ¯\_(ツ)_/¯


    Yes, I like that idea the best.  The you could query the database with SQL and analyse the logs for the past month or however you want to slice it.  And, you don't lose any information, which you would with your Powershell script:  You are selecting only a few choice fields for export.  This is perhaps a downfall of this method?

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Friday, February 17, 2012 5:11 PM
  • Logging to a database is recommended.


    ¯\_(ツ)_/¯


    Yes, I like that idea the best.  The you could query the database with SQL and analyse the logs for the past month or however you want to slice it.  And, you don't lose any information, which you would with your Powershell script:  You are selecting only a few choice fields for export.  This is perhaps a downfall of this method?

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Add a cube to the data base and publish to web.  Gives great historical view of activity by all slices and dices (day, month,year, department, employee, time of day, duration, etc). The web GUI can be drilled into, down and through and can auto link to external information.

    One stop shopping on a budget.


    ¯\_(ツ)_/¯

    Friday, February 17, 2012 5:58 PM
  • Did you know that all 2000/XP/2003 machines ship with "eventquery.vbs".  This can filter on date range and export to csv.

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Friday, February 17, 2012 7:05 PM
  • Did you know that all 2000/XP/2003 machines ship with "eventquery.vbs".  This can filter on date range and export to csv.

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Yes but it is even easier to use Get-Eventlog because the dates can be true datetime objects instead of strings.

    The eventquery script is a very good one and it is a great example of how to write a failrly compicated script in vbscript.

    XCACLS.vbs is even more impressive.

    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=19419


    ¯\_(ツ)_/¯

    Friday, February 17, 2012 7:36 PM
  • In the era pre-powershell there was a wonderful tool "logparser" ... nowadays I think hasn't lost his fashion

    http://technet.microsoft.com/en-us/scriptcenter/dd919274

    This is a sample 

    LogParser -i:EVT "select * from \\remoteComputer\System
    WHERE TimeGenerated BETWEEN to_timestamp('01/01/2012', 'dd/MM/yyyy') and to_timestamp('31/01/2012', 'dd/MM/yyyy') " -o:csv



    Gastone Canali >http://www.armadillo.it

    • Proposed as answer by jrv Sunday, February 19, 2012 5:46 PM
    Saturday, February 18, 2012 1:01 AM
  • In the era pre-powershell there was a wonderful tool "logparser" ... nowadays I think hasn't lost his fashion

    http://technet.microsoft.com/en-us/scriptcenter/dd919274

    This is a sample 

    LogParser -i:EVT "select * from \\remoteComputer\System WHERE TimeGenerated BETWEEN to_timestamp('01/01/2012', 'dd/MM/yyyy') and to_timestamp('31/01/2012', 'dd/MM/yyyy') " -o:csv


    Gastone Canali >http://www.armadillo.it

    No - LogParser is still very much alive.  It is being replaced by PowerSHell but when it does what yu want it is very fast, flexible and easy to use.


    ¯\_(ツ)_/¯

    Saturday, February 18, 2012 1:14 AM
  • "It is being replaced by PowerSHell" is a strong word

    Look this:
    logparser -i:evt "select EventTypeName, count(*) as [Number of events]  into c:\temp\Chart.gif from System group by EventTypeName"   -chartType:PieExploded -chartTitle:"Bytes per data type" -categories:off -o:chart

    Bye Gas

    @echo off
    ::
    :: Export Security Event Log on Monthly Basis
    :: Gastone Canali
    ::
    setlocal
    set path=C:\Program Files\Log Parser 2.2;%path%
    call :_GetFLm
    
    for /F %%S in ('type c:\temp\ListOfServers.txt') do call :_Export_log %%S "c:\temp\%%S_%month%_EvtReport.csv"
    
    
    goto :EOF
    
    :_GetFLm
    >  "%temp%\eva.v" echo execute (wscript.arguments(0))
    set exec=cscript //nologo //E:vbscript "%temp%\eva.v"
    Rem Get the First of the current month
    %exec%  "wscript.echo date() - day(date)+1"             >"%temp%\_F%"
    Rem Get the Last of the current month
    %exec%  "wscript.echo (date() + 31) - day(date() + 31)" >"%temp%\_L%"
    REM get the month
    %exec%  "wscript.echo Right("0" & Month(Now)  , 2)" >"%temp%\_m%" 
     
    call set /p First=<"%temp%\_F%"
    call set /p Last=<"%temp%\_L%"
    call set /p month=<"%temp%\_m%"
    goto :EOF
    
    :_Export_log
    
    echo LogParser -i:EVT  "select *  into "%~2" from \\%~1\Security 	WHERE TimeGenerated BETWEEN to_timestamp('%first%','dd/MM/yyyy') and to_timestamp('%last%', 'dd/MM/yyyy') "  -o:csv 
    goto :EOF


    Gastone Canali >http://www.armadillo.it

    Saturday, February 18, 2012 4:48 PM
  • "It is being replaced by PowerSHell" is a strong word

    Look this:
    logparser -i:evt "select EventTypeName, count(*) as [Number of events]  into c:\temp\Chart.gif from System group by EventTypeName"   -chartType:PieExploded -chartTitle:"Bytes per data type" -categories:off -o:chart

    Bye Gas

    @echo off :: :: Export Security Event Log on Monthly Basis :: Gastone Canali :: setlocal set path=C:\Program Files\Log Parser 2.2;%path% call :_GetFLm for /F %%S in ('type c:\temp\ListOfServers.txt') do call :_Export_log %%S "c:\temp\%%S_%month%_EvtReport.csv" goto :EOF :_GetFLm > "%temp%\eva.v" echo execute (wscript.arguments(0)) set exec=cscript //nologo //E:vbscript "%temp%\eva.v" Rem Get the First of the current month %exec% "wscript.echo date() - day(date)+1" >"%temp%\_F%" Rem Get the Last of the current month %exec% "wscript.echo (date() + 31) - day(date() + 31)" >"%temp%\_L%" REM get the month %exec% "wscript.echo Right("0" & Month(Now) , 2)" >"%temp%\_m%" call set /p First=<"%temp%\_F%" call set /p Last=<"%temp%\_L%" call set /p month=<"%temp%\_m%" goto :EOF :_Export_log echo LogParser -i:EVT "select * into "%~2" from \\%~1\Security WHERE TimeGenerated BETWEEN to_timestamp('%first%','dd/MM/yyyy') and to_timestamp('%last%', 'dd/MM/yyyy') " -o:csv goto :EOF


    Gastone Canali >http://www.armadillo.it

    I agree - I should probably have said "It can easily be replaced by PowerShell".

    Since the advent of PowerShell there has been no attempt to update LogParser.  It no longer is distributed with a Resource Kit.

    Microsoft is pretty insistent that all future system tools should be built with and in PowerShell.

    I use LogParser frequestly and I like it.  It would be nice to see it fully integrated into PowerShell althoug PowerSHell makes a great front-end to LogParser.

    Most users are not patient enough to learn LogParser.

    With Event Logs PowerShell does an equally robust jobe of access  and management with a much simpler interface if you already know PowerShell.


    ¯\_(ツ)_/¯

    Saturday, February 18, 2012 5:03 PM
  • Since the advent of PowerShell there has been no attempt to update LogParser.  It no longer is distributed with a Resource Kit.

    Microsoft is pretty insistent that all future system tools should be built with and in PowerShell.

    Probably the really reason is that Gabriele Giuseppini, the log parser author's has left Microsoft...


    Gastone Canali >http://www.armadillo.it

    Saturday, February 18, 2012 6:35 PM
  • Since the advent of PowerShell there has been no attempt to update LogParser.  It no longer is distributed with a Resource Kit.

    Microsoft is pretty insistent that all future system tools should be built with and in PowerShell.

    Probably the really reason is that Gabriele Giuseppini, the log parser author's has left Microsoft...


    Gastone Canali >http://www.armadillo.it

    Yes but he didn't write it for Microsoft, he just gave it to them.  It was distributed int the RK until recently when Microsoft dropped almost all commandline tool developemnt an upgrade in favor of PowerShell.

    I wish they would upgrade LogParser to Net and Powershell.  It is already fully COM so the upgrade should not be too hard.


    ¯\_(ツ)_/¯

    Saturday, February 18, 2012 6:42 PM
  • Is there any VB script? I am not allowed to install Powershell.

    By the way, I cannot run the script saying that I need to sign the script!!! Script have to be signed???

    Tuesday, March 13, 2012 4:22 AM
  • Is there any VB script? I am not allowed to install Powershell.

    By the way, I cannot run the script saying that I need to sign the script!!! Script have to be signed???

    Use LogParser top export ebentlogs using a batch file.  It does not require installing POwerShell.

    If you really need VBScript then there are numerous log export routines in the repository in VBscript.


    ¯\_(ツ)_/¯

    Tuesday, March 13, 2012 4:33 AM
  • Is there any VB script? I am not allowed to install Powershell.

    By the way, I cannot run the script saying that I need to sign the script!!! Script have to be signed???

    Use LogParser top export ebentlogs using a batch file.  It does not require installing POwerShell.

    If you really need VBScript then there are numerous log export routines in the repository in VBscript.


    ¯\_(ツ)_/¯

    Thanks, so what is the command and do I need to install anything cos I cannot install any 3rd party software?

    By the way I am running locally not remotely cos I saw the earlier reply is to run the script remotely.


    • Edited by awbrab Tuesday, March 13, 2012 5:54 AM
    Tuesday, March 13, 2012 5:53 AM
  • If you really need VBScript then there are numerous log export routines in the repository in VBscript.

    Please look in the repository for VBScript scripts that can do what you ask.


    ¯\_(ツ)_/¯

    Tuesday, March 13, 2012 6:10 AM
  • Thanks, i cant find anything...
    Tuesday, March 13, 2012 7:34 AM
  • Here are 51 scripts for managing event logs.  You can chose one that most nearly matches what you need to do and modify it to your needs.

    http://gallery.technet.microsoft.com/scriptcenter/site/search?f%5B0%5D.Type=RootCategory&f%5B0%5D.Value=logs&f%5B0%5D.Text=Logs%20and%20monitoring&f%5B1%5D.Type=SubCategory&f%5B1%5D.Value=eventlogs&f%5B1%5D.Text=Event%20Logs&pageIndex=1

    The responses in this thread have also provided numerous alternatives.  You might start with one of them.


    ¯\_(ツ)_/¯

    Tuesday, March 13, 2012 12:03 PM
  • Here is an older script in VBscript that wexports a months worth of Event Log records from teh application log.

    Const cSep = ""","""
    Const cBeginLine = """"
    Const cEndLine = """"
    dStart=#02/01/2012#
    dEnd=#03/01/2012#
    Set dStartDate = CreateObject("WbemScripting.SWbemDateTime")
    Set dEndDate = CreateObject("WbemScripting.SWbemDateTime")
    dStartDate.SetVarDate dStart, True
    dEndDate.SetVarDate dEnd, True
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate,(Security)}!\\" & strComputer & "\root\cimv2")
    Set colLoggedEvents = objWMIService.ExecQuery _
        ("Select * from Win32_NTLogEvent Where Logfile = 'Application'  AND TimeWritten >= '" _ 
            & dStartDate & "' and TimeWritten < '" & dEndDate & "'") 
    WScript.Echo "Category.ComputerName,EventCode,Message,RecordNumber,SourceName,TimeWritten,Type,User"
    For Each objEvent In colLoggedEvents
         str = cBeginLine _
         & objEvent.Category _
         & cSep & objEvent.ComputerName _
         & cSep & objEvent.EventCode _
         & cSep & objEvent.RecordNumber _
         & cSep & objEvent.SourceName _
         & cSep & objEvent.TimeWritten _
         & cSep & objEvent.Type _
         & cSep & objEvent.User _
         & cSep & objEvent.Message _
         & cEndLine
         WScript.Echo str 
    Next


    ¯\_(ツ)_/¯

    Tuesday, March 13, 2012 12:32 PM
  • Here is an older script in VBscript that wexports a months worth of Event Log records from teh application log.

    Const cSep = ""","""
    Const cBeginLine = """"
    Const cEndLine = """"
    dStart=#02/01/2012#
    dEnd=#03/01/2012#
    Set dStartDate = CreateObject("WbemScripting.SWbemDateTime")
    Set dEndDate = CreateObject("WbemScripting.SWbemDateTime")
    dStartDate.SetVarDate dStart, True
    dEndDate.SetVarDate dEnd, True
    strComputer = "."
    Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate,(Security)}!\\" & strComputer & "\root\cimv2")
    Set colLoggedEvents = objWMIService.ExecQuery _
        ("Select * from Win32_NTLogEvent Where Logfile = 'Application'  AND TimeWritten >= '" _ 
            & dStartDate & "' and TimeWritten < '" & dEndDate & "'") 
    WScript.Echo "Category.ComputerName,EventCode,Message,RecordNumber,SourceName,TimeWritten,Type,User"
    For Each objEvent In colLoggedEvents
         str = cBeginLine _
         & objEvent.Category _
         & cSep & objEvent.ComputerName _
         & cSep & objEvent.EventCode _
         & cSep & objEvent.RecordNumber _
         & cSep & objEvent.SourceName _
         & cSep & objEvent.TimeWritten _
         & cSep & objEvent.Type _
         & cSep & objEvent.User _
         & cSep & objEvent.Message _
         & cEndLine
         WScript.Echo str 
    Next


    ¯\_(ツ)_/¯

    Thanks, with the script what do I need to change to extract security log? I tried changing the Application to Security but it won't work.

    Another question is how do I save the result to a txt or csv file instead of the result popping out?

    Very sorry, I am a beginner in scripting.

    Wednesday, March 14, 2012 6:24 AM
  • Sorry but you have to be an administrator to export the security log.

    The output can be sent to a file by using the redirector.


    ¯\_(ツ)_/¯

    Wednesday, March 14, 2012 7:38 AM
  • Sorry but you have to be an administrator to export the security log.

    The output can be sent to a file by using the redirector.


    ¯\_(ツ)_/¯

    Redirector as in the one use in command line?
    example: test.vbs > test.txt

    I tried and it create a test.txt with nothing inside.

    Do you mind explaining in more details cos I really a noob in scripting.

    Wednesday, March 14, 2012 9:02 AM
  • Sorry but you have to be an administrator to export the security log.

    The output can be sent to a file by using the redirector.


    ¯\_(ツ)_/¯

    Redirector as in the one use in command line?
    example: test.vbs > test.txt

    I tried and it create a test.txt with nothing inside.

    Do you mind explaining in more details cos I really a noob in scripting.

    What you are describing is impossible. If you get output without the redirector then it will be in the file. Are you looking in the correct file?

    Why not name the file somethinglike evenlog.txt so ut will not be mistaken for some arbitrary file named test.txt

    You will also need to run it like this

    cscript GetEventLog.vbs >eventlog.txt //NOLOGO


    ¯\_(ツ)_/¯

    Wednesday, March 14, 2012 4:40 PM