Please Help in Adding 30 Day Window To The Following Script

[jasonh711]

I have the following script that will copy the System Event Logs to a
.xls spreadsheet; however, I need it to pull just the last 30 days wort
of data. Any assistance will be greatly appreciated. Thanks:


Code
-------------------

On Error Resume Next
strComputer = "."
strFile = "C:\System Event Logs.xls"

Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate,(Security)}!\\" & _
strComputer & "\root\cimv2")

Set colLoggedEvents = objWMIService.ExecQuery _
("Select * From Win32_NTLogEvent Where Logfile = 'System'")

'EventType Value = Meaning
' 1 = Error
' 2 = Warning
' 3 = Information
' 4 = Security Success
' 5 = Security Failure
' 8 = Security audit success
' 16 = Security audit failure

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.Workbooks.Add
objExcel.Cells(1,1).Value = "System Event log Errors+Warnings for " & strComputer
objExcel.Cells(1,1).Font.Bold = True
objExcel.Cells(1,1).Font.Size = 13
objExcel.Cells(1,1).Interior.ColorIndex = 11
objExcel.Cells(1,1).Interior.Pattern = 1 'xlSolid
objExcel.Cells(1,1).Font.ColorIndex = 2
objExcel.Cells(1,1).Borders.LineStyle = 1 '= xlSolid
objExcel.Cells(1,1).WrapText = True

objExcel.Cells(2,1).Value = "Time: " & Now
objExcel.Cells(2,1).Font.Bold = True
objExcel.Cells(2,1).Font.Size = 12
objExcel.Cells(2,1).Interior.ColorIndex = 11
objExcel.Cells(2,1).Interior.Pattern = 1 'xlSolid
objExcel.Cells(2,1).Font.ColorIndex = 2
objExcel.Cells(2,1).Borders.LineStyle = 1 '= xlSolid
objExcel.Cells(2,1).WrapText = True

objExcel.Cells(4,1).Value = "Time Generated"
objExcel.Cells(4,1).Font.Bold = True
objExcel.Cells(4,1).Font.Size = 11
objExcel.Cells(4,2).Value = "LogFile"
objExcel.Cells(4,2).Font.Bold = True
objExcel.Cells(4,2).Font.Size = 11
objExcel.Cells(4,3).Value = "Type"
objExcel.Cells(4,3).Font.Bold = True
objExcel.Cells(4,3).Font.Size = 11
objExcel.Cells(4,4).Value = "Event Code"
objExcel.Cells(4,4).Font.Bold = True
objExcel.Cells(4,4).Font.Size = 11
objExcel.Cells(4,5).Value = "Message"
objExcel.Cells(4,5).Font.Bold = True
objExcel.Cells(4,5).Font.Size = 11

x = 5
y = 1

For Each objEvent in colLoggedEvents

If objEvent.Type="error" or objEvent.Type="warning" Then

strTimeGen = (evtdatetime(objEvent.TimeGenerated))
strLogfile = objEvent.Logfile
strType = objEvent.Type
strEventCode = objEvent.EventCode
srtMessage = Trim( Replace( objEvent.Message, vbCrLf, " "))

y1 = y
objExcel.Cells(x,y1).Value = strTimeGen
y1 = y1 + 1
objExcel.Cells(x,y1).Value = strLogfile
y1 = y1 + 1
objExcel.Cells(x,y1).Value = strType
y1 = y1 + 1
objExcel.Cells(x,y1).Value = strEventCode
y1 = y1 + 1
objExcel.Cells(x,y1).Value = srtMessage

x = x + 1

End If
Next

objExcel.Columns("A:E").Select
objExcel.Selection.HorizontalAlignment = 1 'xlLeft
objExcel.Selection.Borders.LineStyle = 1 '= xlSolid

objExcel.Range("A1","E1").MergeCells = 1
objExcel.Range("A2","E2").MergeCells = 1

objExcel.Columns("A:AH").EntireColumn.AutoFit

objExcel.DisplayAlerts = False
Set objWorkbook = objExcel.ActiveWorkbook
objWorkbook.SaveAs strFile
objWorkbook.Close
objExcel.Quit

msgbox "System Export Complete!"

Function evtdatetime(evttime)
Dim tmGen, dtPart,tmPart
tmGen = Left(evttime,14)
dtPart = Left(tmGen,8)
tmPart = Right(tmGen,6)
evtdatetime = Left(dtPart,4) & "/" & Mid(dtPart,5,2) & "/" & Right(dtPart,2) &","& _
Left(tmPart,2) & ":" & Mid(tmPart,3,2) & ":" & Right(tmPart,2)
End Function

-------------------

--
jasonh71
-----------------------------------------------------------------------
jasonh711's Profile: http://forums.techarena.in/members/137625.ht
View this thread: http://forums.techarena.in/server-scripting/1249810.ht

http://forums.techarena.i

[Tom Lavedas]

See comments in code below

I have the following script that will copy the System Event Logs to an
.xls spreadsheet; however, I need it to pull just the last 30 days worth
--------------------
  On Error Resume Next
  strComputer = "."
  strFile = "C:\System Event Logs.xls"
  Set objWMIService = GetObject("winmgmts:" _
  & "{impersonationLevel=impersonate,(Security)}!\\" & _
  strComputer & "\root\cimv2")

Set dtmStartDate = CreateObject("WbemScripting.SWbemDateTime")
dtmStartDate.SetVarDate dateadd("d", -30, now)' CONVERT_TO_LOCAL_TIME

  Set colLoggedEvents = objWMIService.ExecQuery _
  ("Select * From Win32_NTLogEvent Where Logfile = 'System'" _
& "TimeWritten > '" & dtmStartDate & "'"")

{snip}

_____________________
Tom Lavedas

[jasonh711]

Thanks Tom for the assistance; however, I'm coming across the sam
problem that I encountered while attempting this prior to the pos
(there is no output in the .xls). Here is the updated script. Please le
me know if you have a different result than myself. Thanks.

Code
-------------------

On Error Resume Next
strComputer = "."
strFile = "C:\System Event Logs.xls"

Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate,(Security)}!\\" & _
strComputer & "\root\cimv2")

Set dtmStartDate = CreateObject("WbemScripting.SWbemDateTime")
dtmStartDate.SetVarDate dateadd("d", -30, now)' CONVERT_TO_LOCAL_TIME

Set colLoggedEvents = objWMIService.ExecQuery _
("Select * From Win32_NTLogEvent Where Logfile = 'System'"_
& "TimeWritten > '" & dtmStartDate & "'")


'EventType Value = Meaning
' 1 = Error
' 2 = Warning
' 3 = Information
' 4 = Security Success
' 5 = Security Failure
' 8 = Security audit success
' 16 = Security audit failure

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.Workbooks.Add
objExcel.Cells(1,1).Value = "System Event log Errors+Warnings for " & strComputer
objExcel.Cells(1,1).Font.Bold = True
objExcel.Cells(1,1).Font.Size = 13
objExcel.Cells(1,1).Interior.ColorIndex = 11
objExcel.Cells(1,1).Interior.Pattern = 1 'xlSolid
objExcel.Cells(1,1).Font.ColorIndex = 2
objExcel.Cells(1,1).Borders.LineStyle = 1 '= xlSolid
objExcel.Cells(1,1).WrapText = True

objExcel.Cells(2,1).Value = "Time: " & Now
objExcel.Cells(2,1).Font.Bold = True
objExcel.Cells(2,1).Font.Size = 12
objExcel.Cells(2,1).Interior.ColorIndex = 11
objExcel.Cells(2,1).Interior.Pattern = 1 'xlSolid
objExcel.Cells(2,1).Font.ColorIndex = 2
objExcel.Cells(2,1).Borders.LineStyle = 1 '= xlSolid
objExcel.Cells(2,1).WrapText = True

objExcel.Cells(4,1).Value = "Time Generated"
objExcel.Cells(4,1).Font.Bold = True
objExcel.Cells(4,1).Font.Size = 11
objExcel.Cells(4,2).Value = "LogFile"
objExcel.Cells(4,2).Font.Bold = True
objExcel.Cells(4,2).Font.Size = 11
objExcel.Cells(4,3).Value = "Type"
objExcel.Cells(4,3).Font.Bold = True
objExcel.Cells(4,3).Font.Size = 11
objExcel.Cells(4,4).Value = "Event Code"
objExcel.Cells(4,4).Font.Bold = True
objExcel.Cells(4,4).Font.Size = 11
objExcel.Cells(4,5).Value = "Message"
objExcel.Cells(4,5).Font.Bold = True
objExcel.Cells(4,5).Font.Size = 11

x = 5
y = 1

For Each objEvent in colLoggedEvents

If objEvent.Type="error" or objEvent.Type="warning" Then

strTimeGen = (evtdatetime(objEvent.TimeGenerated))
strLogfile = objEvent.Logfile
strType = objEvent.Type
strEventCode = objEvent.EventCode
srtMessage = Trim( Replace( objEvent.Message, vbCrLf, " "))

y1 = y
objExcel.Cells(x,y1).Value = strTimeGen
y1 = y1 + 1
objExcel.Cells(x,y1).Value = strLogfile
y1 = y1 + 1
objExcel.Cells(x,y1).Value = strType
y1 = y1 + 1
objExcel.Cells(x,y1).Value = strEventCode
y1 = y1 + 1
objExcel.Cells(x,y1).Value = srtMessage

x = x + 1

End If
Next

objExcel.Columns("A:E").Select
objExcel.Selection.HorizontalAlignment = 1 'xlLeft
objExcel.Selection.Borders.LineStyle = 1 '= xlSolid

objExcel.Range("A1","E1").MergeCells = 1
objExcel.Range("A2","E2").MergeCells = 1

objExcel.Columns("A:AH").EntireColumn.AutoFit

objExcel.DisplayAlerts = False
Set objWorkbook = objExcel.ActiveWorkbook
objWorkbook.SaveAs strFile
objWorkbook.Close
objExcel.Quit

msgbox "System Export Complete!"

Function evtdatetime(evttime)
Dim tmGen, dtPart,tmPart
tmGen = Left(evttime,14)
dtPart = Left(tmGen,8)
tmPart = Right(tmGen,6)
evtdatetime = Left(dtPart,4) & "/" & Mid(dtPart,5,2) & "/" & Right(dtPart,2) &","& _
Left(tmPart,2) & ":" & Mid(tmPart,3,2) & ":" & Right(tmPart,2)
End Function

-------------------

--
jasonh71
-----------------------------------------------------------------------
jasonh711's Profile: http://forums.techarena.in/members/137625.htm
View this thread: http://forums.techarena.in/server-scripting/1249810.htm

http://forums.techarena.in

[Tom Lavedas]

Thanks Tom for the assistance; however, I'm coming across the same
problem that I encountered while attempting this prior to the post
(there is no output in the .xls). Here is the updated script. Please let
me know if you have a different result than myself. Thanks.

{snip}

Sorry, I forgot the concatenation word 'and' and a space. Try ...

Set colLoggedEvents = objWMIService.ExecQuery _
("Select * From Win32_NTLogEvent Where Logfile = 'System' and "_
& "TimeWritten > '" & dtmStartDate & "'")

BTW, it is nearly impossible to find problems when debugging when the
first line of the script is 'On Error Resume Next', since the real
problem is never reported. I almost never use it - and never as a
blanket covering the whole script. If it is used the script code must
handle expected errors or it makes it impossible when the script fails
to figure out why.
_____________________
Tom Lavedas

[jasonh711]

Thanks Tom for the help! That worked perfectly

--
jasonh71
-----------------------------------------------------------------------
jasonh711's Profile: http://forums.techarena.in/members/137625.ht
View this thread: http://forums.techarena.in/server-scripting/1249810.ht

http://forums.techarena.i

[Tom Lavedas]

Thanks Tom for the help! That worked perfectly.
--
jasonh711
------------------------------------------------------------------------
jasonh711's Profile:http://forums.techarena.in/members/137625.htm
View this thread:http://forums.techarena.in/server-scripting/1249810.htm
http://forums.techarena.in

You're welcome. Glad it got corrected.
_____________________
Tom Lavedas