![]()
Data Mining the Atmail Log Tables
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.
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(UserSession.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(UserSession.LastLogin) = Users.DateCreate;
List of accounts that have not been used for X days/weeks/months/years
mysql> select Account, LastLogin from UserSession 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;
OK that's it for now...