Export Configuration

Overview

The Export Configuration dialog allows you to configure KeyServer to export its internal data to an external database, such as an Oracle server, MySQL Server, etc. Depending on the platform hosting the KeyServer process, specific export modules are available:

• ODBC - will export to any target database that has an ODBC driver.
• MySQL - Macintosh OS X or Linux (Windows can export to a MySQL database by using the ODBC export module connecting through a DSN configured with the MySQL's ODBC Driver).
• PostgreSQL - Macintosh OS X or Linux (Windows can export to a PostgreSQL database by using the ODBC export module connecting through a DSN configured with the PostgreSQL's ODBC Driver).

There are various reasons you might want to use exporting:

• Exporting data will allow you to integrate with other existing databases. Note: custom report generation tools can join data queried directly from KeyServer with data queried from other data servers - exporting is not actually necessary for this kind of integration but may be most convenient.
• Exported copies of KeyServer's tables can have fields added that may be useful for integration with other inventory tracking practices.
• Reports may run significantly faster against a high performance external database server if you have a lot of usage information (accumulated for a long period of time from many computers). Note: with KeyReporter, reports can be scheduled to run automatically during off hours so speeding up the run time may then become unimportant.

Both external and internal reports in KeyConfigure can be used to access either exported or internal data. External reports (Crystal, MSAccess, FileMaker, etc.) always use ODBC with a correctly configured DSN to specify the target database. Internal reports target the KeyServer's internal database by default unless the Options button in the Configure Reports dialog is used to specify a DSN targeting an exported database.

If there are firewall rules controlling traffic between the KeyServer host computer and the external database server, you may need to set up appropriated firewall rules. Consult the database server documentation.

Three additional export modules listed below are included for use in transforming KeyServer data to XML, comma delimited, or tab delimited text. These methods do not use the network to pass data directly to a remote database server and cannot be performed incrementally (e.g. complete database files are exported every time). They can be used for static export/import of K2 data into another database or analysis tool.

• XML
• CSV
• Text

Note: every time one of the static export methods (XML, CSV, or Text) is run, the complete database files are exported into the "Export" folder within the KeyServer Data Folder replacing any previously exported file of the same name. For these three static methods, scheduled exports are allowed at most once per day.

Configuration Details

OPEN Export Configuration dialog

Disabled Module

By default, the export module is Disabled, meaning that the internal databases will not be exported.

Export Audits and Export Usage

All tables except for Audits and Usage are always exported. These two check boxes allow you to configure whether the Audits and Usage tables are exported or not, since these two tables are by far the largest. By default, they are both exported, so that the external database will contain all the data that the internal database does.

ODBC Module

Configuring this module allows KeyServer to use ODBC to export the internal databases to an SQL database through a DSN. Before configuring the ODBC module, you must first set up the ODBC Data Source that KeyServer will use. Instructions on setting up an ODBC Data Source should be included with your database software, as the details are specific to each database system. However, the DSN must be created on the computer which is running KeyServer, and it must be a System DSN. Furthermore, if you are running 64 bit Windows, you must define the DSN using the 32 bit version of the ODBC Administrator, which is at C:\windows\syswow64\odbcad32.exe

You will also need to create a database into which KeyServer will insert data. You can use an existing database if you choose. There must also be an account and password that is granted read and write (SELECT, INSERT, UPDATE, CREATE TABLE) access to the database.

Once you have set up an ODBC Data Source (DSN) and the corresponding database (and access account), you can configure KeyServer to export its data to this Data Source. Choose the Exporting command from the Admin menu in KeyConfigure, and then select ODBC from the pop-up menu. In the Configuration section of the dialog box, enter the name of the Data Source you just set up, along with the account name and password (don't leave the password field blank).

There are six options that you can set to control the SQL commands used for creating new tables, records, and fields.

Use Microsoft-compatible CREATE TABLE

KeyServer will create all tables if they are not already present in your database. This option changes what type of syntax is used in the CREATE queries. If you are using MS Access or SQL Server to host your database, you should check this option. Other database programs might also support or even require this method of table creation. See KeyServer Tables Description for more information on what tables will be created. If you don't know whether to use this option, try using it first, before trying not using it.

Use REPLACE semantics

Some databases support a “REPLACE” SQL command, which is an efficient way to insert an item into a table that might already contain that item. If your database supports REPLACE, check this option. Otherwise, KeyServer will use a combination of the INSERT and UPDATE keywords to perform the same operation. For most databases, this option should NOT be checked.

Use SQLBindParameter optimization

Some databases support “Parameter binding,” which is an efficient way of executing multiple SQL queries that use the same basic form. While exporting, the ODBC module issues many commands that look virtually identical except for the actual data. Checking this option may allow your database driver to parse the actual command a single time, greatly speeding up the time required to export. If your database supports Parameter Binding, check this option.

Use UTF-8 Encoding for text fields

The default text encoding is ascii, but you can choose UTF-8 if your target database supports it. Note: some versions of postgresql require UTF-8 text encoding.

Record Greenwich Mean Time

By default, KeyServer writes the “local” time of each event. If you have different KeyServers in different time zones, or if you want your data to be independent of time zone, check this option. The KSServers table contains the GMT offset of each server listed, which aids in computing the local time from GMT, or GMT from local time. If you do check this option, you should check it on all servers that stream data to the same database.

Escape Backslashes in text fields

This option should only be turned on if you are exporting to postgresql. postgresql treats a backslash within a string as an escape symbol, which causes problems if a single backslash is followed by three numerical characters. If you discover that another ODBC driver requires this option, please let us know.

Time fields can be exported in one of four different formats:

Use Standard DATETIME

The "standard" DATETIME data type and time format is accepted by most databases. This is the default.

Use Oracle-compatible DATETIME

Oracle accepts the DATETIME data type but the exact syntax for the time format is Oracle specific, so use this format when exporting to Oracle. Note: Oracle also uses a proprietary join syntax for queries. If you make use of the ODBC option to point internal reports to an exported Oracle database, then be sure to select "Use Oracle Join Syntax" under Options in the Report Selection dialog. See Report Options Dialog for more information.

Use TIMESTAMP

The TIMESTAMP data type and time format is required by some databases (instead of the more common DATETIME datatype).

Use text times

If your database does not support a datatype for date-time data, you can resort to a text export.

In the lower left, in the Schedule area, you can configure how often KeyConfigure will perform an export. Note that the export will be incremental, so there will not be a huge performance penalty if you choose a short interval. The KeyServer 5.2 concept of “Streaming” can be mimicked by choosing a short interval such as 5 minutes.

Once these settings have been made, KeyServer begins to export its databases according to the schedule you have configured. You can force an initial export by clicking Export Now. The first thing that KeyServer does during an export is check if the tables are already present in the database. If not, it attempts to create these databases using the appropriate “CREATE TABLE” command. If for some reason the tables cannot be created, you can create these tables yourself (FileMaker Pro for example does not accept this command). See KeyServer Tables Description for the details of what is in each table.

Example configurations

The following list gives examples of how to configure the ODBC export module to work with your particular ODBC driver. If you are using a different database and driver, we would be interested to hear how to successfully configure it.

Microsoft (Access, SQL server, etc.) - Use Microsoft-compatible CREATE TABLE, Use Standard DATETIME
MySQL - Use REPLACE semantics, Use Standard DATETIME
Oracle - Use Microsoft-compatible CREATE TABLE, Use SQLBindParameter optimization, Use Oracle-compatible DATETIME
PostgreSQL - Use UTF-8 Encoding for text fields, Use Text Times

Testing the ODBC export

Every computing environment is different, and with KeyServer's ODBC exporting you are using software from several different sources (KeyServer, ODBC, the ODBC drivers, the database, etc.) These factors increase the potential for problems, so you should be sure to verify that your configuration works properly.

One good strategy is to test your configuration at each stage. First set up your database and make sure you can get the proper access to it using the database's native tools. Next, set up the ODBC Data Source and verify that it works correctly using a simple ODBC-based query tool like Crystal Reports, MS Access, MS Query, or any tools that come with your database system. It is important at all steps to verify that you have granted the appropriate permissions to the account that KeyServer will be using to connect to your database.

Configure KeyServer's ODBC export module and click Export Now. Check that the tables were created properly. As a simple confirmation, you could use a query tool to run the SQL command "select count(*) from KSComputers" - this should return a number which matches the computer count at the bottom of KeyConfigure's Computer Window.

If you run into problems after configuring Exporting, use the ODBC Trace facility to gather diagnostic information. Enable ODBC Tracing in the ODBC control panel, then click Export Now again. The ODBC trace log will contain useful status information for each step of the process, and will help you determine what needs to be fixed. If you need to call Sassafras technical support for help in setting up ODBC exporting, the first thing we will ask for is your trace log.

In order for the linux version of this module to work, you will need to install unixODBC, which can be downloaded from http://www.unixodbc.org/.

MySQL Module

The MySQL module can only be used to export to MySQL databases. It is available to KeyServers running on OS X or linux. If you want to export to a MySQL database from a Windows KeyServer, you can download the ODBC driver for MySQL and use the ODBC exporting module.

This module looks very similar to the ODBC module, but it has fewer options, and there are a few slight differences in how it should be configured.

The Data Source field usually contains both the host name and the Database name separated by a slash. The syntax is:

    hostname/database

If the hostname is missing, then localhost is used. For example, typing "/ksdb" will use the MySQL server on localhost, and the database named "ksdb". Typing "192.168.0.4/ksdb" will use a MySQL server on 192.168.0.4, and the database named "ksdb".

In order for the linux version of this module to work, you will need to install the MySQL "Shared compatibility libraries" that MySQL provides. These can be downloaded from http://mysql.org/downloads/mysql/5.0.html (search for "Shared compatibility libraries" in the various Linux flavors).

PostgreSQL Module

The PostgreSQL module can only be used to export to PostgreSQL databases. It is available to KeyServers running on OS X or linux. If you want to export to a PostgreSQL database from a Windows KeyServer, you can download the ODBC driver for PostgreSQL and use the ODBC exporting module.

This module looks very similar to the ODBC module, but it has fewer options, and there are a few slight differences in how it should be configured.

The Data Source field usually contains both the host name and the Database name separated by a slash. The syntax is:

    hostname/database

If the hostname is missing, then localhost is used. For example, typing "/ksdb" will use the MySQL server on localhost, and the database named "ksdb". Typing "192.168.0.4/ksdb" will use a PostgreSQL server on 192.168.0.4, and the database named "ksdb".

In order for the linux version of this module to work, you will need to have libpq.so.3 installed, which can be downloaded from postgresql.org.

XML module

This module exports all databases as XML files into the “Export Files” directory (within the "KeyServer Data Folder"). Unlike the ODBC or MySQL methods, the exported data is not immediately accessible to reporting tools until it has been explicitly imported into some database or analysis tool. As noted above, exporting is not incremental and can be scheduled at most once per day.

CSV module

Text module

This module exports all databases in tab separated field format into the “Export Files” directory (within the "KeyServer Data Folder"). Unlike the ODBC or MySQL methods, the exported data is not immediately accessible to reporting tools until it has been explicitly imported into some database or analysis tool. As noted above, exporting is not incremental and can be scheduled at most once per day.

Special case for Term tables

After successfully configuring exporting, you may notice that all tables have been successfully exported, but all seven term tables are missing. If this is the case, try the following series of steps:

1. Disconnect with KeyConfigure
2. Quit KeyServer on the computer where it is running
3. Start up KeyServer again
4. Connect to KeyServer with KeyConfigure
5. Open the Export Configuration dialog and click Export Now
6. Wait for the export to complete and see if the term tables have been exported.

The reason that the KeyServer may need to be restarted in order to export the Term tables is that the Term tables, unlike all the other tables, very seldom change. Therefore, KeyServer only attempts to export them the first time it does an export after starting up. So if you have made changes to the export configuration, or manually deleted any tables from the external database server, you may be stuck in a situation where KeyServer won't try to export the Term tables again until you restart.

Related Topics