Event log file (*.evt) data import into SQL

[dim]

Hi, does anyone know how to access the *.evt file (SysEvent.Evt) from SQL in order to import a data from a file into a server table?

When you run EventViewer there is an option to save the log file as *.csv file and then use it. I want to eliminate this step (or make it automatic) and get the data into SQL right away.

Any ideas?

Thx
Dim

[skhanal]

.EVT files are not in proper format to be directly read into SQL Server.

If you don't want to save the file as .CSV then you have to write a vb/vb script program to do the conversion and save it to a table.

[dim]

I'm looking now at the way to get the info using OLE Automation stored procedures. do you think I can succeed with this method? (haven't gone far in it so can't tell be myself).

Dim

[skhanal]

Look at WMI documentation on MSDN. I did not find a saveas method, but you should be able to query the log and insert into a table row by row.

[skhanal]

Good news for you. I found this in msdn,

It uses a DSN named EventLogs to connect to a database, you can directly connect to your database using connection string for sql server.

-------script starts here-----------

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "DSN=EventLogs;"
objRS.CursorLocation = 3
objRS.Open "SELECT * FROM EventTable" , objConn, 3, 3
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colRetrievedEvents = objWMIService.ExecQuery _
("Select * from Win32_NTLogEvent")
For Each objEvent in colRetrievedEvents
objRS.AddNew
objRS("Category") = objEvent.Category
objRS("ComputerName") = objEvent.ComputerName
objRS("EventCode") = objEvent.EventCode
objRS("Message") = objEvent.Message
objRS("RecordNumber") = objEvent.RecordNumber
objRS("SourceName") = objEvent.SourceName
objRS("TimeWritten") = objEvent.TimeWritten
objRS("Type") = objEvent.Type
objRS("User") = objEvent.User
objRS.Update
Next
objRS.Close
objConn.Close

[dim]

Thanks a lot! Will try it on tuesday next week (will post the result).
Tried so many different approaches to the problem but no luck so far!

Have a great long weekend, and thx again!

Dim

[dim]

Couldn't wait untill next week to try this solution. IT WORKED!!!

Thank you for your help!
Dim

[Ads]

I have never used OLE Automation stored procedures....
I do need to get event viewer data into a table or some format in sql...
Can somebody explain a little more how I use this script that skanal posted earlier?

Thanks a bunch!

[dim]

Hi, the script Skhanal provided is very much ready to go (no needs to do any mods), I just adjusted it to my own needs, here are the steps I've done to get a Log file data into SQL:
I have a server named TUNNEL with the log files and SQL server SDEV

1) Created VBS file with (text file with .vbs extension), see file "GetTunnelEventsScr.vbs" in a attached ZIP file.

2) Created "GetTunnelEventScr.bat" with an execution string ov mentioned abouve VBS file:

cscript \\sdev\REPLWORK\DBA_STATS\GetTunnelEventsScr.vbs //NoLogo

3) On SQL server:

created 2 tables with a stored procedure that uses xp_cmdshell sp to call my .bat file and get the data into "_TMP" table and then process the data from "_TMP" table into perm. table in order to get only non existing records (avoid duplications).

See the SQL script for tables and the SP in the ZIP file attached.

What you need to do is just change my servers names to the yours and run it!

Note: In my Stored procedure I do na extract of details from Event Message field (cause I'm intrested in the only one event #20048 that is "RemoteAccess", another words from the message below I extract and put into different columns Login name, login time, logout time, time spent, bites received and bites transfered, here is an example of the message I import - "The user KGB\SHTIRLIC connected on port VFG-007 on 05/15/2003 at 08:10pm and disconnected on
05/15/2003 at 08:18pm. The user was active for 7 minutes 30 seconds. 261833 bytes
were sent and 87440 bytes were received. The port speed was 10000000. The
reason for disconnecting was user request.
"

But if you need all the EVT messages then take this string manipulations out cause all the messages wil be different and you will get an error..)

Hope it helps,
Dim

[dim]

Found another solution for this problem, the method with WMI programing is good but it's very slow when you deal with a big even log file, so the solution is:
1) http://www.microsoft.com/technet/tre...ommandline.asp Here is explained how to use a command line to dump the evt file into flat file and then you can BCP/BULK INSERT the data into SQL.

2) Download from MS web site a utility called: dumpel.exe and
use it to dump evt file data into flat file and then BCP it into SQL (works with NT4/2000 as well)

Dim

[d20001000]

Your scripts work great!! there is only one thing i am having a problem with. Every time i run the Stored Procedure from the Query anaylizer, it only returns the the items in the System log and the Application log....not the items in the Security Log. Any Ideas on what is wrong. Oh yes, also when i manually run both the .vbs script and the .bat file from the command prompt, it works correctly (in importing all the Event Logs).

[dim]

Hi, even if I don't read a Security log file, I've tried to read the one just to test the script and it works great for me. It's hard to troubleshoot remotely your situation. It could be a permission settings. Did you try to read the log file from other/local Server/PC?

Dim

[d20001000]

No, i am not reading the log file from other/local Server/PC...However, I did a little more troubleshooting. Here is the situation:
When i Run the VBscript from the command prompt it works fine and imports everyting into the SQL database. When i run the VBScript from the xp_cmdshell SP, it returns everything except the entries in the Security log.
Now i am thinking that the log file might be too big to run inside of the xp_cmdshell SP....it might be timing out.
Does that sound right?

[dim]

No, the timeout is not an issue in this case for sure. The long run of the xp_cmdshell in my first approach of getting event log info into SLQ db was the reason why I started to use the "dumpel.exe" utility from MS web site instead of vb script. The xp_cmdshell sp usted to run for 5-9 hrs to get all the events from my System log file. So it's something else.

Dim