/*
	Pentana Audit - A consolidated history of logged events

	Many user activities are recorded in LOG tables throughout the application.  While you can view activity for a particular record in the
	properties panel log viewer there is no centralised viewing screen, which makes it difficult to view a user's activity across the system.
	
	This script builds a temporary table and copies in some basic information from all existing log tables, enabling a simple overview of 
	activity.
	
	Each row in the table contains:
	
		- the name of the table that the log entry came from
		- the Name of the parent record (where available)
		- the name of the user that made the change
		- the time that the change was made
		- the type of change (add, modify, delete)
		
	This has the potential to build a huge table, so the date range examined is limited, controlled via two variables.  The default period is
	(Today - 7 days).  Change these variables at the top of the script.
	
	You can query the table to find e.g.
	
	-- A history of all events
	SELECT * FROM #EventHistory 
	ORDER BY ByTime;
	
	-- Events from a particular person
	SELECT * FROM #EventHistory
	WHERE ByName = 'Some Person'
	ORDER BY ByTime;
	
*/

-- Set the date range here
	DECLARE @STARTTIME VARCHAR(50) = CONVERT(VARCHAR, (DATEADD(DAY,-7,GETDATE())), 23);			-- This is the start, specify as 'yyyy-mm-dd'
	DECLARE @ENDTIME VARCHAR(50) = CONVERT(VARCHAR, (DATEADD(DAY,1,GETDATE())), 23);			-- This is the end date, specify as 'yyyy-mm-dd'

-- Build a table to hold the result
	CREATE TABLE #EventHistory (
	TABLE_NAME NVARCHAR(128), 
	Name NVARCHAR(555), 
	ByName NVARCHAR(225), 
	ByTime DATETIME, 
	ChangeCue NVARCHAR(20));

-- Get the log values
	-- Start with tables that have a Name column.  Exclude the installlog.
		DECLARE @SQL1 NVARCHAR(MAX);
		DECLARE @TableName NVARCHAR(128);

		DECLARE [TablesWithName] CURSOR FOR
			SELECT DISTINCT(TABLE_NAME) FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME LIKE '%_LOG'AND COLUMN_NAME = 'Name' AND TABLE_NAME <> 'Installlog'

		OPEN [TablesWithName]
		FETCH [TablesWithName] INTO @TableName

		WHILE @@FETCH_STATUS = 0	
			BEGIN	  	

		PRINT @TableName
				SET @SQL1 = 'INSERT INTO #EventHistory SELECT ''' + @TableName + ''', NAME, ByName, ByTime, ChangeCue FROM ' + @TableName + ' WHERE ByTime > ''' + @STARTTIME + ''' AND ByTime < ''' + @ENDTIME + ''';'
				EXEC (@SQL1);

				FETCH [TablesWithName] INTO @TableName
			END

		DEALLOCATE [TablesWithName]

	-- Now tables without a column name
		DECLARE [TablesWithoutName] CURSOR FOR
			SELECT DISTINCT(TABLE_NAME) FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME LIKE '%_LOG'
			AND TABLE_NAME NOT IN 
			(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME LIKE '%_LOG' AND COLUMN_NAME = 'Name')

		OPEN [TablesWithoutName]
		FETCH [TablesWithoutName] INTO @TableName

		WHILE @@FETCH_STATUS = 0	
			BEGIN	  	
		PRINT @TableName
				SET @SQL1 = 'INSERT INTO #EventHistory (TABLE_NAME, ByName, ByTime, ChangeCue) SELECT ''' + @TableName + ''', ByName, ByTime, ChangeCue FROM ' + @TableName + ' WHERE ByTime >= ''' + @STARTTIME + ''' AND ByTime <= ''' + @ENDTIME + ''';'
				EXEC (@SQL1);

				FETCH [TablesWithoutName] INTO @TableName
			END

		DEALLOCATE [TablesWithoutName]
	GO