Reminder to approve requests - being sent out every minute

[NicOla]

We are having an issue where our “Reminder to approve requests” emails are being sent out in bulk.

In the last hour 3000+ emails have been sent out.

It looks like they are only being sent out to approvers who have pending approval requests. Some approvers are getting 50 to 100 emails each.

It appears that the “Reminder to approve requests” emails have not actually been sent since about August 2019 – we did not notice they were not being sent out – so maybe this is some kind of backlog catch-up?

To stop this until we figure out how to fix the issue we removed the SMTP Relay Server setting in the Site Management – Settings – Email – Outgoing email tab

To purge backed up emails we will go into Site Management > Logs > Email and select them and delete them. Is there a way to stop the ‘Reminder to approve requests’ emails? The ‘Send schedule reminder email’ option is unticked (under Site Management – Settings – Email – Outgoing email tab).

Can anyone think of anything that could help us fix and do a root cause analysis on this?

[CharlesW · Accepted solution]

I've attached the new ESDService.exe to the support case.. Let us know if you see any issues after applying.

[jdempsey]

We have seen this before and there are a couple different factors involved.  I know Support has a hotfix available for the root cause (depending on what version of App Portal you're on), and I have a SQL script to help clean up the after-effects.  Please submit an urgent support case for this.  When I get time (currently booked in calls all day), I'll post back here with more information on the issue.

[CharlesW]

Yes, you should open a support case.. i tried to email the fix to you, but the email was rejected. I also tried to attach the zip file containing the fix to this community post, but it would not let me.

[NicOla]

Hi Charles,

I emailed support last night - but I think the support ticket still needs to be manually created. I just did that now and created case - 02155900. Is there any chance you could attach the fix the that please? 

Thanks again.

[CharlesW · Accepted solution]

I've attached the new ESDService.exe to the support case.. Let us know if you see any issues after applying.

[jdempsey]

Please be aware that even with the hotfix in place, once you resume sending emails, approvers with pending approvals will receive a separate reminder email for every request that is pending their approval.  It will look similar to the repeating approvals behavior, but it will only happen once.  After that, it will sync up all the reminders into a single email for each approver.  To avoid that initial "flood", that's where you would want the SQL query.  Again, I'll post that as soon as I'm able, but I'm not able to go look it up at the moment.

[jdempsey · Accepted solution]

Here is the SQL script I use to locate and clean up the duplicate reminder emails.

--Find Duplicates

SELECT t.ADGUID, COUNT(t.ADGUID) AS [Count], p.UniqueUserName

FROM (SELECT DISTINCT TOP 100 PERCENT ADGUID, LastNotify

      FROM dbo.WD_ApprovalNotify WITH (NOLOCK)

      WHERE Notified = 1 AND (DATEDIFF(minute, LastNotify, getutcdate()) >= 0)

     ) as t

LEFT OUTER JOIN WD_Profile p ON t.ADGUID = p.ADGUID

GROUP BY t.ADGUID, p.UniqueUserName

HAVING COUNT(t.ADGUID) > 1

ORDER BY [Count] DESC



/*--Fix Duplicates

UPDATE old

SET old.LastNotify = new.LastNotify

FROM WD_ApprovalNotify old

JOIN (SELECT ADGUID, MIN(LastNotify) AS [LastNotify]

      FROM WD_ApprovalNotify

      WHERE Notified = 1

      GROUP BY ADGUID) AS new ON old.ADGUID = new.ADGUID

WHERE old.Notified = 1 AND old.LastNotify > new.LastNotify
*/

Please keep in mind that this will only impact approval reminder emails and will not impact emails that are already showing as in the mail queue (i.e. if you look under Site Management > Logs > Email Log, any items that are still pending/retrying will still be sent unless you delete them from the queue).

[NicOla]

Thanks for that Jim,

We have 30, 000+  items that are in a pending or sending status.

We can delete them manually from the Site Management > Logs > Email Log however I think this will take hours. Is there a quicker (safe) way we can get rid of these too?

[jdempsey]

Unfortunately, I don't have a query for that.  It would be based on the records in WD_MailQueue, but I don't know what's "safe" when working with that table.  I provided a couple starter queries to one of our Support engineers working on your case.  Hopefully, that will get you what you need.

[NicOla]

Thank you Jim.

You said this issue has been seen before. I replaced the EDSService.exe but have not re-enabled outgoing emails yet.

We are seeing something strange now. Old requests that were already approved are appearing and having comments added which say 'Auto-Approve'.

I can see also there are emails queued to go out to requesters (as in the initial email a requester will receive when a users requests an application) but the requests are from 2019.

Has that happened before in conjunction with this initial issue?

[jdempsey]

I don't recall seeing that specific behavior, but it's been about 9 months since I encountered that issue, so maybe I just forgot.  I vaguely recall deleting a bunch of older requests, but I don't think that was directly related to the issue at hand.  That was more of a general database cleanup to reduce size and avoid any potential issues associated with older data that wasn't relevant any longer.

[NicOla]

Thanks Jim,

We are having even stranger issues now...

We removed the rows from [WD_MailQueue] as per Flexera Supports instructions...

        DELETE FROM [WD_MailQueue] WHERE ID > 226029

        AND [StatusID] = 0 

        AND [subject] ='Appstore: Approval Reminder...'

We had already copied the new ESDService.exe and we had stopped and started the ESDService wile we copied the new exe over.

The had been working fine today (apart from us not having email enabled).

We added the setting back to the SMTP Relay server to get email going again, then restarted the service again.

Now when any one clicks on any of these tabs below:

They get this error:

Also - just to note - we had not made any recent change to any part of our App Portal. We enabled Flexible email approval over a month ago and that was working fine.

Have you come across anything like that before? We do have a case open too and I uploaded out log files to that.

[jdempsey]

Only thing I can think of is if the hostfixed EXE is based off a different version of App Portal than what you have installed.  It wouldn't make sense for that issue to have anything to do with the email queue cleanup.

[NicOla]

Our version is App Portal 2019 R1 (14.0.0.0).

I replaced the exe earlier today and users could get into every tab and work in 'My Requests', 'Approve/Reject' . I did stop the ESDSErvice before i made the copy and start it up after.

It was only after we re-enable our email that this issues appeared.

Would you know if there a way to check if the version is incorrect and maybe get a new version of the exe?

[jdempsey]

Right-click the new ESDService.exe and select properties.  Go to the Details tab and look at the File Version and Product Version.  I'm not totally sure, but I would guess at least one of those should match your product version.  If one or both of them say 14.0.1.0 (or even 14.0.1.x), then it was likely built for 2019 R1 SP1 and you'd either need to revert the hotfix, upgrade to SP1, and reapply the hotfix, or you'd need to get a new version of the hotfix built on 2019 R1 without SP1.  For any changes to the hotfix, you'd need to work through Support for that.

It's almost 3am here, so I'm signing off for now.  I'll check back here after some rest.

[NicOla]

Hi Jim, thanks for your time .

We managed to fix the last issue with a reboot. Out App Portal is up and running.

Our next step is to figure out the root cause. We can work with the support team on that one.

Cheers,

Nic