Issue with mails in sql quarantine

[Deeztek Support]

The whole table structure is a bit confusing. 

The msgs table lists all the msg details but without the part you are looking for, the receiver. In order to get the receiver, you have to take the msgs.mail_id and query that against msgrcpt table which will give you the receiver id (rid) and then you take the rid and you query against the recipients table which will give you the recipients. 

The query I send you below does an inner join on msgs and msgrcpt tables. You could do a join on 3 tables (msgs, msrcpt, recipient) table, but my SQL ninja is not that good.

If it's a new server, have you considered using this?

https://github.com/deeztek/Hermes-Secure-Email-Gateway

It has a nice Web GUI that will give you all the amavis management and search capabilities you need.








-----Original Message-----
From: amavis-users <amavis-users-bounces+support=deeztek.com at amavis.org> On Behalf Of Benedict White
Sent: Monday, June 14, 2021 6:48 AM
To: Dino Edwards <dino.edwards at mydirectmail.net>; amavis-users at amavis.org
Subject: RE: Issue with mails in sql quarantine

Many thanks for that, the dates bit doesn't seem to work though it is a new server so that is not important.

Any idea what I need to add to see the address the emails were sent to?

Kind Regards, 

Benedict White

Our business grows by referrals. If you know someone who would benefit from our help, please pass on our details.


Tel: 
01444 238070
Tech: 
01444 238080
Fax: 
01444 238099
Web: 
www.cse-ltd.co.uk
Registered in England and Wales No: 8666450 Unit D, Consort Way, Burgess Hill, West Sussex,
RH15 9TJ

                 





Legal Disclaimer: This message and any attachment may be confidential and privileged. If you are not the intended recipient please notify the sender and delete this e-mail and any attachment from your system. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. All information given in this e-mail is provided in good faith but not binding for legal reasons.


From: Dino Edwards [mailto:dino.edwards at mydirectmail.net]
Sent: 14 June 2021 10:36
To: Benedict White; amavis-users at amavis.org
Subject: RE: Issue with mails in sql quarantine

Try the following query:

SELECT msgrcpt.mail_id, msgrcpt.ds, msgs.sid, msgs.spam_level, msgs.mail_id, msgs.secret_id, msgs.time_iso, msgs.subject, msgs.from_addr, msgs.content, msgs.client_addr FROM msgs INNER JOIN msgrcpt ON msgs.mail_id = msgrcpt.mail_id where msgs.time_iso between '2021-05-01 12:00:00' and '2021-05-01 23:00:00' and msgs.content like binary 'S' order by msgs.time_iso desc

Here are the quarantined msgs.content types:

V = Virus
B = Banned
S = Quarantined Spam
M = Bad-Mime
H = Bad-Header
O = Oversized

Hope this helps.

Many more queries can be found here:

https://github.com/deeztek/Hermes-Secure-Email-Gateway/blob/master/dirstructure/var/www/html/admin/message_history_new.cfm






From: amavis-users <amavis-users-bounces+dino.edwards=mydirectmail.net at amavis.org> On Behalf Of Benedict White
Sent: Sunday, June 13, 2021 7:26 PM
To: amavis-users at amavis.org
Subject: Issue with mails in sql quarantine

I had an issue for two days over a weekend where most emails got quarantined.

Does anyone have an sql statement to list quarantined emails by user please?

Kind Regards, 

Benedict White

Our business grows by referrals. If you know someone who would benefit from our help, please pass on our details.


Tel: 
01444 238070
Tech: 
01444 238080
Fax: 
01444 238099
Web: 
www.cse-ltd.co.uk
Registered in England and Wales No: 8666450 Unit D, Consort Way, Burgess Hill, West Sussex,
RH15 9TJ

                 





Legal Disclaimer: This message and any attachment may be confidential and privileged. If you are not the intended recipient please notify the sender and delete this e-mail and any attachment from your system. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. All information given in this e-mail is provided in good faith but not binding for legal reasons.