Note: An improved method for sending data to a database is here.

WinAudit can send your audit result directly to a database. All popular databases are supported and the programme is designed to make this task as automated as possible. From the menu, select File + Database Export to show the Export box. Next, select the Data Source Name to which the records will be sent. If your database requires authentication, supply the user name and password, then click the Export button. If this is the first export, you will be prompted to create a table in the database. If that did not go smoothly, then you will need to read the rest of this page.

WinAudit auto-detects the capabilities of the database and optimises the insertion of records to achieve best performance. With some databases, you may be able to reduce signifcantly the time required to insert records by selecting the bulk insertion feature. In this mode, WinAudit collates all the data and sends it to the database in a single operation. This feature is particularly useful if you are connecting to a remote database. This mode requires more memory but the data-set is relatively small at under 1MB. With this option selected, for concurrency reasons, a client side rather than a server side timestamp is recorded. WinAudit does not automatically enable this feature because it is conditional of the capabilities of a possibly remote database. Essentially, auto-detection would entail a trial bulk insert, a check for success then a roll back.

Supported Databases

WinAudit.exe connects to databases using Open Database Connectivity (ODBC) and this ANSI version has been validated on:

dBase™ 5

Firebird® 1.5/2.0

Microsoft® Visual FoxPro® 6

Microsoft® Access 97/2000/2007/2010

Microsoft® SQL Server 6.5/2005/2008

MySQL® 3.23/4.1/5.5

Oracle® 10/11

Paradox® 5.0

SQLite 3

There are many databases and ODBC drivers on the market so WinAudit adjusts its behaviour to suit the capabilities of the database/driver combination. In theory, WinAudit should work in any database that has a Level 3 ODBC driver.

Unicode Note:
Important: Tests of WinAuditu.exe using various databases and ODBC drivers have sometimes resulted in programme instability. It is strongly advised to use production quality ODBC drivers and to verify that all your software is Unicode compatible. Also check that the database is allocating the correct number of bytes for Unicode characters. WinAudit does not transform (e.g. UTF-8) Unicode data to make it ANSI backwards compatible. No problems were observed exporting data to Microsoft® Access 2000 or Microsoft® SQL Server 2005. This does not mean that the programme's export algorithm of Unicode characters is error free.

Software Prerequisites

Before you can export your data to a database you must ensure:

1. A database exists, an empty one is acceptable as WinAudit can do the rest.
2. ODBC is installed on computer being audited.
3. The ODBC driver for the database is installed on the computer being audited.

Data Source Name

WinAudit connects to a database via a Data Source Name (DSN). A DSN is set of database connection information such as the database's name, location, etc. This information is supplied by your database administrator. After auditing your computer, on the menu select File + Database Export to display the database export box. The drop down list shows the DSNs currently on your computer. To add a new DSN click the 'Add New' button. When prompted, complete the subsequent steps. By default, the DSN is called WinAuditDSN, change this if you wish. For example, to set up a DSN called WinAuditDSN for a Microsoft® Access database located at C:\Temp\DB.mdb:

 1. Select File then Audit
 2. Select File then Database Export
 3. Click Add New
 4. Select 'User Data Source', then Next
 5. Select 'Microsoft Access Driver (*.mdb)', then Next
 6. Click Finish
 7. Click Database:Select...
 8. Navigate to C:\Temp then select DB.mdb then click OK
 9. Click OK

The entry WinAuditDSN should now appear in the drop down list.

File DSN note:
For File DSNs to be shown in the drop down list mentioned above, they must have the extension .dsn and be located as follows:

1) in the user's default DSN directory which is specified in the registry as key DefaultDSNDir at 'HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC File DSN' .

2) Only if the registry key DefaultDSNDir does not exist will the operating system's ODBC driver manager look in the directory 'ODBC\Data Sources' below the system level directory specified as key CommonFilesDir at 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion' . Typically this is 'C:\Program Files\Common Files\' .

Connection string note:
In command line mode you can pass a DSN-Less connection string to WinAudit. The string must contain the ODBC keyword DRIVER= for it to be identified correctly. Further, the string must not end with .dsn or have any forward slashes (/).

WinAudi2 Table Design

The audit is exported to a table in the database called WinAudi2, this name cannot be changed. Firstly, this ensures that in the event that users have CREATE TABLE permission, they do not inadvertently create other tables in the database. Secondly, to reduce administrative effort, the programme will attempt to create the table for you, based on the capabilities of the installed client ODBC driver.

When the first export is attempted, the database is queried to determine if the WinAudi2 table exists. This operation requires SELECT privilege. If the table does not exist, it will be created with the appropriate primary key, indices and column constraints. This operation requires CREATE TABLE privilege. As the database administrator, you probably have not granted this privilege to any users, in which case you have two options. First, create a DSN with a user name that has CREATE TABLE privilege (i.e. a database administrator) then do an export. Second, create the table manually, its design is:

Column Name	Data Type	Comments
RecordID	AUTO INCREMENT	Primary Key (Indexed, Duplicates not allowed). This column is optional because AUTO INCREMENT is not supported across all databases, but it is good database practice to include a primary key.
AuditID	INTEGER	Indexed, Duplicates allowed. An INTEGER identifying the audit report.
UserDB	VARCHAR(32)	NULLS allowed. The database logon name of the user.
DateTimeDB	DATETIME	NULLS allowed. Database time when record was inserted. If bulk insertion is used then a client side timestamp is used.
Computer	VARCHAR(32)	Indexed, Duplicates allowed. The name of the audited computer.
ItemOrder	INTEGER	NULLS allowed. An INTEGER specifying the order of the items when the report was created. This is for sorting and displaying subsequent SQL queries and does not uniquely represent the audit item.
Category	VARCHAR(24)	NULLS allowed The category of the audit report.
ItemName	VARCHAR(64)	NULLS allowed The name of the audited item.
ItemValue1	VARCHAR(64)	NULLS allowed The value of the audited item column 1.
ItemValue2	VARCHAR(64)	NULLS allowed The value of the audited item column 2.
ItemValue3	VARCHAR(64)	NULLS allowed The value of the audited item column 3.
ItemValue4	VARCHAR(64)	NULLS allowed The value of the audited item column 4.
ItemValue5	VARCHAR(64)	NULLS allowed The value of the audited item column 5.

WinAudi2 Table Permissions

For the programme to create the WinAudi2 table in the database, the user specified in the DSN must have CREATE TABLE and preferably CREATE INDEX permissions. For the programme to export the audit to the WinAudi2 Table, the user specified in the DSN must have both SELECT and INSERT permissions.

The programme does not modify existing records so UPDATE permission is not required. Likewise, access to the Data Definition Language (DDL) is not required.

The programme does not query the records in the WinAudi2 table other than to assign the next AuditID hence, the records in the database are not visible to the user. Because of the simple table design the programme will attempt to obtain a very short, table wide lock to create this AuditID.