help centre
For more info visit status.atmail.com

How can we help?


Search our knowledge base for answers to
common questions and latest updates.



My activities New request

Follow

Data Mining the Atmail Log Tables

Stewart -

PROBLEM

I need more detailed logs than the Atmail WebAdmin UI provides.

ENVIRONMENT

  • On-Premise Server + WebMail Installations: Version 6.0 > Current Version
  • Webmail Only Installations: Version 6.0 > Current Version

CAUSE

As handy and powerful as Atmail's webadmin is there is of course some information it does not glean from the DB and display in the pretty interface. Here are a handful of SQL queries that you may find useful for squeezing some extra info out of the Atmail DB.

RESOLUTION

  • Total number of accounts that have never been logged into
    mysql> select count(Users.Account) from Users, UserSession where Users.Account = UserSession.Account and from_unixtime(Users.LastLogin) = Users.DateCreate;
  • List of accounts that have never been used
    mysql> select Users.Account from Users, UserSession where Users.Account = UserSession.Account and from_unixtime(Users.LastLogin) = Users.DateCreate;
  • List of accounts that have not been used for X days/weeks/months/years
    mysql> select Account, LastLogin from Users where from_unixtime(LastLogin) > date_sub(NOW(), interval 1 year);
    Alter the "interval 1 year" part to suit the desired time frame. It should be in the format "interval [num] [unit]" where [num] is the number of [unit]s and [unit] is one of "day, week, month, year" (note that you do not use plurals for the units even if it seems grammatically correct, ie use "interval 2 month" NOT "interval 2 months").
  • Find the Account that receives the most email
    mysql> select Account, count(Account) as emailsReceived from Log_RecvMail group by Account order by emailsReceived desc limit 1;
    Or if you want a full list of emails received per account leave off the "limit 1":
    mysql> select Account, count(Account) as emailsReceived from Log_RecvMail group by Account order by emailsReceived desc;
  • Find the account which sends the most mail
    mysql> select Account, count(Account) as emailsSent from Log_SendMail where Account != "System" group by Account order by emailsSent desc limit 1;
    Again you can leave off the "limit 1" and get a full listing of amount of emails sent per account.
  • Average number of messages sent per day (overall and per user)
    For overall avg:
    mysql> create view v as select count(*) as emailsSent from Log_SendMail group by date(LogDate);
    mysql> select avg(emailsSent) from v;
    For per-user avg:
    mysql> create view v2 as select Account, date(LogDate) as day, count(*) as emailsSent from Log_SendMail group by day, Account;
    mysql> select avg(emailsSent), Account from v2 group by Account;
  • Average number of messages received per day (overall and per user) 
    For overall avg:
    mysql> create view v3 as select count(*) as emailsRec from Log_RecvMail group by date(LogDate);
    mysql> select avg(emailsRec) from v3;
    For per-user avg:
    mysql> create view v4 as select Account, date(LogDate) as day, count(*) as emailsRec from Log_RecvMail group by day, Account;
    mysql> select avg(emailsRec), Account from v4 group by Account;
Have more questions? Submit a request

Comments


Contact our support team


+61 (7) 5357 6605       support@atmail.com