Newsletters

Sawmill Newsletter

September 15, 2010

Welcome to the Sawmill Newsletter!

You’re receiving this newsletter because during the downloading or purchase of Sawmill, you checked the box to join our mailing list. If you wish to be removed from this list, please send an email, with the subject line of “UNSUBSCRIBE” to newsletter@sawmill.net (please include the entire message, as the identifying information is at the bottom).

News

Streaming Media West, Los Angeles, CA, November 2-3 2010
Please join us at Streaming Media West, where we will be showing off the latest version of Sawmill, and giving away prizes. Prizes include Sawmill licenses, t-shirts, mugs and more. SMW is being held at the Hyatt Regency Century Plaza, and we look forward to seeing you there!

Sawmill News
Sawmill 8.1.6 shipped on September 6, 2010. This is a bug-fix release--it fixes a few bugs. This release is free to existing Sawmill 8 users. It is recommended for anyone who is experiencing problems with Sawmill 8.1.5 or earlier. You can download Sawmill 8.1.6 from http://sawmill.net/download.html .

Sawmill 7 users can upgrade to Sawmill 8 for half of the license price; or if you have Premium Support, the upgrade is free. Major features of Sawmill 8 include support for Oracle and Microsoft SQL Server databases, real-time reporting, a completely redesigned web interface, better multi-processor and multi-core support, and role-based authentication control.

This issue of the Sawmill Newsletter describes connecting to a Microsoft SQL Server database from a Sawmill installation running on Linux.

Get The Most Out Of Sawmill With Professional Services

Looking to get more out of your statistics from Sawmill? Running short on time, but need the information now to make critical business decisions? Our Professional Service Experts are available for just this situation and many others. We will assist in the initial installation of Sawmill using best practices; work with you to integrate and configure Sawmill to generate reports in the shortest possible time. We will tailor Sawmill to your environment, create a customized solution, be sensitive to your requirements and stay focused on what your business needs are. We will show you areas of Sawmill you may not even be aware of, demonstrating these methods will provide you with many streamlined methods to get you the information more quickly. Often you'll find that Sawmill's deep analysis can even provide you with information you've been after but never knew how to reach, or possibly never realized was readily available in reports. Sawmill is an extremely powerful tool for your business, and most users only exercise a fraction of this power. That's where our experts really can make the difference. Our Sawmill experts have many years of experience with Sawmill and with a large cross section of devices and business sectors. Our promise is to very quickly come up with a cost effective solution that fits your business, and greatly expand your ROI with only a few hours of fee based Sawmill Professional Services. For more information, a quote, or to speak directly with a Professional services expert contact consulting@flowerfire.com.

Tips & Techniques: Using Sawmill With Microsoft SQL Server On Linux

With Enterprise licensing, Sawmill can use a Microsoft SQL Server (MS SQL) database as the back-end database, which it uses to store the parsed log data, to store support information like cross-reference tables, hierarchy tables, and session information, and to query the results to generate reports.

Most Sawmill customers run Sawmill on Windows if they want to use MS SQL, and that is certainly the easiest route--Windows has a built-in, fully functional ODBC driver for MS SQL, and it is necessary only to set up a DSN in Administrative Tools, and Sawmill will happily connect to MS SQL and use it for all its database needs.

For customers who prefer to run Sawmill on a different (non-Windows) platform, but still want to use Microsoft SQL Server, the process is more complicated. It is necessary to install and configure an ODBC driver manager, and then an ODBC driver, to use Sawmill with MS SQL.

Third-Party Commercial ODBC Drivers

One simple solution is to use a third-party commercial ODBC driver, like those provided by DataDirect or EasySoft. In our experience, these both work flawlessly, and allow ODBC connectivity in Sawmill to an MS SQL database server (or Oracle). This is our recommended approach, if the budget allows it.

Open Source ODBC

For customers who prefer open source solutions, in principle, UNIX platforms can use an open source driver manager like unixODBC, together with an open source ODBC driver like the one included as part of FreeTDS. After setting these two up, it should be possible to compile Sawmill from the encrypted source code (which should recognize and link to unixODBC), and then setting up a DSN and connecting to MS SQL. Until recently, however, we have never seen this work in practice, due to bugs in either the driver manager, or the driver.

We are pleased to report, however, that in the past few weeks we have finally seen sufficiently functional versions of both unixODBC and FreeTDS to allow Sawmill to use open source ODBC connectivity to MS SQL from Linux. It's still not perfect, but it's getting there, and this newsletter describes how to do it.

Note that a few small changes were made to Sawmill to work around issues with FreeTDS and MS SQL, so Sawmill 8.1.6 or later must be used in order to successfully build databases on MS SQL using FreeTDS.

Step I: Install and Configure FreeTDS

In this example, we're using the Fedora Core 13 variant of Linux. Start by installing FreeTDS, if it's not already installed, by running yum (or use your favorite package manager):

$ yum install freetds

This installs the FreeTDS component, which implements connectivity to MS SQL, and includes an ODBC driver.

Now configure connectivity to your MS SQL server by adding an entry to /etc/freetds.conf similar to this one:

[sawmill_dsn] host = sage.flowerfire.com instance = SAGE_2005_64BIT port = 1433

In this case, the server's hostname is sage.flowerfire.com--you will change the parameters above to match your own server's hostname, instance, and port. It may not be necessary to list an instance, if you have only a single instance on your SQL server.

You can test the TDS connection by attempting to connect using the tsql tool (installed as part of FreeTDS):

# tsql -S sawmill_dsn -U sqluser -P password locale is "en_US.UTF-8" locale charset is "UTF-8" 1>

If tsql succeeds as above, and gives a prompt without an error, you're ready to move on to setting up the ODBC driver. If it fails, resolve the TDS issue first--ODBC won't work until TDS does.

Step II: Install and Configure unixODBC

Now, install unixODBC, the ODBC driver manager:

$ yum install unixODBC

Once unixODBC is installed, you're ready to create a DSN (Data Source Name), which Sawmill will use to connect to the server. You can do this by adding the following to your /etc/odbc.ini file:

[sage] Description = TDS connection Driver = /usr/lib64/libtdsodbc.so.0 Setup = /usr/lib64/libtdsS.so.0 Server = SAGE\SAGE_2005_64BIT Port = 1433

This creates a DSN called "sage" which points to the FreeTDS ODBC drivers (Driver and Setup), and refers to the MS SQL Server (and instance) and port. If you're using 32-bit Linux, you will have "lib" instead of "lib64" in the pathname for Driver and Setup. If your MS SQL server has only one instance, your server may just be the server name, e.g., "SAGE" without the backslash or the instance name.

If the DSN is now properly configured, you should now be able to use isql to connect to the MS SQL database through the DSN, e.g.,

If you get this far without an error, then ODBC-via-TDS seems to be working properly, and you can move on to Sawmill. If you get an error from isql, resolve that error before proceeding.

Step III: Installing and Configuring Sawmill

Sawmill for Linux does not come with ODBC support compiled (if it did, it wouldn't run on systems without ODBC libraries installed), so it must be built from the encrypted source code in order to use ODBC. This is simple--just download the encrypted source code version of Sawmill, untar/gunzip it, and configure/make it (the final strip step is optional, but makes the binary much smaller). You will need to have the GNU C++ compiler (or another C++ compiler) installed (the package name for the compiler is often gcc-c++).

# tar xfz sawmill8.1.6.3_crypt_src.tar.gz # cd sawmill-8.1.6.3 # ./configure CXXFLAGS="-DNO_SUPPORT_FOR_SQL_WVARCHAR_IN_ODBC_DRIVER" ... checking sqlext.h usability... yes checking sqlext.h presence... yes checking for sqlext.h... yes checking for ODBC... yes checking for SQLAllocEnv in -lodbc... yes checking for SQLAllocEnv in -liodbc... no ... # make sawmill ... # strip sawmill

In the middle of the configure output, watch for the ODBC section, which should look something like the above. It should find sqlext.h; it should say "yes" for ODBC, and it should find SQLAllocEnv in one of the two libraries (probably -lodbc). If it doesn't, then Sawmill's autoconf script hasn't found unixODBC on your system--make sure it's installed. If it's in a non-standard location, you can tell configure where it is with the --with-odbc-prefix=PFX flag.

Once configure is done, run make as above, and when it's done you'll have your "sawmill" binary, ready to start (e.g., with "./sawmill&"). You should now be able to choose ODBC / MS SQL as the database engine (or log source) in the Create Profile wizard of Sawmill, and it will use your MS SQL server as the back-end database.

Caveats: WVARCHAR Support, and ODBC_DRIVER_MULTIPLE_ROWS_BROKEN_HACK

There are two remaining issues with using unixODBC, FreeTDS, and Sawmill.

The first appears to be simply a bug in older versions of FreeTDS, which causes data to be loaded into the database incorrectly when doing bulk inserts. This bug exists in FreeTDS 0.64 (the version included in CentOS 5); it no longer exists in FreeTDS 0.82 (the version included in Fedora Core 13). So you should really use FreeTDS 0.82 or later. But if you can't, you can work around this by running configure like this instead (adding -DNO_SUPPORT_FOR_SQL_WVARCHAR_IN_ODBC_DRIVER to the CXXFLAGS for the C++ compiler):

# ./configure CXXFLAGS="-DNO_SUPPORT_FOR_SQL_WVARCHAR_IN_ODBC_DRIVER -DODBC_DRIVER_MULTIPLE_ROWS_BROKEN_HACK"

This tells Sawmill not to trust the ODBC driver's implementation of multi-row bulk insert, and to insert data one row at a time instead. This can greatly slow down database builds, but without it, builds will fail with an error when using FreeTDS 0.64. For faster import, upgrade to FreeTDS 0.82 or later.

The second problem is that FreeTDS does not support the WVARCHAR type, or "wide" (Unicode) characters. Sawmill normally uses WVARCHAR to support non-ASCII (e.g., non-English) character sets in log data. This is not possible with FreeTDS, which allows only the use of the "narrow" VARCHAR. Furthermore, as of this writing FreeTDS does not have any plans to support WVARCHAR in the future. If it does support it at some time in the future, you can remove the -DNO_SUPPORT_FOR_SQL_WVARCHAR_IN_ODBC_DRIVERoption from configure (shown above); but until then, this option must be present, or Sawmill will generate an error on import.

This means that FreeTDS cannot be used with non-ANSI log data--it will cram it down into 8-bit ANSI, corrupting any 16-bit information in the log data. If you need to process full Unicode log data on Linux with MS SQL, use a commercial ODBC driver--both DataDirect and EasySoft drivers support Unicode. Or run Sawmill on Windows, where the native driver supports Unicode.

What About Oracle?

In principle, it should be possible to use unixODBC with the Oracle Client Driver on Linux, to use an Oracle database as the back end database for Sawmill. However, like MS SQL until recently, the technology just doesn't seem to be working yet in open source. In spite of considerable effort, we have never successfully gotten Sawmill to talk to Oracle via unixODBC and the Oracle Client Driver on Linux (or, by the way, on Solaris). This appears to be due to a version mismatch between unixODBC and the client driver libraries; according to unixODBC, they've changed the library API, and Oracle hasn't followed yet. If anyone has succeeded in using unixODBC with Oracle and Sawmill, we'd be very interested in hearing about it. Until then, however, the solutions are: (1) use Windows, where Sawmill works great with the Oracle Client driver, or (2) use a third-party commercial ODBC driver, like those from DataDirect and EasySoft--these both support Oracle, and work with Sawmill on Linux.

What About Other Platforms?

Windows, as mentioned above, is easy--it just works, without any extra installation, or any configuration other than the DSN. Other UNIX platforms should work just like Linux, in theory, but we haven't tested them yet with the open source components, so we can't say for certain--there could be some platform-specific oddity.

Professional Services

This newsletter describes methods for configuring Sawmill to use ODBC on Linux to use Microsoft SQL Server as the back-end database. If you need assistance with this installation or configuration, or with any other Sawmill tasks, our Sawmill Experts can help. Contact sales@sawmill.net for more information.

[Article revision v1.0]
[ClientID: 43726]