Announcement

Collapse
No announcement yet.

Event Logs Script

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Event Logs Script

    Hello,

    I was searching the forums for a script to get the event logs of server. I came across this one that Rems has modified and works very nicely:

    http://forums.petri.com/showthread.p...ght=event+logs

    What I am trying to do...is to just get the "errors" and "warnings" from the event logs, and put them in an excel spreadsheet. Also...eventually when we figure out what events ID's we'd like the weed out, have only those specified in the script. It won't be like that at first...but just to have the option there would be nice. I found some from searching that will just find particular event ID's, but not multiple ones.

    Is this feasible?

    Thanks to anyone who can help.

  • #2
    Re: Event Logs Script

    I have found a couple solutions that a pretty nice:

    http://www.experts-exchange.com/OS/M..._21769395.html
    http://cwashington.netreach.net/depo...tType=vbscript

    The excel file that gets generated for the first link is a little sloppy. The second link with the HTA application is very nice...so I might work with that one. I haven't tested it with remote PC's yet though.

    Let me know if anyone else finds anything useful.

    Comment


    • #3
      Re: Event Logs Script

      I am trying to get the script from the first post with Rems to read from a list of servers in a text file...but
      Code:
      strArchiveFolder = "C:\Event Logs"
      seems to place the .evt's on the remote machine. I tried using:
      Code:
      strArchiveFolder = "\\servername\C$\Event Logs"
      but that did not work as well. Here is the full code:

      Code:
      strFile = "C:\Event Logs\serverlist.txt"
      strFolder = "C:\Event Logs"
      
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      Set objTS = objFSO.OpenTextFile(strFile)
      
      Do Until objTS.AtEndOfStream
          strServer = objTS.ReadLine
      
      '-------------------------------------------------------------------
      
      strArchiveFolder = "C:\Event Logs"
      Set WS = CreateObject("Wscript.Shell")
      Set FSO = CreateObject("Scripting.FileSystemObject")
      
      DateString = CurrentDate()
      
      Purge = False   'True = clears currrent logs
      
      On Error Resume Next
      Set objWMIService = GetObject("winmgmts:" _
           & "{impersonationLevel=impersonate,(Backup)}!\\" & _
           strServer & "\root\cimv2")
      If Err.Number = 0 Then
        Set colLogs = objWMIService.ExecQuery("select * from Win32_NTEventLogFile",,48)
      
        For Each refLog In colLogs
             LogName = strServer& "_" & LogFileName(refLog.LogFileName) & _
             "_" & DateString
      
        If FSO.FileExists(strArchiveFolder & "\" & LogName & ".evt") Then _
           FSO.DeleteFile(strArchiveFolder & "\" & LogName & ".evt")
        If Purge Then
            RetVal = reflog.ClearEventlog(strArchiveFolder & "\" & LogName & ".evt")
        Else
             RetVal = reflog.BackupEventlog(strArchiveFolder & "\" & LogName & ".evt")
        End If
           If RetVal = 0 Then
              Output vbTab & "Log was archived in .evt format: " & LogName & ".evt"
               If Purge Then Output vbTab & "All events were cleared from the log"
           Else
              Output vbTab & "Error while archiving in .evt format."
           End If
        Next
      Else
        Output vbTab & "Failed connect to the server"
      End If
      Set colLogs = Nothing
      Set refLogs = Nothing
      Set objWMIService = Nothing
      
      Loop
      objTS.Close
      
      Set WS = Nothing
      Set FullLog = Nothing
      Set FSO = Nothing
      
      msgbox "Script Complete!"
      
      WScript.Quit(0)
      
      
      Function CurrentDate
        Today = Date
        If Month(Today) < 10 Then
          CurrentDate = "0" & CStr(Month(Today))
        Else
          CurrentDate = CStr(Month(Today))
        End If
        If Day(Today) < 10 Then
          CurrentDate = CurrentDate & "0" & CStr(Day(Today))
        Else
          CurrentDate = CurrentDate & CStr(Day(Today))
        End If
        CurrentDate = CurrentDate & CStr(Year(Today))
        If Hour(Time) < 10 Then
          CurrentDate = CurrentDate & "0" & CStr(Hour(Time))
        Else
          CurrentDate = CurrentDate & CStr(Hour(Time))
        End If
      End Function
      
      Function LogFileName(LogName)
        Select Case LogName
          'Case "Application"
                 'LogFileName = "app"
          'Case "Directory Service"
                 'LogFileName = "dir"
          'Case "DNS Server"
                 'LogFileName = "dns"
          'Case "File Replication Service"
                 'LogFileName = "rep"
          'Case "Security"
                 'LogFileName = "sec"
          Case "System"
                 LogFileName = "sys"
        End Select
      End Function

      Comment


      • #4
        Re: Event Logs Script

        If you want to export events to excel- or a textfile do not use the BackupEventlog Method of the Win32_NTEventlogFile Class --> use: Win32_NTLogEvent to query.

        http://www.google.com/custom?hl=nl&c...ch=Petri.co.il

        \Rems
        Last edited by Rems; 10th April 2008, 00:14.

        This posting is provided "AS IS" with no warranties, and confers no rights.

        __________________

        ** Remember to give credit where credit's due **
        and leave Reputation Points for meaningful posts

        Comment


        • #5
          Re: Event Logs Script

          Thanks Rems!

          Comment


          • #6
            Re: Event Logs Script

            Ok, so I have excel working thanks to that new information, but the event types that I have specified to only pull, are not working. It looks like its getting every event and not just the "errors" and "warning" specified by EventType 1 and 2.

            Code:
            On Error Resume Next
            strComputer = "."
            strFile = "C:\Event Logs\Event Logs.xls"
            
            Set objWMIService = GetObject("winmgmts:" _
                & "{impersonationLevel=impersonate,(Security)}!\\" & _
                    strComputer & "\root\cimv2")
            
            Set colLoggedEvents = objWMIService.ExecQuery _
                ("Select * From Win32_NTLogEvent Where Logfile = 'System' AND EventType <> 1 AND EventType <> 2")
            
            '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+Warning 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
            
            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
            
            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 "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

            Comment


            • #7
              Re: Event Logs Script

              Figured it out...had to set the event types in the For/Next, and they have to be lower case:

              Code:
              On Error Resume Next
              strComputer = "."
              strFile = "C:\Event Logs\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 "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

              Comment


              • #8
                Re: Event Logs Script

                Nice script ekrengel,
                A good contribution to this form and helpfull to others.

                about your last script,
                I think it is better to use WQL though,
                because in the last script now every event is brought to the For_each loop.

                Note these differences in your two last scripts:
                In the the script before the one above, this was basically your filter:

                EventType NotEquals error AND EventType NotEquals warning

                ~then the conditions you used in your last script:
                objEvent.Type Equals error OR objEvent.Type Equals warning


                Just change the WQL to:
                Code:
                ("Select * From Win32_NTLogEvent Where Logfile = 'System' AND (EventType = 1 OR EventType = 2)")

                \Rems

                This posting is provided "AS IS" with no warranties, and confers no rights.

                __________________

                ** Remember to give credit where credit's due **
                and leave Reputation Points for meaningful posts

                Comment


                • #9
                  Re: Event Logs Script

                  Oops...thanks. Yeah that would do it lol.

                  Comment


                  • #10
                    Re: Event Logs Script

                    I just made this a little more complicated...added reading it from a list of computers, which it has no problem...but where the issue comes along is creating new sheets in excel for every computer name. I tried using a counter...but it doesn't go past the second machine, and it only retains the name of the first machine. The sheets section is in green:

                    EDIT** - Put "intSheet = 1" before the loop, or else it keeps resetting its value to 1, and the highest value you can get is 2.
                    EDIT #2**- Added a statement for the sheets if you have more then 3 computers in the text file...needs some work though.

                    Code:
                    On Error Resume Next
                    
                    strFile = "C:\Event Logs\serverlist.txt"
                    strFolder = "C:\Event Logs"
                    
                    Set objFSO = CreateObject("Scripting.FileSystemObject")
                    Set objTS = objFSO.OpenTextFile(strFile)
                    
                    Set objExcel = CreateObject("Excel.Application")
                    objExcel.Visible = False
                    objExcel.Workbooks.Add
                    
                    intSheet = 1 'counter for sheets
                    
                    Do Until objTS.AtEndOfStream
                        strComputer = objTS.ReadLine
                    
                    '-------------------------------------------------------------------
                    
                    Set objWMIService = GetObject("winmgmts:" _
                        & "{impersonationLevel=impersonate,(Security)}!\\" & _
                            strComputer & "\root\cimv2")
                    
                    Set colLoggedEvents = objWMIService.ExecQuery _
                        ("Select * From Win32_NTLogEvent Where Logfile = 'System' AND (EventType = 1 OR EventType = 2)")
                    
                    'EventType Value = Meaning
                    ' 1 = Error
                    ' 2 = Warning
                    ' 3 = Information
                    ' 4 = Security Success
                    ' 5 = Security Failure
                    ' 8 = Security audit success
                    ' 16 = Security audit failure
                    
                    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(3,1).Value = "Time Generated"
                    objExcel.Cells(3,1).Font.Bold = True
                    objExcel.Cells(3,1).Font.Size = 11
                    objExcel.Cells(3,1).Interior.ColorIndex = 24
                    objExcel.Cells(3,2).Value = "LogFile"
                    objExcel.Cells(3,2).Font.Bold = True
                    objExcel.Cells(3,2).Font.Size = 11
                    objExcel.Cells(3,2).Interior.ColorIndex = 24
                    objExcel.Cells(3,3).Value = "Type"
                    objExcel.Cells(3,3).Font.Bold = True
                    objExcel.Cells(3,3).Font.Size = 11
                    objExcel.Cells(3,3).Interior.ColorIndex = 24
                    objExcel.Cells(3,4).Value = "Event Code"
                    objExcel.Cells(3,4).Font.Bold = True
                    objExcel.Cells(3,4).Font.Size = 11
                    objExcel.Cells(3,4).Interior.ColorIndex = 24
                    objExcel.Cells(3,5).Value = "Message"
                    objExcel.Cells(3,5).Font.Bold = True
                    objExcel.Cells(3,5).Font.Size = 11
                    objExcel.Cells(3,5).Interior.ColorIndex = 24
                    
                    x = 4
                    y = 1
                    
                    For Each objEvent in colLoggedEvents
                    
                    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
                    
                    	If strType = "warning" Then 
                    	  objExcel.Cells(x,y1).Interior.ColorIndex = 6
                    	  objExcel.Cells(x,y1).Font.Bold = True
                    	End If
                    
                    	If strType = "error" Then
                    	  objExcel.Cells(x,y1).Font.ColorIndex = 2
                    	  objExcel.Cells(x,y1).Interior.ColorIndex = 3
                    	  objExcel.Cells(x,y1).Font.Bold = True
                    	End If
                    
                          y1 = y1 + 1
                          objExcel.Cells(x,y1).Value = strEventCode
                          y1 = y1 + 1
                          objExcel.Cells(x,y1).Value = srtMessage
                      x = x + 1
                    
                    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.Range("C:C").Replace "warning", "Warning"
                    objExcel.Range("C:C").Replace "error", "Error"
                    
                    objExcel.Columns("A:AH").EntireColumn.AutoFit
                    
                       objExcel.Sheets("Sheet" & intSheet).Select  'select current sheet
                       objExcel.Sheets("Sheet" & intSheet).Name = strComputer 'rename current sheet as the computername
                    
                         intSheet = intSheet + 1 'counter to add next sheet
                    
                         	If intSheet >= 4 Then
                    	objExcel.Sheets.Add
                    	End If
                    
                             objExcel.Sheets("Sheet" & intSheet).Select  'go to the next sheet
                    
                    Loop
                    
                    objTS.Close
                    
                    objExcel.DisplayAlerts = False
                    objExcel.ActiveWorkbook.SaveAs("C:\Event Logs\Event Logs" & " " & Month(now) & "-" & Day(now) & "-" & Year(now) & ".xlsx")
                    objExcel.Quit
                    
                    msgbox "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
                    Last edited by ekrengel; 14th April 2008, 00:45.

                    Comment


                    • #11
                      Re: Event Logs Script

                      OK! So thanks to scripting answers, I have made some progress with this. The only issue that still exists, is that it duplicates the last computer name in the list with a new sheet, and does not format the "warning" and "error" colors in the sheet:

                      EDIT ** Correction, does not add colors after the 3rd sheet.

                      http://www.scriptinganswers.com/vaul...nd%20Printers/
                      -print server details

                      Code:
                      On Error Resume Next
                      
                      strFile = "C:\Event Logs\serverlist.txt"
                      strFolder = "C:\Event Logs"
                      
                      intSheet = 1 'counter for sheets
                      
                      Set objExcel = CreateObject("Excel.Application")
                      
                      ' Set the new workbook to have one sheet initially, in case there's just one server in the list
                      objExcel.SheetsInNewWorkbook = 1
                      
                      objExcel.Visible = False
                      objExcel.Workbooks.Add
                      
                      Set objFSO = CreateObject("Scripting.FileSystemObject")
                      Set objTS = objFSO.OpenTextFile(strFile)
                      
                      Do Until objTS.AtEndOfStream
                          strComputer = objTS.ReadLine
                      
                      '-------------------------------------------------------------------
                      
                      Set objWMIService = GetObject("winmgmts:" _
                          & "{impersonationLevel=impersonate,(Security)}!\\" & _
                              strComputer & "\root\cimv2")
                      
                      Set colLoggedEvents = objWMIService.ExecQuery _
                          ("Select * From Win32_NTLogEvent Where Logfile = 'System' AND (EventType = 1 OR EventType = 2)")
                      
                        ' Check to see if this is the first sheet of the workbook
                        If intSheet = 1 Then
                          Set objSheet = objExcel.ActiveWorkbook.Worksheets(intSheet)
                          objSheet.Name = strComputer
                        Else
                        'Add a new sheet to the workbook
                          intCount = objExcel.Sheets.Count
                          objExcel.ActiveWorkbook.Sheets.Add,objExcel.sheets(intCount)
                          Set objSheet = objExcel.ActiveWorkbook.Worksheets(intSheet)
                          objSheet.Name = strComputer
                        End If
                      
                      'EventType Value = Meaning
                      ' 1 = Error
                      ' 2 = Warning
                      ' 3 = Information
                      ' 4 = Security Success
                      ' 5 = Security Failure
                      ' 8 = Security audit success
                      ' 16 = Security audit failure
                      
                      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(3,1).Value = "Time Generated"
                      objExcel.Cells(3,1).Font.Bold = True
                      objExcel.Cells(3,1).Font.Size = 11
                      objExcel.Cells(3,1).Interior.ColorIndex = 24
                      objExcel.Cells(3,2).Value = "LogFile"
                      objExcel.Cells(3,2).Font.Bold = True
                      objExcel.Cells(3,2).Font.Size = 11
                      objExcel.Cells(3,2).Interior.ColorIndex = 24
                      objExcel.Cells(3,3).Value = "Type"
                      objExcel.Cells(3,3).Font.Bold = True
                      objExcel.Cells(3,3).Font.Size = 11
                      objExcel.Cells(3,3).Interior.ColorIndex = 24
                      objExcel.Cells(3,4).Value = "Event ID"
                      objExcel.Cells(3,4).Font.Bold = True
                      objExcel.Cells(3,4).Font.Size = 11
                      objExcel.Cells(3,4).Interior.ColorIndex = 24
                      objExcel.Cells(3,5).Value = "Message"
                      objExcel.Cells(3,5).Font.Bold = True
                      objExcel.Cells(3,5).Font.Size = 11
                      objExcel.Cells(3,5).Interior.ColorIndex = 24
                      
                      x = 4
                      y = 1
                      
                      For Each objEvent in colLoggedEvents
                      
                      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
                      
                      	If strType = "warning" Then 
                      	  objExcel.Cells(x,y1).Interior.ColorIndex = 6
                      	  objExcel.Cells(x,y1).Font.Bold = True
                      	End If
                      
                      	If strType = "error" Then
                      	  objExcel.Cells(x,y1).Font.ColorIndex = 2
                      	  objExcel.Cells(x,y1).Interior.ColorIndex = 3
                      	  objExcel.Cells(x,y1).Font.Bold = True
                      	End If
                      
                            y1 = y1 + 1
                            objExcel.Cells(x,y1).Value = strEventCode
                            y1 = y1 + 1
                            objExcel.Cells(x,y1).Value = srtMessage
                        x = x + 1
                      
                      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.Range("C:C").Replace "warning", "Warning"
                      objExcel.Range("C:C").Replace "error", "Error"
                      
                      objExcel.Columns("A:AH").EntireColumn.AutoFit
                      
                      intSheet = intSheet + 1 'counter to add next sheet
                      
                      Loop
                      
                      objTS.Close
                      
                      objExcel.DisplayAlerts = False
                      objExcel.SheetsInNewWorkbook = 3 ' Reset Excel application's SheetsInNewWorkbook to default
                      objExcel.ActiveWorkbook.SaveAs("C:\Event Logs\Event Logs" & " " & Month(now) & "-" & Day(now) & "-" & Year(now) & ".xlsx")
                      objExcel.Quit
                      
                      msgbox "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
                      Last edited by ekrengel; 14th April 2008, 21:14.

                      Comment

                      Working...
                      X