Writing Event Logs to SQL Database

[Brycspain]

I'm trying to copy event logs from all of my servers to a centralized database and I'm having problems transferring the date/time fields and was wondering what any of you could do to get me started in the right direction.

I'm using the standard:

objRS("TimeWritten") = objEvent.Timewritten

On my SQL table I have a field called Timewritten and I'm using the datetime data type however, this field is only 8 characters in length so I get the standard error you receive when you try to fit too much data into too small of a field.

Any suggestions?

 

[markdmac]

Bryc, can you echo out what is stored in objEvent.Timewritten and post that information?

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[barny2006]

this will do what you want:

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

 

[Brycspain]

Mark,

I tried to echo it out but it's not returning any value whatsoever. In the event log there are actually 2 fields...one for date and one for time. The date/time field in SQL only holds 8 characters so that field cannot hold both entries and therefore will not return anything. What I need to know is: Does anyone know more about this particular line of code and how it interacts with SQL fields?

objRS("TimeWritten") = objEvent.TimeWritten

Barney,

Thanks for the code but that's what I'm using now....the problem is, I get an error on the above line because the SQL field is too small to hold both the date and the time. I was trying to understand this dynamic and was asking for help from the gurus.

 

[Brycspain]

Mark,

Here is the output:

20060911072619.000000-300

Hope this helps.

 

[markdmac]

OK Bryc, now we are getting somewhere. This is a date/time that is in UTC.

You can easily grab the date with

objRS("TimeWritten") = Left(objEvent.TimeWritten,8)

If you want the time then you will need to convert the UTC. Let me know if that is your goal.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[Brycspain]

Thanks Mark,

Yes I do need the time as well thanks.

Bryc

 

[barny2006]

you might have to use a dbl precision field to hold this value. since it can be retrieved and like mark said, can be parsed to extract date (yr,mo,day) and time (hr,min,sec), etc.

 

[markdmac]

OK, so here is a function you can work with will return the date & time for you from your value you already have.

thisDateTime = "20060911072619.000000-300"


WScript.Echo dConvertWMItoVBSDate(thisDateTime)


JustTime = Hour(dConvertWMItoVBSDate(thisTime)) & ":" & Minute(dConvertWMItoVBSDate(thisTime)) &":" & Second(dConvertWMItoVBSDate(thisTime))

WScript.Echo JustTime


Private Function dConvertWMItoVBSDate(sDate)
  Dim sMonth, sDay, sYear, sHour, sMinutes, sSeconds
  sMonth = Mid(sDate,5,2)
  sDay = Mid(sDate,7,2)
  sYear = Mid(sDate,1,4)
  sHour = Mid(sDate,9,2)
  sMinutes = Mid(sDate,11,2)
  sSeconds = Mid(sDate,13,2)
  dConvertWMItoVBSDate = DateSerial (sYear, sMonth, sDay) + TimeSerial (sHour, sMinutes, sSeconds)
End Function

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[markdmac]

Sorry scratch the above, I changed a variable name and forgot to change it everywhere:

thisDateTime = "20060911072619.000000-300"


WScript.Echo dConvertWMItoVBSDate(thisDateTime)


JustTime = Hour(dConvertWMItoVBSDate(thisDateTime)) & ":" & Minute(dConvertWMItoVBSDate(thisDateTime)) &":" & Second(dConvertWMItoVBSDate(thisDateTime))

WScript.Echo JustTime


Private Function dConvertWMItoVBSDate(sDate)
  Dim sMonth, sDay, sYear, sHour, sMinutes, sSeconds
  sMonth = Mid(sDate,5,2)
  sDay = Mid(sDate,7,2)
  sYear = Mid(sDate,1,4)
  sHour = Mid(sDate,9,2)
  sMinutes = Mid(sDate,11,2)
  sSeconds = Mid(sDate,13,2)
  dConvertWMItoVBSDate = DateSerial (sYear, sMonth, sDay) + TimeSerial (sHour, sMinutes, sSeconds)
End Function

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[Brycspain]

Mark,

Thanks for the function. The only problem I see with it is the script runs through each line of the event log and therefore the "thisDateTime" value will be different with each line of the event log. Barney posted the script I'm using above. I was only showing you the format of what was being returned with the TimeWritten line of the code.

Hope this helps,

Bryc

 

[markdmac]

To insert this into your script you would do something like this:

objRS("TimeWritten") = dConvertWMItoVBSDate(objEvent.TimeWritten)



Private Function dConvertWMItoVBSDate(sDate)
  Dim sMonth, sDay, sYear, sHour, sMinutes, sSeconds
  sMonth = Mid(sDate,5,2)
  sDay = Mid(sDate,7,2)
  sYear = Mid(sDate,1,4)
  sHour = Mid(sDate,9,2)
  sMinutes = Mid(sDate,11,2)
  sSeconds = Mid(sDate,13,2)
  longDateTime = DateSerial (sYear, sMonth, sDay) + TimeSerial (sHour, sMinutes, sSeconds)
  dConvertWMItoVBSDate = Hour(longDateTime) & ":" & Minute(longDateTime) & ":" & Second(longDateTime)
End Function

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

 

[Brycspain]

I need another field on my database to place the time in as well. Is there another command I can use similar to "Timewritten" to use with this function? Is "TimeGenerated" acceptable and how would that integrate into the function?

 

[markdmac]

objRS("TimeWritten") = dConvertWMItoVBSTime(objEvent.TimeWritten)
objRS("DateWritten") = dConvertWMItoVBSDate(objEvent.TimeWritten)

Private Function dConvertWMItoVBSTime(sDate)
  Dim sMonth, sDay, sYear, sHour, sMinutes, sSeconds
  sMonth = Mid(sDate,5,2)
  sDay = Mid(sDate,7,2)
  sYear = Mid(sDate,1,4)
  sHour = Mid(sDate,9,2)
  sMinutes = Mid(sDate,11,2)
  sSeconds = Mid(sDate,13,2)
  longDateTime = DateSerial (sYear, sMonth, sDay) + TimeSerial (sHour, sMinutes, sSeconds)
  dConvertWMItoVBSTime = Hour(longDateTime) & ":" & Minute(longDateTime) & ":" & Second(longDateTime)
End Function



Private Function dConvertWMItoVBSDate(sDate)
  Dim sMonth, sDay, sYear, sHour, sMinutes, sSeconds
  sMonth = Mid(sDate,5,2)
  sDay = Mid(sDate,7,2)
  sYear = Mid(sDate,1,4)
  sHour = Mid(sDate,9,2)
  sMinutes = Mid(sDate,11,2)
  sSeconds = Mid(sDate,13,2)
  longDateTime = DateSerial (sYear, sMonth, sDay) + TimeSerial (sHour, sMinutes, sSeconds)
  dConvertWMItoVBSDate = Left(longDateTime,8)
End Function

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript