Stéphane Thirion
  • Home
  • Consulting – Raidho
  • homelab
3K
0
0
0
Stéphane Thirion
Stéphane Thirion
  • Home
  • Consulting – Raidho
  • homelab
  • Citrix
  • Microsoft
  • Windows 2003
  • XenApp

EventLog parser (WMI) and export to Excel vbscript

  • June 11, 2010
  • Stephane Thirion
Total
0
Shares
0
0
0
0
0
0
0

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

At the end I added a function to send this Excel file by email so I can schedule this check and genrerate this report.

Total
0
Shares
Tweet 0
Share 0
Share 0
Share 0
Share 0
Share 0
Share 0
Related Topics
  • eventlog
  • excel
  • export
  • parser
  • vbscript
  • wmi
  • XenApp
Stephane Thirion

Previous Article
  • ArchY.net Site
  • Citrix

Frankfurt 2010 – PubForum

  • June 3, 2010
  • Stephane Thirion
View Post
Next Article
  • Citrix
  • Microsoft
  • WebInterface
  • Windows 2003
  • Windows 2008
  • XenApp

Remote Desktop published application – Double upper case issue

  • June 30, 2010
  • Stephane Thirion
View Post
You May Also Like
View Post
  • Active Directory
  • ADC
  • Citrix
  • Citrix Virtual Apps and Desktops
  • DaaS
  • Microsoft
  • NetScaler
  • Security

Netscaler native OTP Active Directory account delegation

  • Stephane Thirion
  • March 22, 2023
View Post
  • ADC
  • Citrix
  • NetScaler
  • SDX
  • Uncategorized

Invalid time in the Message sent by the Peer. Please ensure time synchronization between Netscaler and the Peer

  • Stephane Thirion
  • March 3, 2023
View Post
  • ADC
  • Citrix
  • NetScaler
  • SDX

Netscaler SDX LACP on 0/1 and 0/2 (Management Interfaces)

  • Stephane Thirion
  • March 2, 2023
View Post
  • Citrix
  • Citrix Virtual Apps and Desktops
  • Cloud
  • DaaS
  • Microsoft
  • PowerShell
  • VMware
  • vSphere

Create vmware service account for MCS Citrix

  • Stephane Thirion
  • February 28, 2023
View Post
  • Citrix
  • CTP
  • Uncategorized

This is the end of an era

  • Stephane Thirion
  • February 16, 2022
View Post
  • VMware
  • vSphere
  • Windows 2022

Migrating FSMO roles Windows 2022 Server

  • Stephane Thirion
  • January 3, 2022
View Post
  • ADC
  • Experience
  • Linux

Cloud yes but no, thanks (there is some Citrix)

  • Stephane Thirion
  • December 7, 2021
View Post
  • Azure
  • Azure
  • PowerShell
  • Scripting
  • Security

Export all Admin Roles and members from Azure AD

  • Rodolphe Herpeux
  • March 25, 2021
vmware
Binance – Affiliated link
Coinbase – Affiliated link
Blog Stats
  • 1,239,235 hits
Categories
  • Amazon (1)
  • Apple (20)
    • iOS (5)
    • Mac OSx (11)
  • ArchY.net Site (30)
  • Azure (8)
  • Certifications (3)
  • Citrix (211)
    • ADC (4)
    • Citrix Virtual Apps and Desktops (5)
    • DaaS (2)
    • NetScaler (15)
    • Password Manager (3)
    • Personal vDisk (5)
    • Power and Capacity Management (3)
    • Provisioning Services (22)
    • Receiver (29)
    • SDX (2)
    • ShareFile (8)
    • Single Sign On (3)
    • SmartAuditor (2)
    • Storefront (12)
    • Synergy (25)
    • User Profile Management (2)
    • VDI (7)
    • WebInterface (21)
    • XenApp (84)
    • XenApp Plugin (3)
    • XenClient (10)
    • XenDesktop (55)
    • XenServer (42)
  • Cloud (13)
  • Crystal Ball (2)
  • CTP (13)
  • Docker (2)
  • Events (35)
    • E2E – PubForum (9)
    • Geek Speak (3)
  • Experience (53)
  • Kubernetes (2)
  • Licensing (3)
  • Linux (12)
  • Microsoft (147)
    • Active Directory (1)
    • Azure (8)
    • Office365 (4)
    • PowerShell (19)
    • RDS (5)
    • Windows 10 (6)
    • Windows 2003 (21)
    • Windows 2008 (20)
    • Windows 2008 R2 (54)
    • Windows 2012 (13)
    • Windows 2012R2 (13)
    • Windows 2016 (18)
    • Windows 2019 (4)
    • Windows 2022 (1)
    • Windows 7 (27)
    • Windows 8 (19)
    • Windows Virtual Desktop (1)
    • Windows XP (11)
  • News (5)
  • Raidho (2)
  • Raspberry (3)
  • Scripting (13)
  • Security (5)
  • Slide Deck (1)
  • Thin Clients (3)
  • Twitter (1)
  • Ubiquiti (1)
  • Uncategorized (13)
  • VMware (28)
    • VMWare WorkStation (2)
    • vSphere (16)
Stéphane Thirion
Don't Follow the Trend

Input your search keywords and press Enter.