Auditing Your SQL Server Part 4 - Selective Auditing
Part 4 of this series looks at only auditing selected information in a table. The previous parts of this series covered the following topics:
- Part 1 - Simple auditing of a table with a trigger and a couple additional fields.
- Part 2 - Using a mirror table.
- Part 3 - Managing your archive data.
This article will examine some of what is done in Part 2, but expand this to more tables without duplicating everyone of your tables.
Selective Auditing
Auditing is always a double edged sword. When you need to determine what happened, you want all the information that you can possibly have. However when you don't have issues, you have this huge volume of information to wade through, most of which doesn't really make sense. Balancing these two can be difficult, but it is something that you want to keep in mind and think long and hard about what you audit and how much you keep. You also want to be sure that you capture enough information that you can trace back the true issues that you face.
So how do you decide what you audit? Well, the first place I start is with any regulations that apply. If you work in the financial arena, chances are that your database needs to audit any transactions that take place with some type of audit trail. If you manage a database the runs a meat packing plant, you might need to audit slightly different items. I usually start by looking back at issues in the past where you have not been able to determine what happened or who made a change. These are usually prime fields for auditing. Since I don't know your system, I'll work with one that I do know, an Asset Tracking system. This system is designed to ensure that assets in the company are accounted for and maintained properly. Now there is quite a bit of data work that has to be manually input, and this system can't control that, instead, it's designed to ensure that what data is put into the system is intact and is not altered.
In working with the design of this system, we looked at how people actually use this data. The original model was with an Excel spreadsheet, which by default, prevented people from overwriting changes made by someone else. Excel essentially "table" locked the entire set of data. It also allowed us a last change trail in that the file itself, the XLS, was tracked by NTFS to show the account of the person that last changed it. Since one guy made most of the changes, if he ever opened Explorer and saw another account on this file as the last change account, he would go find that person and properly chastise them.
Worked great for data integrity, not so great for efficiency. It also didn't allow this individual to determine when he last changed a piece of data. Or which piece of data was changed. So I implemented a small, but effective method of auditing data.
A few caveats
First, I don't audit everything. There are a number of pieces of data that are not that important. Relatively, at least, so these are ignored. Mainly the items that are in FK tables are the important ones and we discussed those that need to be audited. This is an ongoing and flexible arrangement and one that occasionally changes as requirements change.
Secondly, this is a low transactional system. And when I say low, I'm talking maybe a dozen or two changes per day. So this method of auditing may or may not work on a more active system. Again, you'd have to debate this method and develop an archive plan as well. As it is, we're talking kb of data per year for this system, so I haven't built an archive plan. Though I could easily add it.
My Method
So here we go. Let me take describe a typical audit area with some DDL, but keep in mind that there are probably a dozen or so tables that this same method is used on. Just slightly different fields.
First, let's look at the location table. It's a separate table from the main table that stores the server information. There is a FK to the PK of the Server table, which is the ServerID, an int. Here's the DDL (not complete, relevant parts only):
create table Server ( ServerID int identity(1,1) , ServerName varchar(80) , ServerDesc varchar(2000) , Active tinyint ... ) go create table Location ( Location varchar(20) NOT NULL -- Unique location within the company. , ServerID int NULL , LocationDesc varchar(500) NULL , Active tinyint NOT NULL default (1) ... )
Here you can see we have two tables that are linked. The audit requirement here is to know when the location of the server changes. So if a server is moved to another location, retired, whatever, we know this. But the change is two things: the server changes and the location changes. The location is "blanked" out when the server is removed. Or perhaps construction occurs and it is marked as such, nothing can ever go there again (because there's a wall there). Either way, the change is marked more as the location change than the server change. If a server moves, we actually have two changes; the old location is marked as "blank" and the new location is marked as having the server in it.
I hope this makes sense, because at first we worried about auditing the Server table for the location change. A little debate and analysis yielded that the Location table is actually where we wanted to mark the change and audit the data. So we implemented our audit there, similarly for power, network, IP, etc.
During the audit discussion, we determined that the location description and other meta data wasn't important for auditing purposes. If a rack changed from a Compaq to an IBM rack, that wasn't a relevant issue. If the description changed to reflect that the power supplies were on the left, that wasn't important. The main thing we needed to capture was a change in the status of the location or in the particular asset that was located there.
The first step was to implement the audit table. Here's the DDL for that table:
create table Location_Audit ( Location varchar(20) NOT NULL , ServerID int , Active tinyint , Modified datetime default getdate() , ModifiedBy varchar(40) default suser_sname() )
Pretty simple and with my incredibly intuitive naming convention, easy to keep track of. We have the PK from the main table (Location) as well as the fields we want to audit (ServerName and Active) and we add two fields from Part 1 that track the change time and person. This allows us to store changes to one of both of our audit fields along with a timestamp in a minimal amount of space. We are not duplicating the description field since we don't care about that from an audit perspective. A failing or issue with Part 2 of my series.
Now all that's left is a trigger to implement this audit. A few things about the trigger before we look at the DDL. First, we don't want to mark every change in this table. If the description or some other field change, then we are inserting a row of data that would look exactly like the previous row. Something we don't want to do. If someone goes back to look at this data, seeing duplicate rows adds confusion and will have them question the effectiveness of the process. Second, we want to be sure that we get the proper values. In any change, there are really two sets of values; the old and the new. But which one do we store?
There are arguments for both sets of values. If I store the new values, then the previous row in the audit tables has the old values. If I store the old values, then the current values in the table have the new ones. Or the next row has the new ones. If I want to store the new values, then I have to be sure I "Preload" the first or initial values. If I store the old values, then my audit query has to include a join to the Location table to get the last row for each location. Since we tend to audit a specific location or server, this isn't a big deal. Either method works, however, once I decide, I cannot change. Or at least not without reloading the entire table the right way. I decided to load the old values and keep the audit data to a minimum (not that there is a big cost in this). So here's the DDL for my trigger:
create trigger location_audit_triud on location for insert, update, delete as insert location_audit ( location, serverid, active, modified, modifiedby) select d.inserted , d.serverid , d.active , getdate() , suser_sname() from inserted i inner join deleted d on i.location = d.location where i.serverid <> d.serverid OR i.active <> d.active OR i.serverid is null OR d.serverid is null
A few things to note. We are handling bulk changes. NEVER assume that only one row changes with a trigger. Might be simpler to code if it did, but SQL Server doesn't work this way (at least through v2000). Also, we only insert those rows from the deleted table where one of two things has happened. The Server changed or the Active flag changed. If someone updated the description and nothing else, no rows would be added to the audit table. If I somehow changed the server in location 1 and the active flag for location 2 in the same transaction, then two rows would be added.
A few examples where I show the before and after contents of the Location and Audit tables. This I think is simpler than seeing the DDL. Here we add a server to previously blank location 1.
BEFORE: Location: Location_Audit Location ServerID Active Location ServerID Active Modified ModifiedBy ----------- --------- ------ ----------- ---------- -------- --------- ------------ A1 null 0 A2 null 0 update location set serverid = 1, active = 1 where location = A1 AFTER: Location: Location_Audit Location ServerID Active Location ServerID Active Modified ModifiedBy ----------- --------- ------ ----------- ---------- -------- ------------ ------------ A1 1 1 A1 null 0 5-3-04 13:00 Steve A2 null 0
Here's a look at moving a server from location A1 to location A2.
BEFORE:
Location: Location_Audit
Location ServerID Active Location ServerID Active Modified ModifiedBy
----------- --------- ------ ----------- ---------- -------- --------- ------------
A1 1 1 A1 null 0 5-3-04 13:00 Steve
A2 null 0
update location set serverid = null, active = 0 where location = A1
update location set serverid = 1, active = 0 where location = A2
AFTER:
Location: Location_Audit
Location ServerID Active Location ServerID Active Modified ModifiedBy
----------- --------- ------ ----------- ---------- -------- ------------ ------------
A1 null 0 A1 null 0 5-3-04 13:00 Steve
A2 1 1 A1 1 1 5-3-04 13:02 Steve
A2 null 1 5-3-04 13:03 Steve
Note that the two update statements generate a couple of audit rows. Here's a bulk update that removes all servers from locations A1 and A2. First we add another server to location A1.
BEFORE:
Location: Location_Audit
Location ServerID Active Location ServerID Active Modified ModifiedBy
----------- --------- ------ ----------- ---------- -------- --------- ------------
A1 null 0 A1 null 0 5-3-04 13:00 Steve
A2 1 1 A1 1 1 5-3-04 13:02 Steve
A2 null 1 5-3-04 13:03 Steve
update location set serverid = 2, active = 1 where location = A1
update location set serverid = null, active = 0 where location = A2
AFTER:
Location: Location_Audit
Location ServerID Active Location ServerID Active Modified ModifiedBy
----------- --------- ------ ----------- ---------- -------- ------------ ------------
A1 null 0 A1 null 0 5-3-04 13:00 Steve
A2 null 0 A1 1 1 5-3-04 13:02 Steve
A2 null 1 5-3-04 13:03 Steve
A1 null 0 5-3-04 13:03 Steve
A2 1 1 5-3-04 13:05 Steve
A1 2 1 5-3-04 13:05 Steve
Note that there were two rows added for the second update, one for each location.
The last part of this article looks at reading back and interpreting the data. As you can imagine over time, there will be lots of data in the audit table. Especially if you consider in my case, nearly 1,000 servers and a few hundred racks. So in general, you search for the audit trail for a single server. Using the examples above, let's look at the trail for A1.
select location , isnull( serverid, 'Empty') 'ServerID' , active , modified , modifiedby from location_audit where location = 'A1' order by modified Location_Audit Location ServerID Active Modified ModifiedBy ----------- ---------- -------- ------------ ------------ A1 Empty 0 5-3-04 13:00 Steve A1 1 1 5-3-04 13:02 Steve A1 Empty 0 5-3-04 13:03 Steve A1 2 1 5-3-04 13:05 Steve
To interpret this, you look at A1 as starting with nothing in the location (NULL), which is translated to "Empty" by the function. This Server 1 is put there, then removed, then Server 2 is back there. But that's not the whole story. There's also the current value of the location, which is empty. To truly do this, we need to include the current value of the location table, which we can do as follows:
select location , isnull( serverid, 'Empty') 'ServerID' , active , modified , modifiedby from location_audit where location = 'A1' union select location , isnull( serverid, 'Empty') 'ServerID' , active , getdate() 'modified' , 'Present Value' 'modifiedby' from location where location = 'A1' order by modified Location_Audit Location ServerID Active Modified ModifiedBy ----------- ---------- -------- ------------ ------------ A1 Empty 0 5-3-04 13:00 Steve A1 1 1 5-3-04 13:02 Steve A1 Empty 0 5-3-04 13:03 Steve A1 2 1 5-3-04 13:05 Steve A1 Empty 0 5-3-04 14:05 Present Value
This adds in the latest value (because of getdate()) and places "Current Value" in the last column.
Much of the code used to write this can be downloaded here.
Conclusions
I hope that this helps you in your auditing requirements a little. It's another method that I have more often used than those in Part 1-3. This one usually has a minimal load and does the job. It's not as centralized as some solutions you can purchase or as easy, but it does allow selective auditing in places where you need it. As with everything, be sure you test this before implementing it.
And be sure to give me any feedback, positive or negative.
Steve Jones
©dkRanch.net May 2004