RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Create a Syslog Sender/Receiver Using the MS Winsock Control : Page 3

With the Microsoft WinSock Control and a few coding tricks, you can use Access as a high-performance syslog sender, receiver, and logger.


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:
  TAG (optional)
    PID (optional)

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 [<space>|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).

Table 1. Structure of Syslog Table

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:

  Priority([Facility],[Severity]) AS Priority, 
  ConcatenateContent([Content0],[Content1],[Content2],[Content3]) AS Content, 
  Len([Content]) AS ContentLength

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.
      ConcatenateContent([Content0],[Content1],[Content2],[Content3])) AS 

Figure 5 shows an output example.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date