Database Export

The Database Export dialog (Config menu) allows you to configure KeyServer to export its internal data to an external database, such as Oracle, MySQL, SQL Server, etc.

What exporting is NOT:

• Exporting is NOT required. KeyServer uses internal, proprietary databases and exporting is never needed. Even when exporting is turned on, KeyServer never consults the external databases, it merely exports a copy of the internal data.
• Exporting is NOT a form of backup. Exporting does create a second copy of the internal data, and you may see this as a form of backup, but N.B. there is NO way to bring data back from the external database into the KeyServer database - that is, there is not "Restore".
• Exporting is NOT the only way to use KeyServer data for an external report (Crystal, php, .NET, etc). We provide an ODBC driver, ksODBC, which allows SQL queries to be performed directly against KeyServer internal databases. This driver is installed by default by the ksp-admin installer. You can install just ksODBC without KeyConfigure if that is all you need on a particular computer.

Reasons 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: 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.

Platform Specific Modules

Several modules are available cross platform:

• XML
• CSV
• Text
• ODBC

Note: every time one of the static export methods (XML/ CSV/ 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. Also, since a complete export of large databases can be slow, you might want to disable export of Audits and Usage when using a text based method. 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 data from your Sassafras Server into another database or analysis tool. Usually, exporting to a database of some sort is a better choice than exporting to any of the text formats.

ODBC - will export to any target database that has an ODBC driver.

Other export modules are only available based on the platform of the host server.

• SQL Server - Windows only, requires DSN configuration. Mac and Linux can export to MSSQL using ODBC.
• MySQL - Mac or Linux. Windows can export to MySQL database by using the ODBC export module connecting through a DSN configured with the MySQL's ODBC Driver.
• PostgreSQL - Mac or Linux. Windows can export to a PostgreSQL database by using the ODBC module connecting through a DSN configured with the PostgreSQL's ODBC Driver.
• SQLite - Mac OS. Windows can export to a SQLite database by using the ODBC module connecting through a DSN configured with the SQLite's ODBC Driver.

Note: newer versions of KeyServer may include additional fields in various tables (e.g. the Computer table has had additional fields added in many of the major releases of KeyServer). In order to include new or additional fields into a previously exported table, consult the tables documentation.

Export Audits and Export Usage

All tables except for Audits and Usage are always exported. There are several check boxes to 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.
Include All Programs - every program installed on a computer gets a row in the export - as opposed to only the application programs.
Export Package Audits is off by default. Checking it will export the KSAuditPackages table

Disabled Module

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

ODBC Module

Configuring this module allows KeyServer to use ODBC to export the internal databases to an SQL database through a DSN (defined with system access permissions on the computer which is running KeyServer). 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.

Note that if you are using a 64-bit version of KeyServer, you will need to use a 64-bit ODBC driver. Likewise, 32-bit KeyServer requires a 32-bit ODBC driver. This is one of the few reasons to prefer one bitness over the other when given a choice on a 64-bit OS. In addition, you must use the correct ODBC Administrator which matches the bitness of the KeyServer executable. Specifically, if you are using 32-bit KeyServer on a 64-bit OS, you will need to manually locate and launch the 32-bit ODBC Administrator, which is at C:\Windows\SysWow64\odbcad32.exe, and use that version of ODBC Administrator when defining the DSN which will be used in the KeyServer Database Export.

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, DELETE) access to the database. Note that DELETE is ONLY used on the Audit Products database to keep the inventory of software to hardware accurate. Otherwise no records are ever deleted (computers, devices, users, purchases, etc). This is intentional so a) the export provides some historic record if needed and b) because generally we advocate for not deleting data unless necessary.

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 Config 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 various 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 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 SQLBindParameter with multiple rows

When doing “Parameter binding,” you can give data for more than query in a single call to the ODBC driver. If your database/driver support multi row binding, you should consider using this option. It could make the export more efficient overall.

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 almost never be turned on. Some old versions of postgresql treated a backslash within a string as an escape symbol, which causes problems if a single backslash is followed by three numerical characters. These versions of postgresql require this option to be turned on. If you discover that another ODBC driver requires this option, please let us know.

The basic queries for adding new rows and modifying existing rows can be done in one of four ways:

Use INSERT/UPDATE semantics

This is the most inefficient way of forming queries. Since KeyServer does not know if each row will exist in exported data, it will first try to INSERT a row. If this fails because the row already exists, KeyServer will then UPDATE the row.

Use REPLACE semantics

Some databases (such as MySQL) 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, select this option. For most databases, this option should NOT be selected.

Use MERGE semantics

Some databases (such as MS SQL) support a “MERGE” SQL command, which is an efficient way to insert an item into a table that might already contain that item. If your database supports MERGE, select this option.

Use PL/pgSQL stored procedure

postgresql does not support REPLACE or MERGE, but it does allow a stored procedure to be created which will conditionally INSERT or UPDATE a row. If you are using postgresql this is likely more efficient than using INSERT/UPDATE

There are three options for text encoding - the correct choice might depend on what type of RDBMS you are using but also how the specific database is configured:

Use ANSI Encoding for text fields

This is the default text encoding. With this encoding, you will not be able to see high ascii or non-ascii characters in your exported data. Such characters would most likely appear in Program Names, but could also appear in other fields.

Use UTF-8 Encoding for text fields

You can also choose UTF-8 if your target database supports it. This will allow all characters to be represented. Note: some versions of postgresql require UTF-8 text encoding.

Use Unicode Encoding for text fields

Some databases might be configured to use Unicode (UTF-16) encoding, in which case you should use this option.

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. For a small KeyServer you might export as often as every hour. For a larger KeyServer you should probably export no more frequently than once a day.

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 SQLBindParameter optimization, Use SQLBindParameter with multiple rows, Use MERGE semantics, Use Standard DATETIME
MySQL - Use REPLACE semantics, Use Standard DATETIME
Oracle - Use Microsoft-compatible CREATE TABLE, Use SQLBindParameter optimization, Use SQLBindParameter with multiple rows, Use MERGE semantics, Use Oracle-compatible DATETIME
PostgreSQL - Use PL/pgSQL stored procedure, Use UTF-8 Encoding for text fields, Use Text Times (Escape Backslashes in text fields only for old versions of postgresql)

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, check the Status Details to see what error is displayed and whether it contains enough information to understand the problem. If you can't see an error, or if it does not contain enough data, you can also configure Diagnostic logging to include more information about exporting. If you need to contact Sassafras Software Support for help in setting up ODBC exporting, we will most likely need to look at the diagnostic log.

In order for the linux version of this module to work, you will need to install unixODBC, which can be downloaded from unixODBC Project.

SQL Server/ MySQL Module

The available module depends on if the host server is running Windows (SQL Server) or Unix (MySQL). Effectively they are the same but optimized for either MSSQL or MySQL servers. They share many characteristics with the ODBC module but several options are hardcoded for simplicity and optimization of these specific database servers.

On Windows you will configure a System DSN in the ODBC Data Source tool. Note you should use the built in SQL Server module that is part of Windows, not an MSSQL Server version specific driver as the latter will often not pass authentication as expected. 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, not the SQL Server module.

MySQL requires libmysqlclient version 20. Obtaining and installing the needed client components is largely left to the customer but a linux example can be seen below. We recognize this has become difficult on MacOS since Apple ceased including the library in the OS so it may require additional 3rd party utilities. A simple mysql-client install may suffice on Linux depending on version.

wget http://security.ubuntu.com/ubuntu/pool/main/m/mysql-5.7/libmysqlclient20_5.7.28-0ubuntu0.19.04.2_amd64.deb
sudo dpkg -i libmysqlclient20_5.7.28-0ubuntu0.19.04.2_amd64.deb

The Data Source field will either be the DSN name as configured in Windows, or the host address and database name separated by a slash on Linux (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".

The Name and Password fields must be for a valid database account that has access to the database you are exporting to. If you're using a DSN for the Data Source credentials are not needed here as they are specified in the DSN. In the case of SQL Server this means you can either specify credentials in the DSN or use "With Windows NT authentication using the network login ID". The latter will allow you to run KeyServer under a domain service account and leverage that for the database export. Be careful to consider other impacts of changing the account that KeyServer runs under.

In most common cases no other Configuration options need to be changed from defaults.

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 almost identical to the MySQL module.

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.

SQLite Module

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

The configuration for this module is very simple. All that is needed is to specify a file name in the Database field. This export will create this file in the Export Files directory (or Export Modules directory in older versions).

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.

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

This module exports all databases in comma 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 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 Database Export 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 Database Export, 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.