EventLog parser (WMI) and export to Excel vbscript

Today my need was very simple, I needed to build a script to collect logs in application event log on many XenApp servers. The goal with the following script it to use MFCom to get the target XenApp server list and export everything from the last 7 days and a specific keyword to an Excel file. This is very far to be perfect but it works and it did the job I needed 🙂

Let’s share !

Source code

Set objFSO = CreateObject("Scripting.FileSystemObject")
 
'Path and name of the Excel file
FileExport = "d:tempyourfile.xls"
'Name of your management Server (Must have MFCOM SDK if not a XenApp Server
CtxFarms = Array("XenappManagementserver.domain.inc")
'Folder in the XenApp Management Console, must begin with Servers/ ex : Servers/APPLICATION1
TargetPool = "Servers/ApplicatioForlder"
 
If (objFSO.FileExists(FileExport)) Then
	objFSO.DeleteFile(FileExport)
End If
 
Const CONVERT_TO_LOCAL_TIME = False
Set dtmStartDate = CreateObject("WbemScripting.SWbemDateTime")
Set dtmEndDate = CreateObject("WbemScripting.SWbemDateTime")
DateToCheck = CDate(Date)
'Number of day back to check here 7
dtmStartDate.SetVarDate DateToCheck - 7, CONVERT_TO_LOCAL_TIME
dtmEndDate.SetVarDate DateToCheck, CONVERT_TO_LOCAL_TIME
 
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add
Set objRange = objExcel.Range("A1","Z1")
objRange.Font.Size = 12
objRange.Font.Bold = True  
 
objExcel.Cells(1, 1).Value = "Server"
objExcel.Cells(1, 2).Value = "Event Source"
objExcel.Cells(1, 3).Value = "Event Type"
objExcel.Cells(1, 4).Value = "Event ID"
objExcel.Cells(1, 5).Value = "Event Message"
objExcel.Cells(1, 6).Value = "Event Date"
 
lign = 2
 
For i = 0 To UBound(CtxFarms)
	Set theFarm = CreateObject("MetaFrameCOM.MetaFrameFarm", "" & CtxFarms(i))
	theFarm.Initialize 1
 
	For Each aServer In theFarm.Servers
		If aServer.ParentFolderDN = TargetPool Then
			objExcel.Cells(lign, 1).Value = aServer.ServerName
			TargetSource = "Application"
			Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!" & aServer.ServerName & "rootcimv2")
 
'This line is to query the Security event log
'			Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where Logfile = 'Security' and SourceName = '" & TargetSource & "' and Type = 'Error' and TimeGenerated >= '" & dtmStartDate & "' and TimeGenerated < '" & dtmEndDate & "'")	

'This line is to query the System event log
'			Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where Logfile = 'System' and SourceName = '" & TargetSource & "' and Type = 'Error' and TimeGenerated >= '" & dtmStartDate & "' and TimeGenerated < '" & dtmEndDate & "'")	

'This line is to query the Application event log
			Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where Logfile = 'Application' and SourceName = '" & TargetSource & "' and Type = 'Error' and TimeGenerated >= '" & dtmStartDate & "' and TimeGenerated < '" & dtmEndDate & "'")
						For Each objEvent in colLoggedEvents
'                                   Change here to find event using a keyword in the main message
				MyPos = Instr(1, ucase(objEvent.Message), UCase("KeyWord"))
				If MyPos > 0 Then
					objExcel.Cells(lign, 1).Value = aServer.ServerName
					objExcel.Cells(lign, 2).Value = objEvent.SourceName
					objExcel.Cells(lign, 3).Value = objEvent.Type
					objExcel.Cells(lign, 4).Value = objEvent.EventCode
					objExcel.Cells(lign, 5).Value = objEvent.Message
					objExcel.Cells(lign, 6).Value = WMIDateStringToDate(objEvent.TimeGenerated)
					lign = lign + 1
				End If
			Next
		End If
	Next
Next
 
objExcel.ActiveWorkbook.SaveAs FileExport
objExcel.Quit
 
EnvoiMail()
 
Wscript.Quit
 
Function WMIDateStringToDate(dtmInstallDate)
    WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & "/" & Mid(dtmInstallDate, 7, 2) & "/" & Left(dtmInstallDate, 4) _
            & " " & Mid (dtmInstallDate, 9, 2) & ":" & Mid(dtmInstallDate, 11, 2) & ":" & Mid(dtmInstallDate, 13, 2))
End Function
 
Function EnvoiMail()
	Const SendUsingPort = 2
 
	' Set the variables used to send the message
	SMTPServer = "smtp.server.com"
	FromName = "administrator@admin.com"
	ToName = "me@admin.com" 
 
	' Setup the configuration for the message to use
	Set Conf = CreateObject("CDO.Configuration")
	With Conf.Fields
	    .Item("") = SendUsingPort
	    .Item("") = SMTPServer
	    .Update
	End With
 
	'Setup the message
	Set Message = CreateObject("CDO.Message")
	With Message
	Set .Configuration = Conf
 
    'Set email adress, subject And body
    	.To = ToName
    	.Subject = "Email Subject"
    	.TextBody = "Hello World !"
    	.From = FromName
    	.AddAttachment FileExport
    	.Send
	End With
 
End Function