I would like to change my query so it only shows values that have changed. I am comparing values between a current and history table. The query as it is shows all values and i have to trail the mail to find a difference. How could I write the query to only show rows with changed values?
DECLARE @Date Datetime = (SELECT TOP 1 CollectDate FROM
SQLMonitor.dbo.tbl_ServerLevelSecurity_Current_HIST ORDER BY CollectDate
DESC)
/*select * FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current NEW
INNER JOIN SQLMonitor.dbo.tbl_ServerLevelSecurity_Current_HIST OLD ON
OLD.ServerName = NEW.ServerName
WHERE OLD.CollectDate = @Date
*/
--Changed or new
IF EXISTS(SELECT * FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current WHERE
ISNULL (ServerName, '_')
+ISNULL (name, '_')
+ISNULL (Type_Desc, '_')
+ISNULL ([Public], '_')
+ISNULL (SysAdmin, '_')
+ISNULL (SecurityAdmin, '_')
+ISNULL (ServerAdmin, '_')
+ISNULL (SetupAdmin, '_')
+ISNULL (ProcessAdmin, '_')
+ISNULL (DiskAdmin, '_')
+ISNULL (DBCreator, '_')
+ISNULL (BulkAdmin, '_')
+ISNULL (ControlServer, '_')
NOT IN (SELECT ISNULL (ServerName, '_')
+ISNULL (name, '_')
+ISNULL (Type_Desc, '_')
+ISNULL ([Public], '_')
+ISNULL (SysAdmin, '_')
+ISNULL (SecurityAdmin, '_')
+ISNULL (ServerAdmin, '_')
+ISNULL (SetupAdmin, '_')
+ISNULL (ProcessAdmin, '_')
+ISNULL (DiskAdmin, '_')
+ISNULL (DBCreator, '_')
+ISNULL (BulkAdmin, '_')
+ISNULL (ControlServer, '_')
FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current_HIST WHERE CollectDate =
@Date) )
BEGIN
DECLARE
@html NVARCHAR(MAX),
@table NVARCHAR(MAX)
SET @html = N'<html><head><title>Server Level Security Audit
Differences</title></head>' + CHAR(10) +
N'<body style="font-family: Arial"
bgcolor="#FFFFCC">' +
N'<h2>Added or Changed</h2>' +
N'<table border="1">' +
N'<tr>' +
N'<th width="120">Server Name</th>' +
N'<th width="120">Name</th>' +
N'<th width="500">Type Desc</th>' +
N'<th width="500">Public</th>' +
N'<th width="500">SysAdmin</th>' +
N'<th width="500">SecurityAdmin</th>' +
N'<th width="500">ServerAdmin</th>' +
N'<th width="500">SetupAdmin</th>' +
N'<th width="500">ProcessAdmin</th>' +
N'<th width="500">DiskAdmin</th>' +
N'<th width="500">DBCreator</th>' +
N'<th width="500">BulkAdmin</th>' +
N'<th width="500">ControlServer</th>' +
N'</tr>' + CHAR(10);
SELECT @table = CONVERT(varchar(max),
(SELECT
td = NEW.ServerName,
'',
td = NEW.Name,
'',
td = NEW.Type_Desc,
'',
td = 'Public was ' + ISNULL(OLD.[Public], 'NULL') + ' and Public is now ' + ISNULL(NEW.[Public], 'NULL'),
'',
td = 'SysAdmin was ' + ISNULL(OLD.SysAdmin, 'NULL') + ' and SysAdmin is now ' + ISNULL(NEW.SysAdmin, 'NULL'),
'',
td = 'SecurityAdmin was ' + ISNULL(OLD.SecurityAdmin, 'NULL') + ' and SecurityAdmin is now ' + ISNULL(NEW.SecurityAdmin, 'NULL'),
'',
td = 'ServerAdmin was ' + ISNULL(OLD.ServerAdmin, 'NULL') + ' and ServerAdmin is now ' + ISNULL(NEW.ServerAdmin, 'NULL'),
'',
td = 'SetupAdmin was ' + ISNULL(OLD.SetupAdmin, 'NULL') + ' and SetupAdmin is now ' + ISNULL(NEW.SetupAdmin, 'NULL'),
'',
td = 'ProcessAdmin was ' + ISNULL(OLD.ProcessAdmin, 'NULL') + ' and ProcessAdmin is now ' + ISNULL(NEW.ProcessAdmin, 'NULL'),
'',
td = 'DiskAdmin was ' + ISNULL(OLD.DiskAdmin, 'NULL') + ' and DiskAdmin is now ' + ISNULL(NEW.DiskAdmin, 'NULL'),
'',
td = 'DBCreator was ' + ISNULL(OLD.DBCreator, 'NULL') + ' and DBCreator is now ' + ISNULL(NEW.DBCreator, 'NULL'),
'',
td = 'BulkAdmin was ' + ISNULL(OLD.BulkAdmin, 'NULL') + ' and BulkAdmin is now ' + ISNULL(NEW.BulkAdmin, 'NULL'),
'',
td = 'ControlServer was ' + ISNULL(OLD.ControlServer, 'NULL') + ' and ControlServer is now ' + ISNULL(NEW.ControlServer, 'NULL')
FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current NEW
LEFT JOIN SQLMonitor.dbo.tbl_ServerLevelSecurity_Current_HIST OLD
ON OLD.ServerName = NEW.ServerName
WHERE OLD.CollectDate = @Date and
ISNULL (new.ServerName, '_') +ISNULL (new.name, '_') +ISNULL (new.Type_Desc, '_') +ISNULL (new.[Public], '_') +ISNULL (new.SysAdmin, '_') +ISNULL (new.SecurityAdmin, '_') +ISNULL (new.ServerAdmin, '_') +ISNULL (new.SetupAdmin, '_') +ISNULL (new.ProcessAdmin, '_') +ISNULL (new.DiskAdmin, '_') +ISNULL (new.DBCreator, '_') +ISNULL (new.BulkAdmin, '_') +ISNULL (new.ControlServer, '_') <> ISNULL (old.ServerName, '_') +ISNULL (old.name, '_') +ISNULL (old.Type_Desc, '_') +ISNULL (old.[Public], '_') +ISNULL (old.SysAdmin, '_') +ISNULL (old.SecurityAdmin, '_') +ISNULL (old.ServerAdmin, '_') +ISNULL (old.SetupAdmin, '_') +ISNULL (old.ProcessAdmin, '_') +ISNULL (old.DiskAdmin, '_') +ISNULL (old.DBCreator, '_') +ISNULL (old.BulkAdmin, '_') +ISNULL (old.ControlServer, '_')
FOR XML PATH(N'tr'), TYPE));
SET @html = @html + @table + CHAR(10) +
N'</table></body></html>';
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'EMAIL@EMAILTEST.com',
@body_format = 'HTML',
@body= @HTML,
@subject = 'Server Level Security Audit Difference - Added or Changed',
@profile_name = 'DBA'
END
--SendMail
Best Answer
When comparing an OLD and NEW row this will get any rows that are different OR added: