SQL RNNR

USE AdminDB;
GO
SET NOCOUNT ON;
 
DECLARE @ConfigLastUpdateDate	DATETIME
		,@PreviousPollDate		DATETIME
		,@MaxPollDate			DATETIME
		,@PATH					NVARCHAR(260);
 
SELECT @PATH = REVERSE(SUBSTRING(REVERSE([PATH]), 
						CHARINDEX('\', REVERSE([path])), 260)) + N'LOG.trc'
	FROM sys.traces 
	WHERE is_default = 1;
 
IF OBJECT_ID('tempdb..#DBCCConfig') IS NOT NULL DROP TABLE #DBCCConfig;
	CREATE TABLE #DBCCConfig(
		[ParentObject] VARCHAR (100),
		[Object]       VARCHAR (100),
		[Field]        VARCHAR (100),
		[Value]        VARCHAR (100)); 
 
INSERT INTO #DBCCConfig (ParentObject, Object, Field, Value)
	EXECUTE ('DBCC CONFIG WITH TABLERESULTS');
 
WITH cte AS
(
SELECT cfgupddate = MAX(CASE WHEN t1.Field = 'cfgupddate' THEN t1.Value ELSE NULL END),
        cfgupdtime = MAX(CASE WHEN t1.Field = 'cfgupdtime' THEN t1.Value ELSE NULL END)
FROM #DBCCConfig t1
WHERE   Field IN ('cfgupddate', 'cfgupdtime')
)
SELECT  @ConfigLastUpdateDate = CONVERT(DATETIME,t3.configure_upd_dt)
FROM    cte t1
        CROSS APPLY (SELECT cfgupddate = DATEADD(DAY, CONVERT(INT, t1.cfgupddate), '1900-01-01')) t2
        CROSS APPLY (SELECT configure_upd_dt = DATEADD(ms, CONVERT(INT, t1.cfgupdtime)*3.3, t2.cfgupddate)) t3;
 
IF NOT EXISTS (SELECT Name 
					FROM sys.objects 
					WHERE name = 'SysConfigAudit')
	CREATE TABLE SysConfigAudit (
		configuration_id	int
		,name	NVARCHAR(256)
		,value	sql_variant
		,minimum	sql_variant
		,maximum	sql_variant
		,value_in_use	sql_variant
		,description	NVARCHAR(MAX)
		,is_dynamic	bit
		,is_advanced	BIT
		,PollDate		DATE
		,LastConfigUpdtDate	DATETIME)
 
IF NOT EXISTS (SELECT Name 
					FROM sys.objects 
					WHERE name = 'SysConfigChangeLog')
	CREATE TABLE SysConfigChangeLog (
		configuration_id	int
		,name	NVARCHAR(256)
		,CurrValue	SQL_VARIANT
		,PrevValue	SQL_VARIANT
		,description	NVARCHAR(MAX)
		,PollDate		DATE
		,LastConfigUpdtDate	DATETIME
		,PrevConfigUpdtDate	DATETIME
		,ChangeDate	DATETIME
		,ChangeBy	NVARCHAR(256)
		,HostName	NVARCHAR(256)
		,ChangeSPID	SQL_VARIANT
		,Changedata	NVARCHAR(2000)
		,ApplicationName NVARCHAR(256)
		,Severity INT
		,ERROR SQL_VARIANT
		,ChangeBySessionLogin	NVARCHAR(256))
IF NOT EXISTS (SELECT Name 
					FROM sys.key_constraints 
					WHERE name ='PK_SysConfigChangeLog' 
						AND OBJECT_NAME(parent_object_id) = 'SysConfigChangeLog')
BEGIN
ALTER TABLE dbo.SysConfigChangeLog ADD CONSTRAINT
	PK_SysConfigChangeLog PRIMARY KEY CLUSTERED 
	(
	configuration_id,
	ChangeDate DESC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
		ON Admin_Data
 
END
 
SET @MaxPollDate = ISNULL((SELECT TOP 1 PollDate
								FROM dbo.SysConfigAudit 
								ORDER BY PollDate DESC),'1/1/1900')
 
/* Write the configurations out to audit table 
*/
IF CONVERT(DATE,GETDATE()) <> @MaxPollDate
BEGIN
	INSERT INTO SysConfigAudit
			( configuration_id ,
			  name ,
			  value ,
			  minimum ,
			  maximum ,
			  value_in_use ,
			  description ,
			  is_dynamic ,
			  is_advanced,
			  PollDate,
			  LastConfigUpdtDate
			)
	SELECT configuration_id,name
			,value
			,minimum,maximum
			,value_in_use
			,description
			,is_dynamic,is_advanced
			,GETDATE(),@ConfigLastUpdateDate
		FROM master.sys.configurations;
END
 
/* Recast MaxPollDate */
SET @MaxPollDate = ISNULL((SELECT TOP 1 PollDate
								FROM dbo.SysConfigAudit 
								ORDER BY PollDate DESC),'1/1/1900')
 
SET @PreviousPollDate = ISNULL((SELECT TOP 1 PollDate 
									FROM dbo.SysConfigAudit 
									WHERE Polldate <> @MaxPollDate 
									ORDER BY PollDate DESC),'1/1/1900');
 
/*	A configuration has changed and a reboot has occurred 
	causing the updtdate to be written to the config block of the page 10
	The configuration may not be written to the page, but we will write it to
	the table anyway and then compare to the default trace file in all cases.
	If there are any values changed, then write those to the change log with
	the login of the person who changed the value
*/
 
BEGIN
 
WITH presel AS (
SELECT df.LoginName,df.TextData,df.StartTime,df.HostName,df.Severity,df.DatabaseName,df.SPID,df.ERROR
		,df.SessionLoginName,df.ApplicationName
		,SUBSTRING(df.TextData
					,CHARINDEX('''',df.TextData)+1
					,CHARINDEX('''',df.TextData
								,CHARINDEX('''',df.TextData)+1)-CHARINDEX('''',df.TextData)-1)
				AS ConfigName
		,ROW_NUMBER() OVER (PARTITION BY SUBSTRING(df.TextData
													,CHARINDEX('''',df.TextData)+1
													,CHARINDEX('''',df.TextData
															,CHARINDEX('''',df.TextData)+1)-CHARINDEX('''',df.TextData)-1) 
								ORDER BY df.StartTime DESC) AS RowNum
	FROM ::fn_trace_gettable( @path, DEFAULT )  df
	WHERE 1=1
		AND df.TextData LIKE '%Configuration option%'
		AND df.ApplicationName NOT IN ('SQLAgent - Initial Boot Probe','SQLAgent - Enabling/disabling Agent XPs')
		AND df.Severity IS NOT NULL
)
 
INSERT INTO SysConfigChangeLog (configuration_id,name,CurrValue,PrevValue,description,PollDate	
							,LastConfigUpdtDate	,PrevConfigUpdtDate	,ChangeDate,ChangeBy
							,HostName,ChangeSPID,Changedata,ApplicationName,Severity
							,ERROR,ChangeBySessionLogin)
SELECT CUR.configuration_id,cur.NAME,cur.VALUE AS CurrValue,Prev.VALUE AS PrevValue,cur.description
		,cur.polldate, cur.LastConfigUpdtDate,prev.LastConfigUpdtDate AS PrevConfigUpdtDate
		,df.StartTime AS ChangeDate,df.LoginName AS ChangeBy,df.HostName,df.SPID AS ChangeSPID
		,df.TextData AS ChangeData,df.ApplicationName,df.Severity,df.ERROR
		,df.SessionLoginName AS ChangeBySessionLogin
	FROM AdminDB.dbo.SysConfigAudit CUR
		INNER JOIN AdminDB.dbo.SysConfigAudit Prev
			ON CUR.configuration_id = Prev.configuration_id
			AND CUR.PollDate = @MaxPollDate
			AND Prev.PollDate = @PreviousPollDate
		CROSS APPLY presel  df
	WHERE df.RowNum = 1
		AND df.ConfigName = CUR.NAME
		AND CUR.VALUE <> Prev.VALUE	
		AND NOT EXISTS (SELECT configuration_id
							FROM SysConfigChangeLog
							WHERE Name = Cur.NAME
								AND CurrValue = CUR.VALUE
								AND PrevValue = Prev.VALUE
								AND ChangeDate = df.StartTime
								AND ChangeBy = df.LoginName)
		;
END
 
SELECT *
	FROM dbo.SysConfigChangeLog
	ORDER BY ChangeDate DESC;