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 <[email protected]> On Behalf Of Benedict White Sent: Monday, June 14, 2021 6:48 AM To: Dino Edwards <[email protected]>; [email protected] 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:[email protected]] Sent: 14 June 2021 10:36 To: Benedict White; [email protected] 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 <[email protected]> On Behalf Of Benedict White Sent: Sunday, June 13, 2021 7:26 PM To: [email protected] 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.
