主页 / dba / 问题 / 189126
Accepted
Vorster
Asked: 2017-10-24 09:38:32 +0800 CST

比较两个不同表中的行，如果有差异，请发送邮件显示差异

  • 772

我想更改我的查询，以便它只显示已更改的值。我正在比较当前表和历史表之间的值。查询显示所有值，我必须追踪邮件以找到差异。我如何编写查询以仅显示具有更改值的行？

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

在此处输入图像描述

sql-server t-sql

1 个回答

  1. Best Answer

    比较旧行和新行时，这将得到任何不同或添加的行：

    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
         )
      )
    • 1

相关问题