locked
PowerShell Convert Multiple Event Log Archives (evtx) to CSV files of the same size RRS feed

  • Question

  • Hi all,

    Hoping someone can help me with this tricky one.

    I have about 1 TB worth of Event Log Archives that I'm trying to convert to CSV. The resulting CSV files will then be ingested by an SQL Database.

    The event log archives vary in size (due to trying to combine them, then splitting them for security reporting).

    They range from 256 MB through to 20 GB. The initial attempts to combine logs from multiple servers were successful. They provided too large to be useful though and off the shelf Event Log tools wouldn't play nicely with evtx files bigger than 1 GB. Big mistake on my part, I know.

    What I would like to do, using the most efficient method, is to read the events and parse them out as CSV files of the same size.

    e.g. Log-001.evtx = 4 GB

    Split Log-001.evtx to 400 x 100 MB CSV files

    Log-002.evtx = 500 MB

    Split Log-002.evtx to 5 x 100 MB CSV files.

    I then hope to batch import these csv files (running over a week if needed, taking advantage of the Christmas break), and get SQL to ensure no duplicate events (same Server, Date & Time, Event ID, Event Message) are imported. I'm only concerned about duplicates because I want to process the big/merged log files just in case one of the originals was moved or missing.

    I found a script that does this one at a time, and have tried using a couple of tools like Event Log Parser but it doesn't seem to take every column, just the 'highlights.'

    I tried this:

    $Logs = Get-ChildItem -Path 'c:\temp\logs\' -Recurse -Name *.evtx | Select-Object Directory,Name
    foreach ($Log in $Logs)
        {
            $LogPath = Write-Host $Log.Directory
            $LogFileName = Write-Host $Log.Name
            $LogFullPath = Write-Host $LogPath + $LogFileName
            $CSVFileName = Write-Host $Log.Name+".csv"

            get-winevent -Path $LogFullPath | export-csv $CSVFileName -useculture
            exit
        }

    It converts them, but takes an incredibly long time and still gives me massive CSV files. It also doesn't show me any progress, so I don't know if it has hung or where it is up to.

    Any thoughts or suggestions would be greatly appreciated. Unfortunately I can't use off the shelf products as the only budgeted item I can use is my time :|

    Thanks in advance and Happy Holidays,

    Ian


    Ian

    • Moved by Bill_Stewart Wednesday, September 4, 2019 3:24 PM This is not "design solution for me" forum
    Wednesday, December 19, 2018 1:30 PM

All replies

  • You are expanding every event because exporting causes the messages to be included.  In the EVTX file there are no messages.  The messages are generated dynamically as you view or extract the events.  Also all text fields are added like Level and other basic flag fields.  In the event they are integers and in the extract they become strings.

    There is no way to export events to a CSV that won't do this but you can skip the messages.

    get-winevent -Path $LogFullPath | select * -Exclude Message | export-csv $CSVFileName -useculture

    There is really not point to turning the events into CSV.  This only makes decoding these things harder and the CSV will not be usable because most of the fields will get lost unless you pick an explicit set of fields.  Event records are no homogeneous.  Each provider can determine how many fields to use.


    \_(ツ)_/

    Wednesday, December 19, 2018 2:30 PM
  • I should clarify. The base properties are the same but the properties can be complex objects which cannot be stored in a CSV without first being converted to strings of some kind and format.


    \_(ツ)_/

    Wednesday, December 19, 2018 2:37 PM
  • Thanks JRV.

    I've got a foreach loop running now and specified each field.

    I'm also playing with Log Parser Studio now and seeing what I can do with the batch and powershell components.

    I'll keep looking and update if I find anything that works.


    Ian

    Wednesday, December 19, 2018 3:07 PM