Export Data - Users

To send the audit to a database, on the menu select File then Database Export 2. A window will be shown allowing you to control how the data is exported. Select the database's type (e.g. Microsoft Access). For an Oracle™ or MySQL® database select, from the adjacent list, the software used to connect to it. Next, enter the database's name into the box. Your database administrator will provide you with all the required information. Ignore the remaining options unless otherwise instructed by the administrator. Press the Export button, if requested enter your credentials. The data export will begin. The time taken for this to complete depends on how much data you are sending, so be sure to wait for it to finish. You will then see a result message indicating how many records where exported. Finally, click the Close button.

The remainder of this document is intended for the database administrator.

Administration - DBA

WinAudit allows you to send data to the database in either presentation or tabular format. The former preserves the data ordering as displayed at the user interface. Essentially, when a data record is too wide to fit on a physical A4/Letter page it is transposed to columnar format. The "System Overview" is an example of this, it is actually a single data record transposed for ease of viewing. When posted to the database it occupies serveral records. This presentation format can also make it difficult to create simple SQL statements. You can avoid these limitations by exporting your data in tabular format. In this mode, you should see a significant improvement in concurrency over the columnar format. Additionally, you can purge the database of historical audits and create simple reports. To export data at the command line use the /ODBC2 switch as described in the command line section of this documentation. The following DBMS versions are supported:

- Microsoft® Access 97 and later
- MySQL® 3 and later
- Oracle™ 9 and later
- Microsoft® SQL Server 7 and later

1. Create the Database

Next, click the Administration button, the Administrative Tasks window will be shown. Your choice of DBMS and database name are shown under the window's title. You cannot change these properties while this window is visible. Before proceeding, it is recommended that you activate the logger. If the Start Log button is enabled, click it to show the Save Document dialog, enter a log file name and click Save. If the Start Log button is disabled then the application's logger has already been activated, in which case you can view its contents by clicking the View Log button. The logger now incorporates an SQL trace facility. WinAudit does all the required checks before actually beginning to create the database so it would be unusual for it to fail once it has started. However, if an error condition is severe enough to be shown to you then the database creation has failed. Even though WinAudit wraps the data definition statements in a transaction, this is seldom sufficient for a pristine rollback so you will have no option but to manually DROP the database or its objects.

The following options are available for creating your database. The ones shown depend on your choice of DBMS.

a. Database objects only
In this mode only the objects contained within an existing database/schema will be created. If you have specified the name of an existing database check this box. For Oracle™ this is mandatory as WinAudit will not create an Oracle database. In all cases, none of the database objects that WinAudit will create can exist in the database/schema. In other words, WinAudit will not issue a DROP statement.

b. Unicode
If you wish to store Unicode characters check the Unicode box, In this context, Unicode is 2-bytes per character (UCS-2). Identifier columns used in the Computer_Master table are always in low ASCII, so Unicode (NVARCHAR/WVARCHAR) are not used for these. Hence, if you intend to run queries on this table you do not need to prefix your string literals with 'N'. No provision is made for labels of a fully qualified domain name to be in Unicode as this is a non-standard extension to the specification.

When reading or writing character data to the database, WinAudit will perform any required character conversion. This enables you to use the ANSI executable (WinAudit.exe) to communicate with your Unicode database. In practical terms, if non-Unicode capable Windows 95, 98 or Me will be connecting to the database you can still select this Unicode option.

c. Access Version
If you have specified Access as the DBMS then a drop down list will be shown. Select the version that is installed on the machine you are using. WinAudit uses OLEDB to create the Access database file (.mdb/.accdb) so this component must be installed on the machine.

d. Grant Privileges to PUBLIC
For Oracle and SQL Server databases you can specify if you want PUBLIC to have a minimal set of privileges to post data into the database. Essentially, this allows users to post but not to view or delete data. For Oracle this also involves the creation of synonyms so that connecting clients can 'see' database objects created by you. If you elect not to choose this option then you will need to implement a security arrangement that better suits your environment. See below for more details on user privileges.

e. Create ROLE WinAudit_Role
As a convenience for Oracle administrators you can create a ROLE named WinAudit_Role with the required privileges to post data into the database as described above for PUBLIC.

When you have selected the options you desire, click the Create button. Except for a non-password protected Access database you will be presented with a login dialog. Do not enter the database name as either it does not yet exist or if it does, a USE DATABASE statement will be issued when appropriate. Allow the job to run to completion then view the log file.

2. Client Connection

- the database's ODBC driver is installed on client's machine
- your clients know which DBMS they are to use
- your clients know the name of the database
- for Oracle™ and MySQL®, your client's know which ODBC driver to use

Microsoft's Access and SQL Server ODBC drivers ship with the operating system and are installed by default. ODBC drivers for MySQL and Oracle databases are available from their respective vendors. The other information shown on the window is optional. When then user clicks Export WinAudit creates a Connection String that is passed to the ODBC driver. Therefore you do not need to create a Data Source Name on the client. Normally, a login window will be displayed, usually a server name is also required. Logging of the completion connection string is suppressed if it contains a password so if there is a login error, instruct your users to copy any messages shown to them. As soon as the data transfer is complete the connection is promptly closed. Users must therefore login every time they attempt a database export. WinAudit prevents the user from posting an empty data set to the database. Likewise, the same audit data cannot be posted twice to the same database. Posting to a different database is permitted.

As mentioned above, audit data can be sent to your database via the command line. This is typically done in a batch file or logon script. This example performs a system overview and posts the result to an SQL Server database named WinAuditDB which is on the PXSSQLSVR server. If you permit trusted connections in your network environment you can use the same command for all clients.

WinAudit.exe /r=g /o=ODBC2 DRIVER={SQL Server};SERVER=PXSSQLSVR;DATABASE=WinAuditDB;Trusted_Connection=Yes;

Listed below are the options show on the data export window.

a. Maximum Errors [%]
WinAudit often sends from several hundred to a few thousand records to the database. Usually there are no errors however, occasionally some data cannot fit into the allocated column size in the database. Typically these are long file paths. It is not always desirable to fail an entire database operation because of a few errors. Consequently, you can instruct your users to set this tolerance parameter thereby allowing the operation to succeed. For example if you set this to 1% and 1000 records are sent to the database, if fewer that 10 records fail to be posted the operation as a whole will be deemed to have succeeded. The default setting is 5% with a range of 0 (no errors allowed) to 25%.

b.Maximum Affected Rows
You can specify a limit the number of records that can be posted to the database in a single operation. The default value is 9999 with a range of 1 to 9999.

c. Connect Timeout [secs]
Specify the connection timeout for the database. This setting applies to both the initial login timeout (SQL_ATTR_LOGIN_TIMEOUT) and subsequent comunications (SQL_ATTR_CONNECTION_TIMEOUT) with the database. The default value is 30s with a range of 5s to 99s. In command line mode the default value is used.

d. Query Timeout [secs]
Specify a query timeout for the database. WinAudit often sends large quantities of data to the database, depending on database load it may take some time for the query to complete. WinAudit will not use an infinite timeout. The default value is 60s with a range of 5s to 999s. In command line mode the default value is used.

3. Data Maintenance

Your database will increase in size unless you delete the old audits. WinAudit operates on the assumption that connecting clients do not have DELETE privilege, hence this step cannot be automated. From time to time you need to purge the database of its old audits. Roughly speaking, a default audit is expected to require about 0.5MB. To delete the old audits, on the Administrative Tasks window select, in the Data Maintenance section, the number of audits you want to preserve on a per computer basis. For example, if you want to keep the last 5 audits for every computer, select 5 from the drop down list. Next click the Delete button. To conserve server resources, there is a hard limit on the amount of data that will be processed in a single pass. If you see a message informing you as such, you will need to use the Delete button again.

To delete data manually you should delete records from the 'master' tables named Computer_Master and Audit_Master so that changes casacade to the actual audit data. The former holds various identifiers concerning the computer such as its name and MAC Address. The latter has timestamp information of when audits where done. They are related by the Computer_ID column. To delete a specific computer and all of its audits, delete the appropriate record from the Computer_Master table. Likewise, to delete a specific audit for a specific computer, delete the appropriate record from the Audit_Master table. For databases that do not support ON DELETE CASCADE, such as MySQL 4, you must delete records from the Audit_Data, Audit_Master and Computer_Master tables in that order.

4. Reporting

WinAudit allows you to create some simple reports from your data. This requires SELECT privilege on database tables. In the Reports section, select a report from the drop down list. You can specify a limit to the number of rows returned by using the parameter Maximum Rows. To fetch all the rows required by the report use a value of zero (0). Click Run to create the report. At present you can save the report in html format or copy it for pasting into another application.

You can, of course, use your own database's software to create reports, keep the following in mind when designing the queries.

- Data is stored in the Audit_Data table
- Audits are identified by the Audit_ID column
- The Audit_Data table may have several audits for a given computer
- The most recent Audit_ID is in Computer_Master.Last_Audit_ID
- To view current data INNER JOIN Computer_Master to Audit_Data
- Audit_Data columns are character data, avoid numerical computations

To help you create your own queries check the Show SQL box, this will show the statement used to generate the report. These statements serve as working examples which you can use to build your own queries. In particular, you will need to map the Category of data you want to its numerical identfier and the names of the columns. This information is found in the Display_Names table. For example, to get a list of installed software in your organisation:

SELECT DISTINCT
    Audit_Data.Item_1 As Software_Name
FROM Computer_Master INNER JOIN Audit_Data ON
    Computer_Master.Last_Audit_ID = Audit_Data.Audit_ID
WHERE Audit_Data.Category_ID = 500

The INNER JOIN ensures you are viewing only current data. In the Display_Names table you will see that Software Programs has a Category_ID of 500. Similarly, Item_1 corresponds to the data item of Name. The descriptions of the individual data items are shown elsewhere in this documentation.

SQL Server note:
Alternatively, for SQL Server, below are listed two contributed scripts which you can use to create views and then build your SQL queries on those.

5. Client Privileges

A dash(-) signifies that no GRANT is required for standard users. Oracle does not support GRANT SELECT on individual columns of a table so SELECT * is required for the Audit_Master table. The table PXS_Col_Attributes contains no data however, its presence is mandatory. For databases that do not support procedures SQL statements are used instead. WinAudit makes use of an empty view defined as:

CREATE VIEW v_Audit_Data_Empty AS SELECT * FROM Audit_Data WHERE 1 = 2

This is an often used technique to enable a client to determine the shape of an underlying table without it seeing any of its contents. However, if your database (Access 97, MySQL 4) does not support views then SELECT is required on Audit_Data.

6. Database Notes

This section itemises the small differences in how WinAudit behaves with different DBMS, perhaps of primary importance is the minimum supported version.

a. Microsoft® Access
Minimum supported version: Access 97
Stored Procedures: Requires Access 2000/Jet 4 or later
Cascading Deletes: Requires Access 2000/Jet 4 or later
Empty Data View: Requires Access 2000/Jet 4 or later
Unicode: Requires Access 2000/Jet 4 or later
GRANTS: None issued, use the Security Wizard to setup the privileges

b. MySQL®
Minimum supported version: 3.23
ODBC Driver: Ensure you are using the latest release of the v3.51 driver
Stored Procedures: MySQL v5 required
Cascading Deletes: MySQL v5 required
Empty Data View: MySQL v5 required
Unicode: No
GRANTS: None issued, a GRANT creates a user

c. Oracle™
Minimum supported version: Oracle 9
ODBC Driver: Ensure you are using Oracle's own driver
Unicode: UCS-2
ROLE: Optional WinAudit_Role
GRANTS: Optional for PUBLIC

Schema: WinAudit must be able to query the database in a schema neutral fashion. Given the small number of database objects, a convenient solution to this is to create synonyms. WinAudit will create these for WinAudit_Role and PUBLIC if you checked those options when the database was created. If you decided that this was not appropriate for your environment then you must ensure that connecting clients can run SQL statements without specifying the schema owner. For example, a statement of this type must succeed:

SELECT Computer_ID
FROM Computer_Master
WHERE WinAudit_GUID = '{nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn}'

Create/Drop Database: WinAudit will not create a new Oracle database. You are required to nominate an existing one. Child objects are then created in this database. If at a later date you wish to DROP these child objects you must run the following statements in the given order. The word schema is a placeholder for the user name used when the database objects were created.

DROP PUBLIC SYNONYM Audit_Data;
DROP PUBLIC SYNONYM Computer_Master;
DROP PUBLIC SYNONYM v_Audit_Data_Empty;
DROP PUBLIC SYNONYM pxs_sp_insert_audit_master;
DROP PUBLIC SYNONYM pxs_sp_insert_computer_master;
DROP ROLE WinAudit_Role;
DROP PROCEDURE schema.pxs_sp_insert_audit_master;
DROP PROCEDURE schema.pxs_sp_insert_computer_master;
DROP SEQUENCE schema.Computer_Master_Seq;
DROP SEQUENCE schema.Audit_Master_Seq;
DROP SEQUENCE schema.PXS_Col_Attributes_Seq;
DROP VIEW schema.v_Audit_Data_Empty;
DROP TABLE schema.PXS_Col_Attributes;
DROP TABLE schema.Display_Names;
DROP TABLE schema.Audit_Data;
DROP TABLE schema.Audit_Master;
DROP TABLE schema.Computer_Master;

d. Microsoft® SQL Server
Minimum supported version: SQL Server 7
Unicode: UCS-2
GRANTS: Optional for PUBLIC

7. Database Table Design

The database consists of 5 tables as described below. The Computer_Master table contains information for computer identification. Ideally, there should be one record per computer however, computer identification is heuristic as hardware changes or hard drives are reformatted. The Audit_Master table contains one record for each audit job. Each job has an associated Audit_GUID generated by the client machine when the audit is run. Duplicates of this GUID are not allowed thus preventing the client from posting the same data set twice. There are 4 timestamp columns, the suffix UTC represents Univeral Coordinated Time. Likewise, the suffix locale represents the time displayed on the computer's clock. This table is joined to Computer_Master via the column Computer_ID. The table Display_Names contains one record for each data category such as System Overview. Each category has a numerical indentifier that is a mutiple of 100. The names of the data items in a category are shown in the remaining columns, of which there are 50. The table PXS_Col_Attributes is unused but must be present. Finally, the table Audit_Data contains the data items of an audit job. This table is joined to Audit_Master via the column Audit_ID. The order in which these were inserted into the database is recoverable using Record_Ordinal. A record is a collection of data items belonging to the same category. The numerical identifier of which is in the Category_ID column. The computer to which the data pertains is stored in the Computer_ID column. The data items themselves are stored in 50 string columns numbered Item_1 to Item_50. The first 25 are 255 characters wide, the remaining are single character columns.

The WinAudit programme collects items of data which are then grouped into the named data categories. A logical database design would be to have one table per data category. However, this proved impractical as an audit job typically posts a large number records thereby forcing the database to hold many write locks on nearly all of its the tables. This consumes a lot system resources and reduces concurrency. Additionally, inserting records into multiple tables means the entire audit job cannot be sent to the database in a single operation resulting in more network traffic and a further reduction in concurrency. Consequently, a simplistic approach is adopted with all the audit data items posted into the Audit_Data table.

Table Computer_Master:

Table Audit_Master:

Table Display_Names:

Table PXS_Col_Attributes:

Table Audit_Data:

Notes: for Oracle™ an AUTOINCREMENT column is implemented as SEQUENCE and TRIGGER combination. In all cases WVARCHAR is used only if Unicode was specified on database creation otherwise it will be VARCHAR. REFERENCES is only used with databases that support referential integrity.

Database Scripts

/********************************************
Author: David Rike
    [email protected]
Date: 8/28/2007
Inputs :
    @col int Range 1-50
    @categoryID int Range 300-10000
Purpose:
    Helper function for stored procedure: CreateAuditViews that finds the Display Name for data in Audit_Data Table
Note:
    Created and used for SQL Server 2005, but should work fine in SQL Server 2000

*******************************************/
CREATE FUNCTION [dbo].[GetColumnName]
(
    @categoryID int,
    @col int
)

RETURNS NVARCHAR(36)

AS

BEGIN

    IF @col = 1
        RETURN (SELECT Item_1 FROM Display_Names WHERE Category_ID = @categoryID)
   IF @col = 2
        RETURN (SELECT Item_2 FROM Display_Names WHERE Category_ID = @categoryID)
   IF @col = 3
        RETURN (SELECT Item_3 FROM Display_Names WHERE Category_ID = @categoryID)
   IF @col = 4
        RETURN (SELECT Item_4 FROM Display_Names WHERE Category_ID = @categoryID)
   IF @col = 5
        RETURN (SELECT Item_5 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 6
        RETURN (SELECT Item_6 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 7
        RETURN (SELECT Item_7 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 8
        RETURN (SELECT Item_8 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 9
        RETURN (SELECT Item_9 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 10
        RETURN (SELECT Item_10 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 11
        RETURN (SELECT Item_11 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 12
        RETURN (SELECT Item_12 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 13
        RETURN (SELECT Item_13 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 14
        RETURN (SELECT Item_14 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 15
        RETURN (SELECT Item_15 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 16
        RETURN (SELECT Item_16 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 17
        RETURN (SELECT Item_17 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 18
        RETURN (SELECT Item_18 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 19
        RETURN (SELECT Item_19 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 20
        RETURN (SELECT Item_20 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 21
        RETURN (SELECT Item_21 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 22
        RETURN (SELECT Item_22 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 23
        RETURN (SELECT Item_23 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 24
        RETURN (SELECT Item_24 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 25
        RETURN (SELECT Item_25 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 26
        RETURN (SELECT Item_26 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 27
        RETURN (SELECT Item_27 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 28
        RETURN (SELECT Item_28 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 29
        RETURN (SELECT Item_29 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 30
        RETURN (SELECT Item_30 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 31
        RETURN (SELECT Item_31 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 32
        RETURN (SELECT Item_32 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 33
        RETURN (SELECT Item_33 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 34
        RETURN (SELECT Item_34 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 35
        RETURN (SELECT Item_35 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 36
        RETURN (SELECT Item_36 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 37
        RETURN (SELECT Item_37 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 38
        RETURN (SELECT Item_38 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 39
        RETURN (SELECT Item_39 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 40
        RETURN (SELECT Item_40 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 41
        RETURN (SELECT Item_41 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 42
        RETURN (SELECT Item_42 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 43
        RETURN (SELECT Item_43 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 44
        RETURN (SELECT Item_44 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 45
        RETURN (SELECT Item_45 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 46
        RETURN (SELECT Item_46 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 47
        RETURN (SELECT Item_47 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 48
        RETURN (SELECT Item_48 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 49
        RETURN (SELECT Item_49 FROM Display_Names WHERE Category_ID = @categoryID)
    IF @col = 50
        RETURN (SELECT Item_50 FROM Display_Names WHERE Category_ID = @categoryID)
    RETURN null
END

USE [WinAuditDB]
GO
/****** Object: StoredProcedure [dbo].[CreateAuditViews] Script Date: 09/05/2024 11:10:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/********************************************
Author: David Rike
    [email protected]
Date: 8/28/2007
Inputs:
    None
Purpose:
    Creates a View for each category in the Display_Names Table
Note:
    Created and used for SQL Server 2005, but should work fine in SQL Server 2000
    except for the drop statement. If using SQL Server 2000 change
    "sys.views WHERE object_id = OBJECT_ID"
    To
    "dbo.sysobjects WHERE id = OBJECT_ID"
Usage:
Here is an example to get last Audit Information using the System View
SELECT
    Manufacturer, ProductName, Version, SerialNumber, UniversalUniqueID, WakeUpType
FROM
    Computer_Master cm
INNER JOIN
    System s
ON
    cm.Last_Audit_ID = s.auditID
/********************************************/
Revisions:
9/4/2024
1. I had made a false assuption that for the Item_1 - Item_50 in the Display_Names table that
there wouldn't null values between Items, which would short-circuit the inner WHERE loop; this has been corrected.
2. Added Record_Ordinal (renamed RecordID) to each of the views. I needed this to remove duplicate installed software items.

*******************************************/
CREATE PROCEDURE [dbo].[CreateAuditViews]

AS

DECLARE @categoryID INT
DECLARE @sql varchar(8000)
DECLARE @name varchar(255)
DECLARE @tableName nvarchar(128)

--one of the categories has duplicate column names, so name them 1 and 2
DECLARE @sourceNameNum INT


SELECT @categoryID = 200
WHILE @categoryID < 10000

BEGIN
    SELECT @sourceNameNum = 1
    SELECT @categoryID = @categoryID + 100
    SELECT @tableName = null
    SELECT @tableName = REPLACE(REPLACE(REPLACE(Category_Name, ' ',''),'-',''),'/','') FROM Display_Names WHERE Category_ID = @categoryID
    DECLARE @x int
    SELECT @x = 1
    IF @tableName IS NOT NULL
    BEGIN
        --2 Categories are duplicates, concatenate the categoryID to them
        IF @tableName = 'Processor'
            SELECT @tableName = 'Processor' + CONVERT(VARCHAR(4), @categoryID)
        DECLARE @sqlInto varchar(500)
        SELECT @sqlInto = 'CREATE View ' + @tableName + char(13) + 'AS' + char(13)

        --begin sql
        SELECT @sql = 'SELECT ' + char(13) + char(9)
        --loop through columns 1 - 50
        WHILE @x < 51
        BEGIN
            --call helper function to get column name
            SELECT @name = (SELECT dbo.GetColumnName(@categoryID, @x))
            IF @name IS NOT NULL
            BEGIN
                IF @sql <> 'SELECT ' + char(13) + char(9)
                    BEGIN
                        SELECT @sql = @sql + ', ' + char(13) + char(9)
                    END
                ELSE -- add audit and computer ID
                    BEGIN
                        SELECT @sql = @sql + 'Audit_ID as auditID, ' + char(13) + char(9) + 'Record_Ordinal as recordID, ' + char(13) + char(9) + 'computer_ID as computerID, ' + char(13) + char(9)
                    END
                SELECT @name = REPLACE(REPLACE(@name,' ',''),'-','')
                IF @name = 'SourceName' --this is the dup column name
                BEGIN
                    SELECT @name = @name + CONVERT(VARCHAR(3),@sourceNameNum)
                    SELECT @sourceNameNum = @sourceNameNum + 1
                END
                SELECT @sql = @sql + 'Item_' + CONVERT(varchar(2), @x) + ' As [' + REPLACE(REPLACE(@name,' ',''),'-','') + ']'
            END

            --Found out that some display columns had nulls
            --ELSE
                --SELECT @x = 100
            SELECT @x = @x + 1
        END
        --this probably isn't needed anymore
        IF @sql = 'SELECT '
            SELECT @sql = @sql + CONVERT(varchar(4), @categoryID) + ' As [Category Not Present]'
        --finish up with the from and where clause
        SELECT @sql = @sql + char(13) + 'FROM' + char(13) + char(9) + 'Audit_Data As [' + REPLACE(@tableName, ' ','') + ']'
        SELECT @sql = @sql + char(13) + 'WHERE' + char(13) + char(9) + 'Category_ID = ' + CONVERT(varchar(4), @categoryID)

        --create a drop statement and execute
        DECLARE @dropSQL varchar(1000)
        SELECT @dropSQL = '
    IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N''[dbo].[' + @tableName + ']''))
    DROP VIEW [dbo].[' + @tableName + ']'
        --PRINT @dropSQL
        EXEC(@dropSql)

        --PRINT @sqlInto + @sql
        EXEC(@sqlInto + @sql)

        --uncomment this and change the username if SQL User has explicit permissions
        EXEC('GRANT SELECT ON [' + @tableName + '] TO WinAuditUser')
    END
END