0

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 protected]',
   @body_format =  'HTML',
   @body= @HTML,
   @subject = 'Server Level Security Audit Difference - Added or Changed',
   @profile_name = 'DBA'

  END

  --SendMail

enter image description here

Improve this question

1 Answer 1

Reset to default
1

When comparing an OLD and NEW row this will get any rows that are different OR added:

SELECT NEW.ServerName, NEW.[name], NEW.Type_Desc
FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current NEW
   LEFT JOIN SQLMonitor.dbo.tbl_ServerLevelSecurity_Current_HIST OLD 
      ON OLD.ServerName = NEW.ServerName AND OLD.CollectDate = @Date
WHERE
      (
         OLD.ServerName IS NULL OR
         (
         NEW.Public <> OLD.Public OR
         NEW.SysAdmin <> OLD.SysAdmin OR
         NEW.SecurityAdmin <> OLD.SecurityAdmin OR
         NEW.ServerAdmin <> OLD.ServerAdmin OR
         NEW.SetupAdmin <> OLD.SetupAdmin OR
         NEW.ProcessAdmin <> OLD.ProcessAdmin OR
         NEW.DiskAdmin <> OLD.DiskAdmin OR
         NEW.DBCreator <> OLD.DBCreator OR
         NEW.BulkAdmin <> OLD.BulkAdmin OR
         NEW.ControlServer <> OLD.ControlServer
         )
      )
Improve this answer
4
  • 1
    This may not work when some column values are null.
    – jyao
    Oct 23, 2017 at 18:34
  • I applied the suggested code but i still end up with the unwanted rows, any other suggestions?
    – Vorster
    Oct 23, 2017 at 18:46
  • 1
    If the above where statement is in then the rows have a difference. Unless you don't want NEW rows that don't have an OLD row?
    – indiri
    Oct 23, 2017 at 19:45
  • 1
    I found a mistake i made i didn't add "WHERE OLD.CollectDate = @Date and" once i added this in if has resolved my problem
    – Vorster
    Oct 24, 2017 at 3:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.