Display number of emails pending in each account?
-
- New user
- Posts: 1
- Joined: 2020-10-05 13:48
Display number of emails pending in each account?
Hello,
I just installed hMailServer and got it working. Is there any way to display the number of emails available for reading in each account? I was previously using CMailServer, which displayed a GUI table with an entry for each account that indicated (among other things) the account name, the user name, and the number of emails available for reading. That table was handy for getting a general idea of what was going on.
Thanks!
I just installed hMailServer and got it working. Is there any way to display the number of emails available for reading in each account? I was previously using CMailServer, which displayed a GUI table with an entry for each account that indicated (among other things) the account name, the user name, and the number of emails available for reading. That table was handy for getting a general idea of what was going on.
Thanks!
Re: Display number of emails pending in each account?
it is not presented in the gui.BenevolentDeity wrote: ↑2020-10-05 14:18Hello,
I just installed hMailServer and got it working. Is there any way to display the number of emails available for reading in each account? I was previously using CMailServer, which displayed a GUI table with an entry for each account that indicated (among other things) the account name, the user name, and the number of emails available for reading. That table was handy for getting a general idea of what was going on.
Thanks!
you can just go into your hmailserver Data folder where all domains reside, and via file explorer right click on a user choosing "properties" and read how many files a user has in their dir..
shouldnt be to hard to bat script something to put up as a report for a script knowledgeable person, but if you want it live to see if someones inbox is filling up in realtime someone have to do a good hack i guess
___________________________________________________________end of the line
Re: Display number of emails pending in each account?
Do you mean unread mail?BenevolentDeity wrote: ↑2020-10-05 14:18Is there any way to display the number of emails available for reading in each account?
- jimimaseye
- Moderator
- Posts: 8903
- Joined: 2011-09-08 17:48
Re: Display number of emails pending in each account?
There are these that you might find useful starters:
https://www.hmailserver.com/forum/viewt ... 20&t=28052
https://www.hmailserver.com/forum/viewt ... 20&t=35156
A bit of tailoring to look at message flags and you can get filter it to how you like.
[Entered by mobile. Excuse my spelling.]
https://www.hmailserver.com/forum/viewt ... 20&t=28052
https://www.hmailserver.com/forum/viewt ... 20&t=35156
A bit of tailoring to look at message flags and you can get filter it to how you like.
[Entered by mobile. Excuse my spelling.]
5.7 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829
Re: Display number of emails pending in each account?
I think perhaps the OP is talking about POP3 accounts, not IMAP
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation
https://www.hmailserver.com/documentation
Re: Display number of emails pending in each account?
This works. It takes a long time to loop through every message. I think on any but the smallest servers, it will run into execution timeouts. But it does work
Is there a way to count unread messages instead of looping through them?
Counts unread messages for every active user.
PHP:
Probably better to do this by some other script and just have the results emailed. I did it in PHP because I thought it would be refresh, bang, results.
Edit - many thanks to Jimi's email cleardown script for help with the COM stuff.
Is there a way to count unread messages instead of looping through them?
Counts unread messages for every active user.
PHP:
Code: Select all
<?php
$hMSAdminPass = "supersecretpassword";
$hMS = new COM("hMailServer.Application");
$hMS->Authenticate("Administrator", $hMSAdminPass);
echo "Unread Messages <br><br>";
echo "<table>";
for ($i = 0; $i < ($hMS->Domains->Count); $i++) {
$hMSDomain = $hMS->Domains->Item($i);
if ($hMSDomain->Active){
for ($j = 0; $j < ($hMSDomain->Accounts->Count); $j++) {
$hMSAccount = $hMSDomain->Accounts->Item($j);
if ($hMSAccount->Active) {
echo "<tr><td>".$hMSAccount->Address."</td>";
$l = 0;
for ($m = 0; $m < ($hMSAccount->IMAPFolders->ItemByName('INBOX')->Messages->Count); $m++) {
$hMSMessage = $hMSAccount->IMAPFolders->ItemByName('INBOX')->Messages->Item($m);
If ($hMSMessage->Flag(1)) {$l++;}
}
echo "<td>".($hMSAccount->IMAPFolders->ItemByName('INBOX')->Messages->Count - $l)."</td></tr>";
}
}
}
}
echo "</table>";
?>
Edit - many thanks to Jimi's email cleardown script for help with the COM stuff.
Re: Display number of emails pending in each account?
.
i think the cmailserver interface spoken of is this ( i might be wrong )
i think the cmailserver interface spoken of is this ( i might be wrong )
___________________________________________________________end of the line
Re: Display number of emails pending in each account?
Just toying around now. Here's one to display message count for every folder.
I assume this would work for POP just as easily, although IMAP must be enabled (I think) in order to get the counts. I don't see POP anywhere in the COM documentation.
This counts the messages in each folder for each account. If the userbase were using POP exclusively, then only the INBOX would show up. The output is like below:
The first two are accounts for automated messages. The second one only has an INBOX because no one has ever logged in. Its just for sending. I assume this is how a POP only account would appear.
The first account is also an automated sending account, but one time I logged in via webmail and the webmail client created the folders automatically. Otherwise, there would only be an INBOX.
The third account is an actual user with user-created folders.
This one is very fast because it doesn't loop through each message.
PHP:
I like PHP because you just pull it up on a browser for real time information.
I assume this would work for POP just as easily, although IMAP must be enabled (I think) in order to get the counts. I don't see POP anywhere in the COM documentation.
This counts the messages in each folder for each account. If the userbase were using POP exclusively, then only the INBOX would show up. The output is like below:
Code: Select all
battery@mydomain.tld INBOX 0
Sent 0
Drafts 0
Spam 0
Trash 0
Archive 0
TOTAL MESSAGES: 0
brother-mfc@mydomain.tld INBOX 0
TOTAL MESSAGES: 0
actualuser@mydomain.tld INBOX 2450
Trash 3
Sent 855
Drafts 0
Archive 0
Spam 1
Bank 41
Dingbat 41
Junk E-mail 3
TOTAL MESSAGES: 3394
The first account is also an automated sending account, but one time I logged in via webmail and the webmail client created the folders automatically. Otherwise, there would only be an INBOX.
The third account is an actual user with user-created folders.
This one is very fast because it doesn't loop through each message.
PHP:
Code: Select all
<?php
$hMSAdminPass = "supersecretpassword";
$hMS = new COM("hMailServer.Application");
$hMS->Authenticate("Administrator", $hMSAdminPass);
echo "All Messages <br><br>";
echo "<table>";
for ($i = 0; $i < ($hMS->Domains->Count); $i++) {
$hMSDomain = $hMS->Domains->Item($i);
if ($hMSDomain->Active){
for ($j = 0; $j < ($hMSDomain->Accounts->Count); $j++) {
$hMSAccount = $hMSDomain->Accounts->Item($j);
if ($hMSAccount->Active) {
$totalCount = 0;
for ($k = 0; $k < ($hMSAccount->IMAPFolders->Count); $k++) {
$hMSIMAPFolder = $hMSAccount->IMAPFolders->Item($k);
$totalCount += $hMSIMAPFolder->Messages->Count;
if ($hMSIMAPFolder->Name == "INBOX"){
echo "<tr><td>".$hMSAccount->Address."</td><td>".$hMSIMAPFolder->Name."</td><td style='text-align:right;'>".number_format($hMSIMAPFolder->Messages->Count)."</td></tr>";
} else {
echo "<tr><td></td><td>".$hMSIMAPFolder->Name."</td><td style='text-align:right;'>".number_format($hMSIMAPFolder->Messages->Count)."</td></tr>";
}
}
echo "<tr style='font-weight:bold;'><td></td><td>TOTAL MESSAGES:</td><td style='text-align:right;'>".number_format($totalCount)."</td></tr>";
echo "<tr><td colspan='3'><hr></td></td></tr>";
}
}
}
}
echo "</table>";
?>
I like PHP because you just pull it up on a browser for real time information.
Re: Display number of emails pending in each account?
Code: Select all
SELECT COUNT(hm_messages.messageid)
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE hm_accounts.accountaddress = 'wile.e.coyote@acme.inc' AND RIGHT(BIN(hm_messages.messageflags),1) = '0';

SørenR.
Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.
Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.
Re: Display number of emails pending in each account?
Beeyooteefool! I'll push out an update tonight.SorenR wrote: ↑2020-10-06 21:42You are welccome...Code: Select all
SELECT COUNT(hm_messages.messageid) FROM hm_messages INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid WHERE hm_accounts.accountaddress = 'wile.e.coyote@acme.inc' AND RIGHT(BIN(hm_messages.messageflags),1) = '0';
![]()
Re: Display number of emails pending in each account?
I'm tired in my head and soon off to bed so I won't add any more to this but (!) you should be able to create a query that will list all accounts in a domain and their number of unread messages in one go ...palinka wrote: ↑2020-10-06 21:58Beeyooteefool! I'll push out an update tonight.SorenR wrote: ↑2020-10-06 21:42You are welccome...Code: Select all
SELECT COUNT(hm_messages.messageid) FROM hm_messages INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid WHERE hm_accounts.accountaddress = 'wile.e.coyote@acme.inc' AND RIGHT(BIN(hm_messages.messageflags),1) = '0';
![]()
SørenR.
Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.
Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.
Re: Display number of emails pending in each account?
That's probably true, but it was way easier to recycle! I recycle everything.SorenR wrote: ↑2020-10-06 22:02I'm tired in my head and soon off to bed so I won't add any more to this but (!) you should be able to create a query that will list all accounts in a domain and their number of unread messages in one go ...palinka wrote: ↑2020-10-06 21:58Beeyooteefool! I'll push out an update tonight.SorenR wrote: ↑2020-10-06 21:42You are welccome...Code: Select all
SELECT COUNT(hm_messages.messageid) FROM hm_messages INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid WHERE hm_accounts.accountaddress = 'wile.e.coyote@acme.inc' AND RIGHT(BIN(hm_messages.messageflags),1) = '0';
![]()

Also, the COM script cycles through all accounts on all domains, so you get a result for every ACTIVE account in one simple page.
Muuuuuuuccchhhhhhh FASTER than before... Thank ye kindly.
PHP:
Code: Select all
<?php
/* Database Variables
'dbtype' = database server type
For MySQL use dbtype = 'mysql'
For MSSQL use dbtype = 'mssql'
'driver' = connection type
For MySQL use driver = 'mysql'
For MSSQL use driver = 'mssql'
For ODBC use driver = 'odbc'
* When opting for ODBC use correct DSN! *
* Example: "MariaDB ODBC 3.0 Driver" *
* Exact spelling is critical! *
*/
$Database = array (
'dbtype' => 'mysql',
'host' => 'localhost',
'username' => 'hmailserver',
'password' => 'supersecretpassword',
'dbname' => 'hmailserver',
'driver' => 'mysql',
'port' => '3306',
'dsn' => 'MariaDB ODBC 3.0 Driver'
);
/* hMailServer COM Authentication
Password for hMailServer "Administrator"
*/
$hMSAdminPass = "supersecretpassword";
If ($Database['driver'] == 'mysql') {
$pdo = new PDO("mysql:host=".$Database['host'].";port=".$Database['port'].";dbname=".$Database['dbname'], $Database['username'], $Database['password']);
} ElseIf ($Database['driver'] == 'mssql') {
$pdo = new PDO("sqlsrv:Server=".$Database['host'].",".$Database['port'].";Database=".$Database['dbname'], $Database['username'], $Database['password']);
} ElseIf ($Database['driver'] == 'odbc') {
$pdo = new PDO("odbc:Driver={".$Database['dsn']."};Server=".$Database['host'].";Port=".$Database['port'].";Database=".$Database['dbname'].";User=".$Database['username'].";Password=".$Database['password'].";");
} Else {
echo "Configuration Error - No database driver specified";
}
$hMS = new COM("hMailServer.Application");
$hMS->Authenticate("Administrator", $hMSAdminPass);
echo "Unread Messages <br><br>";
echo "<table>";
for ($i = 0; $i < ($hMS->Domains->Count); $i++) {
$hMSDomain = $hMS->Domains->Item($i);
if ($hMSDomain->Active){
for ($j = 0; $j < ($hMSDomain->Accounts->Count); $j++) {
$hMSAccount = $hMSDomain->Accounts->Item($j);
if ($hMSAccount->Active) {
echo "<tr><td>".$hMSAccount->Address."</td>";
$sql = $pdo->prepare("
SELECT COUNT(hm_messages.messageid) AS unread
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE hm_accounts.accountaddress = '".$hMSAccount->Address."' AND RIGHT(BIN(hm_messages.messageflags),1) = '0';
");
$sql->execute();
$unread = $sql->fetchColumn();
echo "<td>".$unread."</td></tr>";
}
}
}
}
echo "</table>";
?>
Re: Display number of emails pending in each account?
Code: Select all
SELECT hm_accounts.accountaddress, COUNT(*)
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0'
GROUP BY hm_accounts.accountaddress;

BUT ! You only need to run one SQL query so it should be faster

SørenR.
Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.
Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.
Re: Display number of emails pending in each account?
Perfection. Anyway, what's the point of looking at accounts with NO unread messages if your goal is to look at accounts WITH unread messages?SorenR wrote: ↑2020-10-06 23:01OK, it will ONLY list accounts with unread messages...Code: Select all
SELECT hm_accounts.accountaddress, COUNT(*) FROM hm_messages INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0' GROUP BY hm_accounts.accountaddress;
BUT ! You only need to run one SQL query so it should be faster![]()
This greatly simplifies things.
Code: Select all
<?php
/* Database Variables
'dbtype' = database server type
For MySQL use dbtype = 'mysql'
For MSSQL use dbtype = 'mssql'
'driver' = connection type
For MySQL use driver = 'mysql'
For MSSQL use driver = 'mssql'
For ODBC use driver = 'odbc'
* When opting for ODBC use correct DSN! *
* Example: "MariaDB ODBC 3.0 Driver" *
* Exact spelling is critical! *
*/
$Database = array (
'dbtype' => 'mysql',
'host' => 'localhost',
'username' => 'hmailserver',
'password' => 'supersecretpassword',
'dbname' => 'hmailserver',
'driver' => 'mysql',
'port' => '3306',
'dsn' => 'MariaDB ODBC 3.0 Driver'
);
If ($Database['driver'] == 'mysql') {
$pdo = new PDO("mysql:host=".$Database['host'].";port=".$Database['port'].";dbname=".$Database['dbname'], $Database['username'], $Database['password']);
} ElseIf ($Database['driver'] == 'mssql') {
$pdo = new PDO("sqlsrv:Server=".$Database['host'].",".$Database['port'].";Database=".$Database['dbname'], $Database['username'], $Database['password']);
} ElseIf ($Database['driver'] == 'odbc') {
$pdo = new PDO("odbc:Driver={".$Database['dsn']."};Server=".$Database['host'].";Port=".$Database['port'].";Database=".$Database['dbname'].";User=".$Database['username'].";Password=".$Database['password'].";");
} Else {
echo "Configuration Error - No database driver specified";
}
echo "Unread Messages <br><br>";
echo "<table>";
$sql = $pdo->prepare("
SELECT hm_accounts.accountaddress AS address, COUNT(*) AS countunread
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0'
GROUP BY hm_accounts.accountaddress;
");
$sql->execute();
while($row = $sql->fetch(PDO::FETCH_ASSOC)){
echo "<tr><td>".$row['address']."</td><td style='text-align:right;'>".number_format($row['countunread'])."</td></tr>";
}
echo "</table>";
?>
Re: Display number of emails pending in each account?
Found one minor flaw. Your all in one query outputs in alphabetical order by address, instead of by domain, then by address. Its not really a flaw, just a nitpick. Using the COM with your first query, and then using if unread > 0, puts them back into orderly fashion, without the no-unread accounts. 

Re: Display number of emails pending in each account?
palinka wrote: ↑2020-10-06 23:32Found one minor flaw. Your all in one query outputs in alphabetical order by address, instead of by domain, then by address. Its not really a flaw, just a nitpick. Using the COM with your first query, and then using if unread > 0, puts them back into orderly fashion, without the no-unread accounts.![]()
Code: Select all
SELECT hm_accounts.accountaddress,COUNT(*)
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0'
GROUP BY hm_accounts.accountaddress
ORDER BY SUBSTRING_INDEX(hm_accounts.accountaddress, "@", -1)
HMS 5.6.8 B2534.28 on Windows Server 2019 Core VM.
HMS 5.6.8 B2534.28 on Windows Server 2016 Core VM.
HMS 5.6.7 B2425.16 on Windows Server 2012 R2 Core VM.
HMS 5.6.8 B2534.28 on Windows Server 2016 Core VM.
HMS 5.6.7 B2425.16 on Windows Server 2012 R2 Core VM.
Re: Display number of emails pending in each account?
Haha! That's great, man! Crowd sourcing is awesome!tunis wrote: ↑2020-10-07 13:30Code: Select all
SELECT hm_accounts.accountaddress,COUNT(*) FROM hm_messages INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0' GROUP BY hm_accounts.accountaddress ORDER BY SUBSTRING_INDEX(hm_accounts.accountaddress, "@", -1)
Re: Display number of emails pending in each account?
I made one small modification.tunis wrote: ↑2020-10-07 13:30Code: Select all
SELECT hm_accounts.accountaddress,COUNT(*) FROM hm_messages INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0' GROUP BY hm_accounts.accountaddress ORDER BY SUBSTRING_INDEX(hm_accounts.accountaddress, "@", -1)
WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0' AND accountactive = '1'
So only active accounts are displayed. I think we can put a fork in this one!

Final Version: PHP (MySQL only, also enabled mobile viewport):
Code: Select all
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body style="font-family:Verdana;">
<?php
$Database = array (
'host' => 'localhost',
'username' => 'hmailserver',
'password' => 'supersecretpassword',
'dbname' => 'hmailserver',
'port' => '3306'
);
$pdo = new PDO("mysql:host=".$Database['host'].";port=".$Database['port'].";dbname=".$Database['dbname'], $Database['username'], $Database['password']);
echo "Unread Messages <br><br>";
echo "<table>";
$sql = $pdo->prepare("
SELECT hm_accounts.accountaddress AS address, COUNT(*) AS countunread
FROM hm_messages
INNER JOIN hm_accounts ON hm_messages.messageaccountid = hm_accounts.accountid
WHERE RIGHT(BIN(hm_messages.messageflags),1) = '0' AND accountactive = '1'
GROUP BY hm_accounts.accountaddress
ORDER BY SUBSTRING_INDEX(hm_accounts.accountaddress, '@', -1)
");
$sql->execute();
while($row = $sql->fetch(PDO::FETCH_ASSOC)){
echo "<tr><td>".$row['address']."</td><td style='text-align:right;'>".number_format($row['countunread'])."</td></tr>";
}
echo "</table>";
?>
</body>
</html>