Visual Audit Trail

See What You’ve Been Missing

http://www.visual-audit.homip.net/

 

Introduction

           

There are a lot of different names for them.  ERP, MRP, CRM and HR applications are all too common in today’s businesses.  Most of these applications perform well in their duties except for one thing.  Keeping track of users activities within the databases.  Whether too costly or to difficult to implement in an off the shelf type product, software developers are leaving out this critical function.  Visual Audit Trail is a tool that creates audits in the database to log what users are doing. 

 

What can it do?

            Visual Audit Trail is very flexible.  It allows you to audit what you want, when you want.   

 

 

Requirements

            Windows 95/98, ME, 2000, XP, NT4

            DCOM98 if using windows 95/98

            Centura SQLBase 7.5.x or MS Sql Server 7/2000

            Gupta SQLBase Driver Pack 8.1

 

Installation Instructions

1. Run setup.exe follow on screen instructions.

2. Unzip SQLBase81DriverPack.zip.

3. Run setup for Centura’s driver pack.

4. Double-click on the file named test.udl.  Under the properties tab, ensure that there is and entry named “Gupta SQL Base OLE DB Provider”.  This must match exactly or the driver may not have installed properly.

5. Run Visual Audit from your Start Menu

 

Using Visual Audit

 

After launching the program file, you will see the login window, (the one for sqlserver is similar to this one).  You must enter your  server name, the database you want to connect to and the sysadm password for Sqlbase and the sa user password for SQLserver.

 

 

 

If login succeeds, you will be shown the main Visual Audit Trail window. Fig 1.

 

Next, we will step through the creation of an insert audit.

 

Step 1.

 

We must give our audit a name and description.  I the box called audit name, enter a short name that will identify this audit.  The name may be no longer than 13 characters long.  It must contain only numbers or letters and NO spaces.  The description should describe what the audit actually does.  The description box may also only have numbers or letters but may contain spaces.  See fig. 1

 

Figure 1

Step 2

 

We are going to create an insert audit on the part table.  An insert audit will log anytime someone adds a new record to the part table.  So lets scroll down the Visual tables list until we find the PART table listed and click on it.  Notice the columns list box has filled with a bunch of stuff.  These are all the fields in the part table.  Selecting a field from the columns list box adds it to out audit.  These are the items that get saved and should contain enough information so we can identify the record being audited.  For our example, lets just select ID and DESCRIPTION.  Being that this will be an insert audit, be sure to click “Inserting a new record”, in the section called “When to Audit”.  See fig 2.

 

 

Figure2

 

 

Step 3

 

At this point, we could actually save the audit by click on the GO button.  This would create an insert audit that would log any new entries into the part table.  In addition to the 2 fields, (ID and Description) that we chose in the columns list, Visual Audit Trail also adds the user name of the person who entered the part and the date and time it was entered.  This is done automatically for all audits.

 

But rather than save this audit, we are going to do a conditional statement as well.  Lets look at the section at the bottom called “Conditional Processing”.  Here is where we can set conditions for our audit.  The first thing we need to do is determine our audits normal behavior.  There are a few of options and they are determined by the, “Audit only when” and “Audit always except” buttons.

 

  1. Audit will always “Fire”
  2. Audit will always “Fire” except if certain condition or conditions exist
  3. Audit will not “Fire” unless certain condition or conditions exist.  In this case, a conditional expression must be specified or the audit will never “Fire”.

 

We are going to use number 2 and add a condition.  We don’t want our part audit to log when the user SYSADM enters a part so we need to choose USERID from the dropdown labeled “This field is”.  We want the USERID to be “Equal to” SYSADM so enter that in the box “This value”.  Now, if we read this from left to right you can see that we will “Audit always except if the USERID field is equal to the value SYSADM”.  Click on the add button to add this criteria.  See fig. 3

 

Figure 3

 

 

Step 4

 

Click the GO button.  If all goes well, all the information you entered should get cleared and the audit will have been saved to your database.  Click on the “Installed audits” tab at the top.  You should see the audit you just created. Fig 4. But wait, that’s not what you named it.  Why is it different?  Visual Audit Trail for Sqlbase adds “BMRA” to the beginning of every audit to distinguish it from other items in your database.  This is not true for when using the Sqlserver version

Figure 4

 

 

Now, every time someone enters a part who is not logged on as SYSADM it will get logged and you can see it here by highlighting the row and click view.

 

Figure 5

 

 

 

A delete audit can be created in exactly the same manner as an insert audit.  Only difference is that the audit “Fires” when a record is deleted instead of added.  Now let’s look at an update audit.  Click on the tab Update Audits.  Notice that this screen is very similar with a few additions.  In the columns list there is a check box “Save old value”.  This tells Audit Trail that you want to save what was in this field before it was changed.  Also notice there is a new list, “If these fields changed”.  This allows you to choose which fields must be changed to “Fire” the audit.  In the example in figure 6 we are creating an audit that fires when a part description is changed.  It will only audit when a change has been made to the description.  The audit will save the id field and also the description field before and after it was changed.  That way, you know what it used to be.  As always, the user who changed it and the date and time are added.  The conditional section can be used just like for the insert and delete audits.

 

Figure 6

 

 

 

Here are the results of our update audit.  Notice the column named ODESCRIPTION (old description field).  This is what the part used to be called.  It was renamed to DO NOT USE.

 

 

 

 

 

 

Here are some other important things to remember when using Visual Audit Trail.

 

  1. Always test your audits on a non-production database first.  Visual Audit Trail does not make any changes to data in other tables but it’s always a good idea to test anyway.
  2. If you use the Audit only when option, make sure you enter a condition at the bottom or the audit will never “Fire”
  3. After you enter the first conditional statement, the option buttons in the conditional section get locked and you cannot change them.  You can hit the reset button to reset the form.
  4. Audits can be disabled on the “Installed Audits” page. This is useful for cases where you would have an update audit on a table that gets changed by a batch process.  For instance if you have an update audit on the part table and want to do a physical inventory.  It may be wise to disable the audit temporarily until the physical inventory process is finished.  This does not delete the audit.
  5. If you export your data to Excel, make sure you have Excel installed on your computer or export will fail.
  6. There are an infinite number of possible audits you can create with Audit Trail.  With such versatility there may be combinations of items that do not act as expected.
  7. Happy Auditing.