Automation Policy sending blank email to administrator

Products

• IT Management Suite
• Client Management Suite

Issue/Introduction

An Automation Policy is sending a blank email to the administrator every morning at 9 AM.  The email did not contain a subject line or any helpful information.

Environment

ITMS 7.x, 8.x

Cause

Remote Access Purge Settings automation policy was set up to send emails at 9 a.m. The policy is set to fire only on the condition of "Run for non-empty data". But it seems to fire even if nothing was purged.

Explanation of why it fires:

 

If you click on the Edit query link in the  Data Source section and then select the Resolved Query tab it will generate the SQL that is being submitted to the database server. If you copy and paste the resolved query results and run them against your database you will probably see (usually)

Total Records    Left Records   Deleted Records
 0                        0                        0

This is why the policy is firing and sending an email. The query returns one row. Albeit it isn't a row that we would want it to fire on.

Resolution

Although this policy did not seem to be enabled, there is a 'Repeat' option that is checked.  By unchecking the repeat option and saving the changes, we did get the blank emails to stop.

Another way of addressing the e-mails being sent out with 'no data' is to edit the query and use one of the included options for 'selecting' when you want the query to return data (even empty data);

declare @TotalRecords int, @LeftRecords int, @DeletedRecords int
      
set @TotalRecords   = (select count(*) from PPA_AMT_MPSNotification)
set @DeletedRecords = (select count(*) from PPA_AMT_MPSNotification where datediff(hour,CreatedDate,getdate()) > 24)

delete from PPA_AMT_MPSNotification where datediff(hour,CreatedDate,getdate()) > 24
set @LeftRecords = (select count(*) from PPA_AMT_MPSNotification)

DECLARE @NTotRecords nvarchar(64)
       ,@NLftRecords nvarchar(64)
       ,@NDelRecords nvarchar(64)
       ,@sQuery     nvarchar(1024)

SELECT @NTotRecords = ISNULL( dbo.fnLocalizeString( 'Total Records', '64A23C09-DEBA-4A1E-BEB5-BEDCB3C5E361', 'En-AU' ), 'Total Records' ),
       @NLftRecords = ISNULL( dbo.fnLocalizeString( 'Left Records', '64A23C09-DEBA-4A1E-BEB5-BEDCB3C5E361', 'En-AU' ), 'Left Records' ),
       @NDelRecords = ISNULL( dbo.fnLocalizeString( 'Deleted Records', '64A23C09-DEBA-4A1E-BEB5-BEDCB3C5E361', 'En-AU' ), 'Deleted Records' )

SELECT @sQuery =
'SELECT ' + CAST(@TotalRecords AS nvarchar) + ' AS [' + @NTotRecords + '], '
          + CAST(@LeftRecords AS nvarchar) + ' AS [' + @NLftRecords + '], '
          + CAST(@DeletedRecords AS nvarchar) + ' AS [' + @NDelRecords + '] '

-- Return results only when all variables are greater than zero
--if(0 not in (@TotalRecords, @DeletedRecords, @LeftRecords)) begin
-- EXEC (@sQuery)
--end
-- Return results when one variable is greater than zero
if((@TotalRecords >0 ) or (@DeletedRecords> 0) or (@LeftRecords > 0)) begin
 EXEC (@sQuery)
end
-- Return results only when records are deleted
--if(@DeletedRecords> 0) begin
-- EXEC (@sQuery)
--end
-- Run query regardless (causes automation e-mail even with empty/all zero data)
--EXEC (@sQuery)