Activity Tracking

So this entry in the blog comes courtesy of this post right here.  In short the answer to my question was YES when you run a query against a view that has table auditing tied to one of the tables in that view, it is captured by the audit.  Now as to why I didn’t know that answer  I can’t say but now I do. What I’d like to do today is guide you through setting up table audits in your Dynamics GP SQL server environment to help capturer who may be simply viewing a window or table in Dynamics.  To help keep this guide simple I am going to use the customer master table in GP as my example table.

The Scenario

So you have setup Activity Auditing in Dynamics GP and that is fine.  You are able to see who is changing data inside of GP, but what about who is just simply looking at the data?  Yes Activity Tracking provides reports on successful attempts to open a window in GP.. But what if the users are consuming information outside of GP?


Target Locked On!

Ok so the first thing we need to do is identify what tables in our database we are looking to audit.  In my instance I want to know who is looking at customer information so I’m going to implement an audit on the RM00101 table which is the customer master table.  If you are not sure of the table names please visit for a complete listing of tables. (I love that site BTW!)


With our table decided the first thing we need to do is get logged into SQL as a user with admin privileges.  Then open a new query window and paste in the following code.


Code is below

USE master ;
-- Create the server audit. (Step 1)
CREATE SERVER AUDIT Customer_Master_Audit
'C:\TEMP' ) ; –If you are using a network share it would be ‘\\servername\networkshare’

-- Enable the server audit. (Step 2)
ALTER SERVER AUDIT Customer_Master_Audit
-- Move to the target database. (Step 3)
-- Create the database audit specification. (Step 4)
FOR SERVER AUDIT Customer_Master_Audit
ON dbo.RM00101 BY dbo )

So in the code above we are going to create a server audit and title it Customer_Master_Audit and I’m going to place the log file in the TEMP directory on my laptop.  In most cases you would store this on a network share. The second part of this code turns the audit on.  The third switches you to your company database.  Then the last bit says I want to audit for any SELECT or Insert statements against the table of my choice and the BY represents logging for what users.  One thing to note is that when I run the above code I run it one section at a time to make sure I don’t make any mistakes.



So once you have the audit created you will be able to see it in SSMS as pictured below.  One thing you will want to do is set some basic options like how large you want to allow your log to grow to, how many times you want the log to overwrite itself before it creates a new file.



Reading your audit

So now you have your audit in place lets test it out.  The first thing we are going to do is simply run a SELECT * FROM dbo.RM00101


So we created the audit, we’ve set some properties, and then we ran a simple select all against our table.  Now lets examine what SQL logged. The SQL statement to retrieve the log is below.  You will want to replace the file name with the file that SQL generated for you.
SELECT * FROM sys.fn_get_audit_file ('C:\Temp\Customer_Master_Audit_8DC13D10-B9C2-4FB0-A78C-3C6F1F355E21_0_130504533654400000.sqlaudit',default,default); GO



There it is.  Your logged information in all of it’s glory.  There is a large amount of information that gets captured in this audit.  Below is a list of all the columns that are available so if you want to narrow down your result set you may.




This completes the post on how to turn on auditing in SQL.  Now this post just barely touches the surface of the subject of auditing in SQL.  If you want to get a more detailed view of it I would recommend reading the BOL subject matter as well as this link

4 thoughts on “Activity Tracking

  1. haid tidak teratur Reply

    That is a very good tip especially to those new
    to the blogosphere. Short but very precise information… Thanks for sharing this one.
    A must read post!

  2. tinnitus Reply

    Hiya! Quick question that’s totally off topic.
    Do you know how to make your site mobile friendly?
    My weblog looks weird when browsing from my iphone 4.
    I’m trying to find a theme or plugin that might be able to resolve this problem.
    If you have any recommendations, please share. Appreciate it!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.