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 https://www.gptablereference.com/ for a complete listing of tables. (I love that site BTW!)

Foundations

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.

1_Execute_Create_Audit

Code is below

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

GO
-- Enable the server audit. (Step 2)
ALTER SERVER AUDIT Customer_Master_Audit
WITH (STATE = ON) ;
GO
-- Move to the target database. (Step 3)
USE FAKEC ;
GO
-- Create the database audit specification. (Step 4)
CREATE DATABASE AUDIT SPECIFICATION Audit_Customer_Tables
FOR SERVER AUDIT Customer_Master_Audit
ADD (SELECT , INSERT
ON dbo.RM00101 BY dbo )
WITH (STATE = ON) ;
GO

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.

2_Complete_Creation

Results

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.

3_Properties1

3_Properties2

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

4_Test

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

 

6_Complete

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.

Log_Columns

 

Summary

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 http://msdn.microsoft.com/en-us/library/cc280386.aspx

jcox@hexoma.com
4 Comments

Add a Comment

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