GettingStarted - SteveStedman/SqlForensics GitHub Wiki

Installation

Download and run InstallSqlForensics.sql https://github.com/SteveStedman/SqlForensics/blob/master/database/InstallSqlForensics.sql

Establishing a base line

At the end of the InstallSqlForensics.sql script [ForensicLogging].[runFullMonitoringPass] will be run. You can run this at any time and it will record anything that has been changed since the last run.

EXECUTE [ForensicLogging].[runFullMonitoringPass] ;

Confirm that it is working

First make a change to something in the configuration, for instance 'show advanced options' as shown here.

EXEC sp_configure 'show advanced options', '1';
EXECUTE [ForensicLogging].[runFullMonitoringPass] ;
EXEC sp_configure 'show advanced options', '0';

Turn on Display Changes then run [ForensicLogging].[runFullMonitoringPass] again.

EXECUTE [ForensicLogging].[setSetting] 'DisplayChanges', 'True';
EXECUTE [ForensicLogging].[runFullMonitoringPass] ;

At this point you will get output showing the recent change:

Looking for recent changes

Once you have [ForensicLogging].[runFullMonitoringPass] you will want to check back occasionally to see if anything has changed. The query to check for changes is:

SELECT li.[name], cast(l.[whenRecorded] as datetime), l.[value]
  FROM [ForensicLogging].[Log] l
 INNER JOIN [ForensicLogging].[LogItems]  li on li.[id] = l.[itemId]
 ORDER BY [whenRecorded] DESC;

Which will create output that looks something like this: