Audits and Dynamics NAV, Part 2 – The Business Central Change Log

In part one of this series, I looked at NAV security from an audit perspective (see SOX Audits and Dynamics NAV, Part 1: “The Auditors are Here and They Have a Few Questions.”)

Here, in part two, I’ll cover two areas:

1. Setting up the change log
2. Looking at change log entries.

After security, changes to the system’s data are the next major area that auditors are concerned with. They want to know when something changed and who changed it.

I’m also working on another post that describes the various reports I’ve developed to make it possible to review the data.

I’m going to go into some detail about the NAV change log. 

But first, I want to consider the different kinds of data that are in the NAV system. 

So allow me to repeat a few definitions from my previous post:

1. Setup – Accessed only occasionally, such as payment terms. Very few folks should have access to these tables
2. Master Data – Customer, Vendor, Chart of Accounts and similar tables. Auditors are interested in these tables. 
3. Transactions – Where actual dollars are entered in the system. This is another area that auditors focus on. Multiple users will have access to these tables.
4. User Admin – These are security tables. Only administrators need access.
5. NA – Tables that no one cares about, such as comment lines. 

When thinking about the change log, we can immediately conclude we don’t care about tables like Comment Lines. 

Further, we don’t care about Transaction tables. Transactions tables in NAV (like Posted Sales Invoice or General Ledger Entries) have a User ID field which tells us who posted the record. 

Given that these posted transactions don’t change after posting*, you don’t need to track any further change information.

(* You can delete some posted transactions. But you really shouldn’t allow anyone to do that.)

The topic of who approved the posting is a separate discussion, which I may address in a future post on work flow.

Anyway, this brings us to Setup, Master Data and User Administration.  

Let’s take each one at a time.

(For more on this, you can also check out my “Understanding the Dynamics NAV Change Log.”)

These are the tables you usually setup once and rarely look at again.  

The most common are General Ledger Setup, Sales & Receivables Setup, and Purchases & Payables Setup. 

However, if we navigate to Change Log Setup: 

And then choose Tables from the Actions tab on the ribbon:

And then filter for Setup, and we see dozens of setup tables:

As you can see in the above example, I’ve set these tables to log every insertion, modification and deletion.  

You may wonder if that’s really necessary. The only table that changes regularly (in general) is the General Ledger Setup Table because we change the Allow Posting From and Allow Posting To fields:

Indeed, many of my clients don’t use all parts of the system.

However, I recommend this approach because:

1. It creates almost no overhead on the system. These tables almost never change. So, we get maybe two to three records written for the change log.

• Its easier to do this than to try to explain to my auditors what each table means.

There are three tables that store change log settings:

You should set these tables FIRST. 

That way, you’ll see the changes you actually make to the Change Log Settings in the Change Log.

Master Data includes things like Vendors, Customers, Accounts, and Items.

These are of primary interest to your auditors.

Here’s what I suggest:

1. Choose all fields for Log Insertion.
2. Choose only the fields you need for Log Modification.
3. Log all fields for Log Deletion.

I’m going to dig into each of these in more detail.

1. Choose all fields for Log Insertion

This would seem like a lot of data, but it’s not. 

If you’ve been a NAV user for any length of time, you know that NAV will save data even if you don’t click OK. 

Here’s an example: Let’s say I create a new vendor, and I ask all fields to be logged (and only some fields to be logged for the change). I then navigate to Change Log Entries and Filter for today. (I’m testing, so this is the only change I should see.) 

When I look, I see that even though I created a new entry from scratch pretty quickly, the only field that I care about that was logged was an insertion for the vendor no.  

Even the name shows up as a modification:

Logically, you might want to only log No. (and maybe Name).  

But in actuality, it’s easier to log everything because it’s less that you’ll have to explain to auditors.  

However, if you really do only want to log some fields, here’s how to do it:

Navigate to Change Log Setup and then choose Tables. Find the table you want and select Some Fields under Log Insertion:

Then click on the assist box and choose the fields you want to log:

And you’ll see the change here:

2. Choose only the fields you need for Log Modification

Setting up the change log is always a balancing act. We don’t want the log to become so big that it’s hard to find the data we want. 

So, in general, I limit what I want for each set of master data. For Vendors, it’s basically name, address and then any information that affects taxes or payments (like preferred bank account or federal tax id number).

3. Log all fields for deletion

Again, there’s rarely a reason to delete master data. So, there’s little reason not to log all fields for deletion.

Several tables are connected to user setup and security. Unlike Vendors and Items, these tables don’t have that many fields, so you want to log everything.

I would suggest logging the following tables:

One last point. I’ve found almost everything that I’d like to track in the change log. However, there is NO tracking of several key security tables, including permission and permission sets. Which is annoying.

Once you’ve set up the change log, it’s easy to see the data — but it’s not that easy to work with.

The key thing to remember is that NAV creates one record in the table for EVERY FIELD that changes.  

So, for example, let’s say you change a price. You change the end date on one record (one field) and then you create a new record with a begin date (another field) and a price (another field). So, you have three change log entries.

To give you an idea of size, a mid-size client of mine (around $100 million in sales) creates about 100,000 entries every year.

It’s important to know this because if you filter the wrong way, you can be waiting some time for the screen to return.

To look at Change Log entries, you navigate to Change Log Entries:

Like any other NAV screen, I can filter. So, I want to see any vendor numbers or names that were changed or inserted in the last three months. 

The system shows me the User (which I’ve obscured) the primary key value (the Vendor Number) and the fields that I asked for.

The problem, of course, is that if you’re tracking many fields, this screen gets old quickly. And there’s no out-of-the-box report that will give you your data in an easy-to-digest format.

I’ve developed some SQL that makes looking at this data much easier. I’m in the process of posting this to my GitHub and should have it ready shortly.  

I’ll discuss it in my next post.