One of the great features in Event Log Explorer 4.7 is a command line utility to export event logs to the database (eldbx.exe). Using this utility, you can make a centralized storage of events for better forensic investigations and improve system and security management on your network.
In the previous article I described how to export events into the database directly from Event Log Explorer application. You can use new Database -> Upload to table command to save a current log view into a SQL table. However, this method has a number of drawbacks:
- You need to open event log before export
- It is difficult to export many logs in a row
- If exporting XML data option enabled, the whole export procedure may take very long time.
Event Log Database Export Utility (eldbx.exe) is free of these drawbacks. It’s a console utility and you can use it in batch files to export different logs in a row. And it is always exporting XML data fast enough.
The utility is described in Event Log Explorer documentation.
The output table is compatible with Elodea table format. And you can load the exported tables in Event Log Explorer using Database -> Load table command.
How to use Event Log Database Exporter.
The utility is compatible with Microsoft SQL Server (or SQL Server Express) 2008 or better.
First, we recommend you to create a separate database for the logs. You can create it from Event Log Explorer as described in Using Event Log Explorer to access database events or create it using SQL Server utilities (e.g. Microsoft SQL Server Management Studio).
Alternatively, you can create the database using the utility itself:
Just run it using CreateDB command. E.g. if you have SQL Server Express installed on your local machine, to create database Events on it, just run the utility as follows:
eldbx CREATEDB /DBSERVER:localhost\SqlExpress /DBNAME:Events
eldbx will try to connect SQLServer using your current Windows credential and create database Events using default parameters. You can check other database settings on eldbx documentation page. For database tuning, I recommend using SQL Server Management Studio to create or alter the database – it gives maximum flexibility in fine-tuning the database.
Now, let’s export local system log into SQL table SysEvents.
eldbx EXPORT /DBSERVER:localhost\SqlExpress /DBNAME:Events /TABLE:SysEvents /LOGNAME:System
To export system log from another computer (Host1) and append its events to SysEvents table, run this command:
eldbx EXPORT /DBSERVER:localhost\SqlExpress /DBNAME:Events /TABLE:SysEvents /TXA:append /HOST:Host1 /LOGNAME:System
This command assumes that your current user have access to the system event log from Host1. If your permissions are not enough, you can explicitly specify user name and password to access the log. Just add 2 parameters to the previous command line:
eldbx EXPORT /DBSERVER:localhost\SqlExpress /DBNAME:Events /TABLE:SysEvents /TXA:append /HOST:Host1 /LOGNAME:System /User:username /Password:password
Export specific events
You may want to export only specific events.
Let’s export the Application event log without Information and Warning events and add the System log without Information events.
First, we need to create an XML query. Since the syntax of XML query allows to join different logs, we can get all the required events in one query and export it at once (without using /TXA:append option).
<QueryList> <Query Id="0"> <Select Path="Application">*[System[(Level=1 or Level=2)]]</Select> <Select Path="System">*[System[(Level=1 or Level=2 or Level=3)]]</Select> </Query> </QueryList>
You can use Windows Event Viewer to create basic XML queries or edit them manually.
Save this query as a file (AppSys.xml).
Run eldbx as follows:
eldbx EXPORT /DBSERVER:localhost\SQLEXPRESS /DBNAME:Events /TABLE:SelectedEvents /QUERY:AppSys.xml
Database exporter will create SelectedEvents table, runs the query and copy all events that match query conditions to the table.
Let’s add Audit Failure Events from Security log on Host1 to this table:
Create XML query and save it as AF.xml
<QueryList> <Query Id="0" Path="Security"> <Select Path="Security">*[System[band(Keywords,4503599627370496)]]</Select> </Query> </QueryList>
Run eldbx as follows:
eldbx EXPORT /DBSERVER:localhost\SQLEXPRESS /DBNAME:Events /TABLE:SelectedEvents /QUERY:AF.xml /TXA:append /HOST:Host1 /User:username /Password: password
Now you can run Event Log Explorer, connect the database and open exported tables.