Mobile Management For SQL Server(s!) - Siccolo - SQL Management Tool
Main
How to Build Remote Event Log Montior/Watcher (Using TCP in .NET)
All About EnableRaisingEvents and OnEntryWritten with a little of TcpListener.Listen() and TCP.Send()
  .NET allows a developer to attach a "handler" to monitor for event log changes:
			...
        Dim objLog As EventLog = New EventLog("Application")
        AddHandler objLog.EntryWritten, AddressOf ApplicationLog_OnEntryWritten
        objLog.EnableRaisingEvents = True
			...

	 Public Sub ApplicationLog_OnEntryWritten(ByVal [source] As Object, ByVal e As EntryWrittenEventArgs)
        	Try

           		'handle event log change here

        	Catch err As Exception
			'oops
        	End Try
        End Sub
	
The only problem with this approach - it does not allow to monitor for event log changes on a remote machine. See Microsoft support article 815314.

  The code bellow allows you to build a simple event log "watcher" application to be able to monitor event log changes on a remote machine(s).




    Log Monitor Application ("Log Watcher") consists of two components:
  • Event log monitor service - responsible for monitoring event log changes on a machine
  • Centrilized listener - responsible for gathering data from log monitor services installed on different machines.
 
First, let's build a service component - responsible for "keep an eye on" event log on a machine. To create service application (i.e. application that runs as a service):

    Application that runs as a service, has few events (inherited from System.ServiceProcess.ServiceBase)
  • OnStart() - occurs when the service receives a Start command.
  • OnStop() - occurs when the service receives a Stop command.
  • OnPause() and OnContinue() - occurs when the service receives a Pause/Resume command.


For this application, we're only need OnStart() and OnStop() events.

     ...
     Private m_LocalIP As String = System.Net.Dns.GetHostName()	'points to machine service is running on

     Private m_Watcher_IP As String	'Listening Log Watcher Host IP
     Private m_Watcher_Port As String	'Listening Log Watcher Host Port

     Private m_ErrorLogFile As String	'Log file where we can log useful information while service is running
     ...

     Protected Overrides Sub OnStart(ByVal args() As String)
        ' Add code here to start your service. This method should set things
        ' in motion so your service can do its work.

        'open config file:	LogMonitoringService.exe.config
	'
        m_Watcher_IP = System.Configuration.ConfigurationSettings.AppSettings.Get("watcher_ip")
        m_Watcher_Port = System.Configuration.ConfigurationSettings.AppSettings.Get("watcher_port")
        m_ErrorLogFile = Path.Combine(GetApplicationDirectory(), "log_monitoring_service_errors.txt")

        WorkerThread = New Thread(AddressOf WatchEventLog)
        WorkerThread.Start()
     End Sub
	
where config file looks like this:


So, when service starts, we get configuration settings and start event log monitor:

    Public Sub WatchEventLog()
        Try
            m_LogWatcherLog = New EventLog()
	    'just to make sure we have LogMonitoringService source registered
            Try
                m_LogWatcherLog.CreateEventSource("LogMonitoringService", "Application")
            Catch
            End Try
            m_LogWatcherLog.Close()
            m_LogWatcherLog = New EventLog("Application", ".", "LogMonitoringService")
            m_LogWatcherLog.Source = "LogMonitoringService"

	    'make a record in Application log:	
            m_LogWatcherLog.WriteEntry("LogWacther started." & vbCrLf & _
                        "Send data to [" & m_Watcher_IP & ":" & m_Watcher_Port & "]" & vbCrLf & _
                        "Error file [" & m_ErrorLogFile & "]", _
                        EventLogEntryType.Information)

	    'make a record in log file:	
            LogError("LogWacther started." & vbCrLf & _
                        "Send data to [" & m_Watcher_IP & ":" & m_Watcher_Port & "]" & vbCrLf & _
                        "Error file [" & m_ErrorLogFile & "]")

	    ' "attach" to Application Log
            m_ApplicationLog = New EventLog()
            m_ApplicationLog.Log = "Application"
            AddHandler m_ApplicationLog.EntryWritten, AddressOf ApplicationLog_OnEntryWritten
            m_ApplicationLog.EnableRaisingEvents = True

	    ' "attach" to System Log
            m_SystemLog = New EventLog()
            m_SystemLog.Log = "System"
            AddHandler m_SystemLog.EntryWritten, AddressOf SystemLog_OnEntryWritten
            m_SystemLog.EnableRaisingEvents = True

            m_run = True
            Do While (m_run)
                Thread.Sleep(10000)
            Loop

        Catch e As Exception
            Dim Log As New EventLog("Application")
            Log.WriteEntry("Failed to WatchEventLog:" & e.ToString, EventLogEntryType.Error)
            Log.Close()
            Log.Dispose()
        End Try
    End Sub
	
Now, as soon as change in Application or System event logs is detected...
   Public Sub ApplicationLog_OnEntryWritten(ByVal [source] As Object, ByVal e As EntryWrittenEventArgs)
        Try
            LogError("Application Log Entry:" & vbCrLf & "Message [" & e.Entry.Message & "]")

            SendEventLogEntryToHost("Application", e.Entry)

        Catch err As Exception
            LogError("Failed to ApplicationLog_OnEntryWritten:" & err.ToString())
        End Try
    End Sub

    Public Sub SystemLog_OnEntryWritten(ByVal [source] As Object, ByVal e As EntryWrittenEventArgs)
        Try
            LogError("System Log Entry:" & vbCrLf & "Message [" & e.Entry.Message & "]")

            'send data to watcher
            SendEventLogEntryToHost("System", e.Entry)

        Catch err As Exception
            LogError("Failed to SystemLog_OnEntryWritten:" & err.ToString())
        End Try
    End Sub
	
... Application will "contact" watching host and send log entry data:
   Private Function SendEventLogEntryToHost(ByVal LogName As String, ByVal e As EventLogEntry) As Boolean
        Try
            'send data to watcher
            Dim objTCP As Socket
            Dim remoteEndPoint As New IPEndPoint(IPAddress.Parse(m_Watcher_IP), m_Watcher_Port)
            objTCP = New Socket(remoteEndPoint.Address.AddressFamily, SocketType.Stream, ProtocolType.Tcp)
            objTCP.Connect(remoteEndPoint)

            If objTCP.Connected Then
                LogError("objTCP socket connected to [" & remoteEndPoint.Address.ToString() & "]" & vbCrLf & _
                                    " From Port [" & CType(objTCP.LocalEndPoint, IPEndPoint).Port.ToString() & "]")

                'send data to watcher host:
                Dim Message As String = e.TimeGenerated.ToString("MM/dd/yyyy HH:mm:ss") & "|" & _
                                       LogName & "|" & _
                                       e.EntryType.ToString() & "|" & _
                                       e.Message

                Dim sendBytes As Byte() = System.Text.Encoding.ASCII.GetBytes(Message)
                objTCP.Send(sendBytes, sendBytes.Length, SocketFlags.None)

                LogError("objTCP socket sent [" & sendBytes.Length & "] bytes")
            Else
                LogError("objTCP socket did not connected to [" & remoteEndPoint.Address.ToString() & "]")
            End If

            objTCP.Shutdown(SocketShutdown.Both)
            objTCP.Close()
            LogError("TCP client closed")

        Catch err As Exception
            LogError("Failed to SendEventLogEntryToHost:" & err.ToString())
        End Try
    End Function
	
To make life easier, service application sends event log entry as one string:
		...
  		Dim Message As String = e.TimeGenerated.ToString("MM/dd/yyyy HH:mm:ss") & "|" & _
                                       LogName & "|" & _
                                       e.EntryType.ToString() & "|" & _
                                       e.Message

                Dim sendBytes As Byte() = System.Text.Encoding.ASCII.GetBytes(Message)
                objTCP.Send(sendBytes, sendBytes.Length, SocketFlags.None)
		...
	
And host will "split" string into corresponding fields.

  Second part, to build actual "watch dog" that will receive notifications from event log wacthers.

For this, regular windows application will do just fine. And idea is simple - start TCP listener and wait for data from event log wacthers.





		...
		...
   Private m_ListenerMonitorPort As Integer
   Friend m_objListener_Monitor As TcpListener
		...
		...

   Private Sub frmLogMonitor_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.Cursor = Cursors.WaitCursor

        m_ListenerMonitorPort = System.Configuration.ConfigurationSettings.AppSettings.Get("watcher_port")
        m_Notify = System.Configuration.ConfigurationSettings.AppSettings.Get("notify")

        lblPort.Text = "Listening for changes on port [" & m_ListenerMonitorPort & "]"
        lblNotify.Text = "Notify on change - [" & m_Notify & "]"

        'attach event handler: so we can monitor local events
        'we cannot monitor events on remote computer this way :
        'http://support.microsoft.com/?scid=kb;EN;815314
        'Receive Event Notifications
        'You can receive event notification when an entry is written to a particular log. To do this, 
	'implement the EntryWritten event handler for the instance of the EventLog. 
	'Also, set EnableRaisingEvents to true.
        'Note You can only receive event notifications when entries are written on the local computer. 
	'You cannot receive notifications for entries that are written on remote computers.
	' to monitor local event log:
        Dim objLog As EventLog = New EventLog("Application")
        AddHandler objLog.EntryWritten, AddressOf ApplicationLog_OnEntryWritten
        objLog.EnableRaisingEvents = True

        'remember form
        m_FormSize = Me.Size()
        m_FormLocation = Me.Location

        UpdateApplicationStatus("Application started. Port [" & m_ListenerMonitorPort & "]. Notify [" & m_Notify & "]")

        Me.Cursor = Cursors.Default
    End Sub
	
where configuration file logmonitor.exe.config:


and UpdateApplicationStatus() method simply adds application events to list box:
    Friend Sub UpdateApplicationStatus(ByVal Message As String)
        Message = System.DateTime.Now.ToString("HH:mm:ss") & " - " & Message
        lstApplicationEvents.Items.Add(Message)
    End Sub
To start monitoring process:
    Private Sub cmdStartListener_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdStartListener.Click
        ThreadPool.QueueUserWorkItem(New WaitCallback(AddressOf ListenForWatchers), Me)
    End Sub 
Where ListenForWatchers() opens port for listener and waits for incoming connections from "log watcher": (entries from remote event logs go into top list view control on the form)
   Public Sub ListenForWatchers(ByVal objState As Object)
        Dim objUI As frmLogMonitor

        Try
            objUI = CType(objState, frmLogMonitor)

            m_objListener_Monitor = New TcpListener(m_ListenerMonitorPort)
            m_objListener_Monitor.Start()

            objUI.UpdateApplicationStatus("Started listening on port [" & m_ListenerMonitorPort.ToString() & "]")

            Do
                Dim objClient As New Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp)
                objClient = m_objListener_Monitor.AcceptSocket()

                Dim remoteEndPoint As IPEndPoint = CType(objClient.RemoteEndPoint, IPEndPoint)
                objUI.UpdateApplicationStatus("TCP connection from [" & remoteEndPoint.Address.ToString() & ":" & _
							remoteEndPoint.Port.ToString() & "]")

                Do While objClient.Available = 0
                    'wait...
                    If Not objClient.Connected Then
                        'oops...we lost it...
                        Throw New System.Exception("!Did not receive data!Or Not Connected")
                    End If
                Loop

                If objClient.Available > 0 Then
                    Dim InBytes(objClient.Available) As Byte
                    objClient.Receive(InBytes, objClient.Available, SocketFlags.None)
                    Dim Message As String = Replace(System.Text.Encoding.ASCII.GetString(InBytes), Chr(0), "")

                    Dim EventLogEntry() As String = Message.Split("|")
                    Dim date_time As String = System.DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss")
                    Dim objItem As ListViewItem = lvwLogEntries.Items.Add(date_time)
                    With objItem
                        .SubItems.Add(remoteEndPoint.Address.ToString())
                        .SubItems.Add(EventLogEntry(1))
                        .SubItems.Add(EventLogEntry(2))
                        .SubItems.Add(EventLogEntry(3))
                    End With
                Else
                    objUI.UpdateApplicationStatus("no data received from TCP connection [" & _
				remoteEndPoint.Address.ToString() & ":" & remoteEndPoint.Port.ToString() & "]")
                End If
            Loop Until False


        Catch err As Exception
            objUI.UpdateApplicationStatus("ListenForWatchers():Process TcpSocket Error [" & err.Message & "] ")
        End Try
    End Sub
and entries from local event logs go into bottom list view control on the form:
   Private Sub frmLogMonitor_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		...
		...
        Dim objLog As EventLog = New EventLog("Application")
        AddHandler objLog.EntryWritten, AddressOf ApplicationLog_OnEntryWritten
        objLog.EnableRaisingEvents = True
		...
		...
    End Sub

    Public Sub ApplicationLog_OnEntryWritten(ByVal [source] As Object, ByVal e As EntryWrittenEventArgs)
        Try

            Dim date_time As String = e.Entry.TimeGenerated.ToString("MM/dd/yyyy HH:mm:ss")
            Dim objItem As ListViewItem = lvwLogEntries_OnEntryWritten_Handler.Items.Add(date_time)
            With objItem
                .SubItems.Add(e.Entry.MachineName.ToString())
                .SubItems.Add("Application")
                .SubItems.Add(e.Entry.EntryType.ToString())
                .SubItems.Add(e.Entry.Message)
            End With

        Catch err As Exception
            MessageBox.Show("Failed to process entry:" & vbCrLf & _
                          "-----------------------------------" & vbCrLf & _
                          err.Message & vbCrLf & _
                          "-----------------------------------", _
                          "OnEntryWritten Handler", _
                          MessageBoxButtons.OK, _
                          MessageBoxIcon.Exclamation)
        End Try
    End Sub	


And that's it!

  Application can be extended to monitor only certain events - such as Errors only, and/or events from certain Event sources only, for example from MS SQL Server only:
To do this, we need to change code in Log Monitoring service just a little bit:
   Public Sub ApplicationLog_OnEntryWritten(ByVal [source] As Object, ByVal e As EntryWrittenEventArgs)
        Try
            If e.Entry.EntryType = EventLogEntryType.Error And _
                        e.Entry.Source = "MSSQLSERVER" Then

                LogError("Application Log Entry:" & vbCrLf & "Message [" & e.Entry.Message & "]")

                SendEventLogEntryToHost("Application", e.Entry)
            End If

        Catch err As Exception
            LogError("Failed to ApplicationLog_OnEntryWritten:" & err.ToString())
        End Try
    End Sub


Application can also send notifications not to just one log wacthing host, but to multiple ones.
In this case, we need to modify SendEventLogEntryToHost() to pass additional paremeter - host address (and, of course, we can add Monitoring Host Port as well):
   Private Function SendEventLogEntryToHost(ByVal LogName As String, _
                                            ByVal MonitoringHost As String, _
                                            ByVal e As EventLogEntry) As Boolean
        Try
            'send data to watcher
            Dim objTCP As Socket
            Dim remoteEndPoint As New IPEndPoint(IPAddress.Parse(MonitoringHost), m_Watcher_Port)
            objTCP = New Socket(remoteEndPoint.Address.AddressFamily, SocketType.Stream, ProtocolType.Tcp)
            objTCP.Connect(remoteEndPoint)

            If objTCP.Connected Then
                LogError("objTCP socket connected to [" & remoteEndPoint.Address.ToString() & "]" & vbCrLf & _
                                    " From Port [" & CType(objTCP.LocalEndPoint, IPEndPoint).Port.ToString() & "]")

                'send data to watcher host:
                Dim Message As String = e.TimeGenerated.ToString("MM/dd/yyyy HH:mm:ss") & "|" & _
                                       LogName & "|" & _
                                       e.EntryType.ToString() & "|" & _
                                       e.Message

                Dim sendBytes As Byte() = System.Text.Encoding.ASCII.GetBytes(Message)
                objTCP.Send(sendBytes, sendBytes.Length, SocketFlags.None)

                LogError("objTCP socket sent [" & sendBytes.Length & "] bytes")
            Else
                LogError("objTCP socket did not connected to [" & remoteEndPoint.Address.ToString() & "]")
            End If

            objTCP.Shutdown(SocketShutdown.Both)
            objTCP.Close()
            LogError("TCP client closed")

        Catch err As Exception
            LogError("Failed to SendEventLogEntryToHost:" & err.ToString())
        End Try
    End Function


Article keywords: ApplicationLog_OnEntryWritten, OnEntryWritten, OnStart(), OnStop(), System.Net.Dns.GetHostName(), System.Configuration.ConfigurationSettings.AppSettings.Get(), New Thread, CreateEventSource(), IPEndPoint(), System.Net.Socket(), TCP.Connect(), TCP.Send(), TCP.Shutdown(), TCP.Close(), EnableRaisingEvents, ThreadPool.QueueUserWorkItem, TcpListener, TcpListener.Start(), TCP.Receive, ListViewItem


Back To Articles Page

Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to [email protected]    Greg Dubinovsky � 2006
or share your thoughts at Siccolo Blog

web sponsor - siccolo.com. well being sponsor - Enabling clinical and operational value across the continuum of care.
Siccolo - SQL Server Management Tool For Mobile Devices is packed with built-in functionality and tools. Siccolo delivers a rich set of management tools for both DBAs and sys admins. SQL Server management has always been an area of DBA concern. The new Management Tool For Mobile Devices - Siccolo - has simple "Enterprise Manager" and the "Query Analyzer". Siccolo is a management tool for the MS SQL Server with administration capabilities and a database query tool. The administration features provide users the ability to browse database structures. An integrated query tool allows users to quickly create, edit and execute SQL queries and scripts. Siccolo also provides an export tool to allow users to easily save and email execution results. Siccolo helps database professionals save time and increase their productivity by utilizing a more efficient approach to database management - use their Windows Mobile empowered device while sipping margarita on the beach For increased security, Siccolo is configured to run under SSL with IIS authentication.