“ary! Bob! Dinner in 10 minutes,” you yell out the kitchen window because you know the kids are outside somewhere. You transmit a simple message to a known receiver. Usually, the message is received and understood, and the hungry kids show up, but sometimes they may not hear you (so they say, anyway). When that happens too often, you may yell the message twice to make sure it gets through.

The 10 minutes pass and still no kids. You call again and your voice becomes a bit sharper: “Mary, Bob. Dinner NOW!” The severity of the message increases.

Syslog works much the same way as you do in this scenario. It is a very simple method for transmitting a simple message across the Internet (from one machine or host to another) without knowing for sure if the receiver is actually listening (is online) and without receiving any confirmation in return. You can view it as texting (SMS) on cell phones transferred to the Internet.

The major advantage of syslog is its simple form: It is just one ASCII text string. As explained in this article from Microsoft, it doesn’t require much code to send and receive a syslog message if you use the WinSock Control. This control takes care of all the hard work involved with low-level communication on an IP network. However, using WinSock Control for this purpose isn’t trivial. You have to build a syslog message string that conforms exactly to the standard, decode a received message, and split the message into parts to allow you to store it in a database record, which has separate fields for every part of the message.

Ways to Improve syslog Reliability

Though a proposal for a newer syslog standard includes reliability as a feature, reliability was not part of the original standard. In fact, syslog would lose some of its simplicity if it were to adopt this feature. However, you can ensure reliability in the current standard. The solution is to build your own syslog sender and receiver and expand them to two-way communicators that act like this:

1. Sender sends message.
2. Receiver receives message.
3. Receiver returns another syslog message as confirmation.
4. Sender receives the confirmation message.
5. Sender records the original message as received.

Steps 1 and 2 represent the normal behavior of a syslog transaction. Steps 3 and 4 add a confirmation message to the transaction. Step 5 logs the transaction. If step 4 fails, either the original message or the confirmation message will be lost, and after a little while the sender will resend the message. The sender will continue resending the message until it receives a confirmation message or it reaches a maximum count of resends. This solution is not only very simple, but it adheres to the standard as well. However, the implementation is beyond the scope of this article.

A poor man’s alternative for raising the reliability of syslog?or any kind of UDP transmission?is to resend the packet three times. Should one or two packets get lost because of contention on the network, at least one will get through. This article demonstrates this technique in the frmSyslogSend form.

So what are the drawbacks and limitations? Some of the security limitations are listed here. Other drawbacks are more related to the hand-coding required to craft a syslog message string. (See Sidebar 1. The Alternatives: TCP and Windows Eventlog.)

The Setup Before You Start

The sections to follow demonstrate how to set up a MS Access database as a syslog sender/receiver using the Winsock Control. It uses an example application as the reference demo (click here to download the demo source code). The setup requires a great deal of code, mostly for building or parsing a syslog message and for verifying the parts of a message. For that reason, this article does not outline every single step for recreating the demo. You can extract the demo file syslog.mdb instead of creating the example yourself. If you want only the code, both code modules (basSyslog.txt and basWinAPI_Hostname.txt) are also available in the download.

The demo database files contain the following components:

• A table (tblSyslog) for storing received syslog messages. This table simply helps to demonstrate the code you’ll need when applying this technique to your own database.
• A module (basSyslog) that contains all the functions to create and parse a syslog message. All functions have in-line comments, which explain much of the code’s purpose and function. It uses ADO to store the received messages, so be sure to set a reference to that library.
• A module (basWinAPI_Hostname) with a collection of functions to retrieve the machine hostname, which must be used to identify the sender of a syslog message. You need to add this module to your own database. You should use this module as is; do not change the names of any constants or calls.
• A collection of forms for sending or receiving syslog messages.
• A simple query (qdySyslogData) for displaying the syslog table with the calculated Priority values and the length of the stored messages.
• A simple query (qdySyslogPacket) for displaying reconstructed syslog packets from their stored parts.

The WinSock Control

The actual transmission of syslog messages between your application and Windows is left to the WinSock Control, which came with Visual Basic 6 and some old versions of the Access Developer Tools. Further, you can download the control from several places, which include tools to correct a corrupted install.

You may not be able to use the control because Internet Explorer has marked it as blocked in the registry. The fastest way to get around this is to run the reg file (WinsockCompatibilityFlagReset.reg) from the demo download. Alternatively, you can manually edit the registry (read the key in the reg file), but if you aren’t that brave, just download and run the ActiveXHelper tool. Under Microsoft Vista (and sometimes under Windows XP too), you must run the tool as Administrator. When launched, use the tool to locate the WinSock Control, mark it as Enabled, and save.

With that, your setup is complete. Notice, however, that some of the regular updates from Windows Update disable this flag again, so you will have to repeat this procedure.

Create a WinSock-Enabled Form

Creating a form with the WinSock Control is quite easy:

1. Create a new form and keep it open in design view.
2. Open the toolbox or menu entry with available ActiveX components.
3. Locate the control: Microsoft WinSock Control, version 6.0.
4. Select this and click OK.

This procedure adds the control to the form, typically with the name Winsock0. At the same time, it verifies that the control is installed and activated. If any errors occur, the control is not installed or activated correctly.

Before going any further, go to the property sheet and set (tab Format) RecordSelectors and NavigationButtons to False. Save the form as frmSyslogTestSend and create a copy of the form by saving it as frmSyslogTestReceive.

Now, open frmSyslogTestReceive in design view, add a wide textbox, and name it txtPackage. Also, set the properties Protocol and LocalPort of the WinSock control to 1 and 514, respectively, and set a descriptive label for the textbox.

Finally, open the form’s code module and insert the following code:

Option Compare DatabaseOption ExplicitPrivate Sub Form_Open(Cancel As Integer)    Me!Winsock0.Bind 514End SubPrivate Sub Winsock0_DataArrival(ByVal bytesTotal As Long)  Dim strPackage  As String    Me!Winsock0.GetData strPackage, vbString  Me!txtPackage.Value = strPackageEnd Sub

Figure 1. Test Form to Receive Syslog Messages: This is the simple test form to receive and display syslog messages.

This is the minimal code to create a form for receiving a syslog message. The are no equal signs following Bind and GetData because these are methods, not properties. Save the form and open it. It should look like Figure 1.

Next, open frmSyslogTestSend in design view, add a button, name it btnSend, and change its caption to Send. Also, set the properties Protocol, RemotePort, LocalPort, and RemoteHost of the WinSock control to 1, 514, 10514, and localhost, respectively. The number 10514 is an arbitrary and high unused port number.Finally, open the form’s code module and insert the following code:

Option Compare DatabaseOption ExplicitPrivate Sub btnSend_Click()    Dim strPackage  As String    strPackage = SyslogPackageEncode()  Me!Winsock0.SendData CStr(strPackage)End Sub

Figure 2. Test Form to Send a Syslog Message: This is the simple test form to send a syslog message with the current time.

This is the minimal code to create a form for sending a syslog message. Note the missing equal sign following SendData. But why, you may ask, is CStr used? The variable strPackage is a string, so what’s the deal? The reason is that Access handles strings as Unicode, thus if you passed the string variable directly like this:

Me!Winsock0.SendData strPackage

The WinSock control would send a Unicode byte stream when a clean ASCII byte stream is what you expect. CStr converts the Unicode string variable to a simple ASCII byte stream. For testing, you could send any string. However, by calling the function SyslogPackageEncode from the basSyslog module, you generate a fully qualified syslog message with the current time and default values for all other parameters, for example:

Figure 3. Configure a Typical Router to Send Out Syslog Messages: Here is how to configure a router to send syslog messages.

<0>Jun 23 23:56:18 MYMACHINENAME Test: 

Save the form and open it. It should look like Figure 2.

The First Test

With the two forms you have created, you are now ready to run your first test. Open both forms and press the Send button. The test message will appear in the textbox. Even though you can’t decode it fully, you can read the current time in 24-hour format and the name of the sender, and you can tell that the message is a test not trying to provide any further information than this.

To obtain a series of true syslog messages, you will find many available sources depending on your environment. One is your local router, your gateway to the Internet. If you have access to this, you can configure it to send out statistics and other info via syslog. An example is shown in Figure 3. Make sure to set the receiver to the IP address of your workstation and the port number to 514.

Coding and Decoding a Syslog Message

Even though the full syslog message may seem simple, it consists of three parts, each having one or two elements:

PRI  Facility  SeverityHEADER  TIMESTAMP  HOSTNAMEMSG  TAG (optional)    PID (optional)  CONTENT

For the full explanation of these, please study section 4 of the RFC 3164 documentation. This information is not difficult, just too extensive to repeat here. The description of the syslog message presents a two-fold challenge:

1. Create a function, SyslogPackageEncode, that takes optional parameters for every part of a syslog message and?no matter what you feed to it?always returns a valid syslog message (or packet, if you like) according to specification.
2. Create a function, SyslogPackageDecode, that takes a syslog message and splits it into it parts and returns these as individual parameters with default values for those parts that perhaps could not be read and understood.

These functions are contained in the basSyslog module:

Public Function SyslogPackageEncode( _  Optional ByVal bytFacility As slFacility, _  Optional ByVal bytSeverity As slSeverity, _  Optional ByVal datTimestamp As Date, _  Optional ByVal strHostname As String, _  Optional ByVal strTag As String, _  Optional ByVal lngPid As Long, _  Optional ByVal strMessage As String) _  As String  Public Function SyslogPackageDecode( _  ByVal strPackage As String, _  Optional ByVal strRemoteAddress As String, _  Optional ByRef bytFacility As slFacility, _  Optional ByRef bytSeverity As slSeverity, _  Optional ByRef datTimestamp As Date, _  Optional ByRef strHostname As String, _  Optional ByRef strTag As String, _  Optional ByRef lngPid As Long, _  Optional ByRef strContent As String) _  As Boolean

The use of ByRef in SyslogPackageDecode allows the decoded parts of the message to be returned via the parameters. Also, note the enumerated values for Facility and Severity as shown in Listing 1.

Having split the message into it parts, you are nearly ready to store a syslog message in a database. First, you must address a potential error source: The date of the Timestamp, because the year is not included:

Mmm [|d]d hh:mm:ss

You can assume the current year most times but not for messages around New Year’s Day, when the local and the remote machines may be off by a full year. You can handle this situation with the following code:

If Abs(intMonthDiff) >= 6 Then      ' The difference between local date and date of strRfc3164time is big.      ' Adjust syslog date one year to match local date closer.      datDate = DateAdd("yyyy", Sgn(intMonthDiff), datDate)    End If

Also, the date string can be malformed in many ways, which is why the function DateValueRfc3164 is so long.

Another issue is that the content of a syslog message can be much longer than the 255 characters an Access text field can hold. You could use a memo field to store the content, but that is not searchable?certainly a bad thing for a log. Thus, the code uses four text fields to store the content and two functions to split and concatenate the content:

Public Function SplitContent( _  ByVal strContent As String) _  As String()Public Function ConcatenateContent( _  ByVal strContent0 As String, _  Optional ByVal strContent1 As Variant, _  Optional ByVal strContent2 As Variant, _  Optional ByVal strContent3 As Variant) _  As String

The Syslog Table

Before proceeding, you must create the table to hold the syslog messages. Its structure follows the specification for the syslog parts closely (see Table 1).

One field that is not a part of the syslog message itself has been added: RemoteAddress. This is because the address of the sender is very useful information, and you get it “for free” as the WinSock control presents this information whenever a message has been received.

Figure 4. Example Output from Query qdySyslogData: The query qdySyslogData displays the computed Priority values of the syslog entries.

To quickly read the table, you use this query:

SELECT   Id,   TimeStamp,   Facility,  Severity,   Priority([Facility],[Severity]) AS Priority,   Content0,  Content1,  Content2,  Content3,   ConcatenateContent([Content0],[Content1],[Content2],[Content3]) AS Content,   Len([Content]) AS ContentLengthFROM   tblSyslog;

This query displays the computed Priority part and the length of the Content part of the stored syslog messages in table tblSyslog. It uses a couple of helper functions, Priority and ConcatenateContent. See the full list of UDFs (User-Defined Functions) and the documentation for these in the source code download. Figure 4 shows an output example.

To view the verified and stored syslog entries as reconstructed syslog packets, you can use another query, qdySyslogPacket, which simply uses the SyslogPackageEncode function mentioned previously to assemble the data from the fields.

Figure 5. Example Output from Query qdySyslogPacket: The query qdySyslogPacket displays the reconstructed packets of the syslog entries.

SELECT   Id,   TimeStamp,  Facility,   Severity,     SyslogPackageEncode([Facility],[Severity],[TimeStamp],[Hostname],      Nz([Tag],""),[ProcessID],      ConcatenateContent([Content0],[Content1],[Content2],[Content3])) AS   PacketFROM   tblSyslog;

Figure 5 shows an output example.

Create and Send a Demo Syslog Message

To create messages of a more realistic style, the code download includes the form frmSyslogDemoSend (see Figure 6), which features options to select Facility and Severity (from which the resulting Priority is calculated). You can enter a Tag or ProcessId and edit the message. Also, you can change the receiving remote hostname or address to allow you to send messages to another machine.

Figure 6. Form to Send Customized Messages: Adjust the parts of the syslog message you send and who you send it to.

The code behind the form is pretty basic. The click event of the send button demonstrates a simple method to retransmit a message three times. Also, the error handler that takes over if a hostname or address cannot be reached is entered for the receiver. This is because errors of this kind are not handled by the OnError event of the WinSock control itself.

Storing a Syslog Message

To create and send, as well as receive and read, a syslog message, the next task is to save a message in your syslog table. This is what the UDF SyslogEntrySave does:

Public Function SyslogEntrySave( _  Optional ByVal strRemoteAddress As String, _  Optional ByVal bytFacility As slFacility = slFacilityDefault, _  Optional ByVal bytSeverity As slSeverity = slSeverityDefault, _  Optional ByVal datTimestamp As Date, _  Optional ByVal strHostname As String, _  Optional ByVal strTag As String, _  Optional ByVal lngPid As Long, _  Optional ByVal strContent As String) _  As String

Note that all parameters are optional, meaning that just calling the function with no parameters will create a very basic, though a completely valid, entry like this:

<13>Jun 29 20:15:57 localhost Test: 

This is fine for storing a message now and then, but it is not well suited for intense traffic because it opens and closes the table for every message. It is relatively easy to expand the function with parameters to open and close the table, or to turn it into a class with similar methods. However, the following section shows a third option: creating a form bound to your syslog table.

Storing Series of Syslog Message

The form frmSyslogReceive (see Figure 7), is capable of receiving syslog messages by itself, and when bound to the syslog table it can save these directly even at a high incoming rate.

Figure 7. Form to Log Large Amounts of Received Messages: Bound to the syslog table, frmSyslogReceive can receive massive batches of syslog messages.

There isn’t much code behind because you, of course, use the function SyslogPackageDecode to decode a received package. But a small feature has been added to the DataArrival event of the WinSock Control. As shown in Listing 2, the feature checks if the received package is identical to the previous one. The purpose is to store only unique messages if the sender should retransmit messages, as the demo form does. Duplicate packages are ignored, while those carrying new information are saved by a call to the subfunction SaveEntry.

Whenever you receive a syslog message, the form will display the full package in the bottom textbox and the decoded parts in the table fields above.

Log Viewer and Further Refinements

You can browse the complete log, but if a new message arrives, the form will move the displayed records instantly to make the new record visible. Thus, for a form to browse, study, and search the messages, you will need a similar form without the WinSock Control and the receiver code, bound only to the syslog table.

Further, the syslog table contains an AutoNumber field as ID. As is, the view will be sorted in the sequence they arrive, not by the timestamp they should include. Also, the syslog specification does not include time zone information. This may cause trouble if some sender is not in time sync or you receive packages from other time zones. If so, you may choose to add a new field to the table, TimeReceived, which you stamp with the current time by making a small modification to the function SyslogEntrySave and the subfunction SaveEntry.

Browse the code modules for all the helper functions, which are fully documented via in-line comments.