1 Topic by bijuedathodi

  • bijuedathodi
  • Member
  • Offline
  • Registered: 2012-09-05
  • Posts: 71

Topic: Sent/Receive/Quarantine logs

Dear ZHB

am using iredmail pro (openldap) on centos
By default iredmail keeps above log details only for 7 days. Suppose If am planning to keep this log details for 6 months whether there will be any chance for  perfomance issue or mail service failure.
Because our clients some times asks log details of past six months.

Also please suggest if i want to only backup these sent/receive/quarantine logs on other system before iredmail automatically deletes ,what should i do?

Are these logs placed on mysql side?

Regards
Biju

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,787

Re: Sent/Receive/Quarantine logs

bijuedathodi wrote:

Suppose If am planning to keep this log details for 6 months whether there will be any chance for  perfomance issue or mail service failure.

If you keep too many records in `amavisd` database (especially tables: `msgs`, 'msgrcpt`), login to iRedAdmin-Pro will be slow. I suggest you dump old records into a separate SQL database (do this every day), then remove old records in `amavisd` database, this way you keep the data and won't cause performance issue.

bijuedathodi wrote:

Because our clients some times asks log details of past six months.

Note: the data in Amavisd is just basic info of in/out emails like sender, recipient, subject, date, etc, no mail body stored.

bijuedathodi wrote:

Are these logs placed on mysql side?

Yes.

3 Reply by bijuedathodi

  • bijuedathodi
  • Member
  • Offline
  • Registered: 2012-09-05
  • Posts: 71

Re: Sent/Receive/Quarantine logs

Can u please tell me other than amavisd database what databases need to be backed up for collecting log details like sent,receive & quarantine.

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,787

Re: Sent/Receive/Quarantine logs

bijuedathodi wrote:

collecting log details like sent,receive & quarantine.

What do you mean "log details"? Do you want to backup mail body?

Currently, only Amavisd stores basic info of in/out emails (again, no mail body).

5 Reply by bijuedathodi (edited by bijuedathodi 2015-05-31 21:13:24)

  • bijuedathodi
  • Member
  • Offline
  • Registered: 2012-09-05
  • Posts: 71

Re: Sent/Receive/Quarantine logs

Dear ZHB

if a user ask me to send full  send/receive reports of a particular user,no data (say abc@domain.com)  on specific time frame ( say last one month). (includes time stamp, sender name , receiver name,mail subject and status)
In My Iredmail setup,I can only provide last 7 days mail statistics ,since 7days is the default settings in my setup. Keeping this value  high (60days)will create perfomance issues , so i don't want to increase this value. So i want to now  whether i can achieve this using any other way.
I tried to  export & import amavisd database on a seperate mysql server , but most of the informations are encrypted i find it very hard to retrieve info from database using  web page.

So i thought of exporting values directly from maillog to a seperate mysql database with info
( time stamp, sender name , receiver name,mail subject and status) without changing any iredmail settings,
when i manually checked postfix log (maillog). 'Subject' line is missing  in maillog.
Can i enable subject value on postfix log?
If i enable this , whether it will create any problems with iredmail mail log statistics

6 Reply by mir

  • mir
  • Member
  • Offline
  • Registered: 2014-05-30
  • Posts: 170

Re: Sent/Receive/Quarantine logs

Something like this should get you the required information:
select * from msgrcpt left join msgs on msgs.mail_id = msgrcpt.mail_id left join maddr on msgrcpt.rid = maddr.id;

7 Reply by bijuedathodi (edited by bijuedathodi 2015-05-31 21:26:52)

  • bijuedathodi
  • Member
  • Offline
  • Registered: 2012-09-05
  • Posts: 71

Re: Sent/Receive/Quarantine logs

Mir, thanks very much, working

Executed you query and exported result in to a html file. There is a column 'smtp_resp', in this column i can only see mail status like  'discarded and queued'. Please see below

250 2.0.0 from MTA([127.0.0.1]:10025): 250 2.0.0 Ok: queued as BAC4CCC0075

250 2.7.0 Ok, discarded, id=26159-20 - SPAM

'status=sent' is not seen in this column. Any idea?

if it is possible to extract only these field values (Message ID, time stamp, sender name , receiver name,mail subject and status), Column values are given below
Sorry , i dont have that much knowledge in constructing a mysql query

partition_tag    mail_id    rseqnum    rid    is_local    content    ds    rs    bl    wl    bspam_level    smtp_resp    partition_tag    mail_id    secret_id    am_id    time_num    time_iso    sid    policy    client_addr    size    originating    content    quar_type    quar_loc    dsn_sent    spam_level    message_id    from_addr    subject    host    partition_tag    id    email    domain

8 Reply by bijuedathodi

  • bijuedathodi
  • Member
  • Offline
  • Registered: 2012-09-05
  • Posts: 71

Re: Sent/Receive/Quarantine logs

bijuedathodi wrote:

Mir, thanks very much, working

Executed you query and exported result in to a html file. There is a column 'smtp_resp', in this column i can only see mail status like  'discarded and queued'. Please see below

250 2.0.0 from MTA([127.0.0.1]:10025): 250 2.0.0 Ok: queued as BAC4CCC0075

250 2.7.0 Ok, discarded, id=26159-20 - SPAM

'status=sent' is not seen in this column. Any idea?

if it is possible to extract only these field values (Message ID, time stamp, sender name , receiver name,mail subject and status), based on a paticular time period (like 12-02-2015 to 12-05-2015) Column values are given below
Sorry , i dont have that much knowledge in constructing a mysql query

partition_tag    mail_id    rseqnum    rid    is_local    content    ds    rs    bl    wl    bspam_level    smtp_resp    partition_tag    mail_id    secret_id    am_id    time_num    time_iso    sid    policy    client_addr    size    originating    content    quar_type    quar_loc    dsn_sent    spam_level    message_id    from_addr    subject    host    partition_tag    id    email    domain

9 Reply by mir

  • mir
  • Member
  • Offline
  • Registered: 2014-05-30
  • Posts: 170

Re: Sent/Receive/Quarantine logs

bijuedathodi wrote:

'status=sent' is not seen in this column. Any idea

To get that information you need to search your mail.log.

grep BAC4CCC0075 /var/log/mail.log |grep status

10 Reply by bijuedathodi

  • bijuedathodi
  • Member
  • Offline
  • Registered: 2012-09-05
  • Posts: 71

Re: Sent/Receive/Quarantine logs

yes, will get the status from maillog using grep. But if i want to know the status of a mail that was sent or received by our mail system a few months back ( say past 3 months) , what should i do?. I won't be able to look up in maillog for each and every mail (i think by default iRedmail won't keep old maillogs).
I want to know whether i could set up a seperate mailtracking database either using  maillog or using existing amvisd database to keep such info, again no mail data required