1 Topic by svjoseph

  • svjoseph
  • svjoseph
  • Member
  • Offline
  • Registered: 2017-05-05
  • Posts: 28

Topic: Top Senders Top Recipients

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release): 0.9.8
- Linux/BSD distribution name and version: Ubuntu 16.04
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Web server (Apache or Nginx): Nginx
- Manage mail accounts with iRedAdmin-Pro? Yes
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

We recently upgraded to iRedMail 0.9.8 and iRedAdmin-Pro(MySQL) 2.9.0 .  We are not seeing Top Senders / Top Recipients in the iRedAdmin panel .   We followed the suggestions in another forum posting to clear the Quarantine and then truncate some MySQL tables.  Received    81  Sent    57  and still no usage data.

----

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

svjoseph's Website

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: Top Senders Top Recipients

- Make sure you have "@storage_sql_dsn" setting (and correct sql username/password) in Amavisd config file: /etc/amavis/conf.d/50-user.
- Do you have "content_filter" setting in /etc/postfix/main.cf and master.cf?

3 Reply by svjoseph

  • svjoseph
  • svjoseph
  • Member
  • Offline
  • Registered: 2017-05-05
  • Posts: 28

Re: Top Senders Top Recipients

yes the @storage_sql_dsn setting is there and looks like this:

@storage_sql_dsn = (['DBI:mysql:database=amavisd;host=127.0.0.1;port=3306', 'amavisd', '*******************']);


$ mysql -u amavisd -p
Enter Password:
Welcome to the MySQL monitor.  (The password is correct)


main.cf:
content_filter = smtp-amavis:[127.0.0.1]:10024

master.cf:
#Submission, port 587...
submission inet
  -o content_filter=smtp-amavis:[127.0.0.1]:10026

#Amavisd Integration
127.0.0.1:10025 inet
    -o content_filter=

svjoseph's Website

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: Top Senders Top Recipients

Amavisd and postfix settings look fine. Try this: Run SQL commands below as mysql root user:

USE amavisd;
TRUNCATE quarantine;
TRUNCATE msgrcpt;
TRUNCATE msgs;
TRUNCATE maddr;

Then wait for new inbound/outbound email and check iRedAdmin-Pro Dashboard again.

5 Reply by jobu

  • jobu
  • Member
  • Offline
  • Registered: 2011-08-01
  • Posts: 122

Re: Top Senders Top Recipients

ZhangHuangbin wrote:

Amavisd and postfix settings look fine. Try this: Run SQL commands below as mysql root user:

USE amavisd;
TRUNCATE quarantine;
TRUNCATE msgrcpt;
TRUNCATE msgs;
TRUNCATE maddr;

Then wait for new inbound/outbound email and check iRedAdmin-Pro Dashboard again.

I have the same problem (under Debian 9) and followed the instructions to this point with similar output, sadly it didn't help me. Top senders and recipients are still empty.
Do you ave any further ideas for me?

Regards

Jobu

6 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: Top Senders Top Recipients

@jobu,

Please show us output of MySQL commands below:

USE amavisd;
DESC maddr;

7 Reply by jobu

  • jobu
  • Member
  • Offline
  • Registered: 2011-08-01
  • Posts: 122

Re: Top Senders Top Recipients

MariaDB [amavisd]> DESC maddr;
+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| partition_tag | int(11)             | YES  | MUL | 0       |                |
| id            | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| email         | varbinary(255)      | NO   | MUL | NULL    |                |
| domain        | varchar(255)        | NO   | MUL | NULL    |                |
+---------------+---------------------+------+-----+---------+----------------+

8 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: Top Senders Top Recipients

jobu wrote:

MariaDB [amavisd]> DESC maddr;

You didn't upgrade iRedMail to the latest iRedMail-0.9.8? This SQL change is mentioned in upgrade tutorial:
https://docs.iredmail.org/upgrade.iredm … xtension_1

9 Reply by jobu (edited by jobu 2018-10-12 14:33:57)

  • jobu
  • Member
  • Offline
  • Registered: 2011-08-01
  • Posts: 122

Re: Top Senders Top Recipients

Thanks, i obviously missed that ...
After that,  i see now numeric values instead of emailadresses.

Top senders   Top recipients
1925     <>   4371    

Table looks like this now:

MariaDB [amavisd]> DESC maddr;
+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| partition_tag | int(11)             | YES  | MUL | 0       |                |
| id            | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| email         | varbinary(255)      | NO   | MUL | NULL    |                |
| domain        | varchar(255)        | NO   | MUL | NULL    |                |
| email_raw     | varbinary(255)      | NO   | MUL |         |                |
+---------------+---------------------+------+-----+---------+----------------+

Did i crash it? roll

10 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,088

Re: Top Senders Top Recipients

1: login to iRedAdmin-Pro and manage the quarantined emails first, release or delete them.
2: Remove all records in 4 SQL tables:

msgs
msgrcpt
maddr
quarantine

Then it should be fine.

11 Reply by jobu

  • jobu
  • Member
  • Offline
  • Registered: 2011-08-01
  • Posts: 122

Re: Top Senders Top Recipients

After cleaning up quarantaine and removing the records in amavisd-table it works again now.

Thanks a lot!

Jobu