Display number of emails pending in each account?

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
BenevolentDeity
New user
New user
Posts: 1
Joined: 2020-10-05 13:48

Display number of emails pending in each account?

Post by BenevolentDeity » 2020-10-05 14:18

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!

User avatar
johang
Senior user
Senior user
Posts: 349
Joined: 2008-09-01 09:20

Re: Display number of emails pending in each account?

Post by johang » 2020-10-05 15:45

BenevolentDeity wrote:
2020-10-05 14:18
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!
it is not presented in the gui.

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

palinka
Senior user
Senior user
Posts: 2180
Joined: 2017-09-12 17:57

Re: Display number of emails pending in each account?

Post by palinka » 2020-10-05 20:53

BenevolentDeity wrote:
2020-10-05 14:18
Is there any way to display the number of emails available for reading in each account?
Do you mean unread mail?

User avatar
jimimaseye
Moderator
Moderator
Posts: 8777
Joined: 2011-09-08 17:48

Re: Display number of emails pending in each account?

Post by jimimaseye » 2020-10-05 23:11

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.]
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

User avatar
mattg
Moderator
Moderator
Posts: 21106
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Display number of emails pending in each account?

Post by mattg » 2020-10-06 00:28

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

palinka
Senior user
Senior user
Posts: 2180
Joined: 2017-09-12 17:57

Re: Display number of emails pending in each account?

Post by palinka » 2020-10-06 03:29

mattg wrote:
2020-10-06 00:28
I think perhaps the OP is talking about POP3 accounts, not IMAP
I guess IMAP Inbox unread is more or less equivilant to POP not-downloaded messages, correct?

palinka
Senior user
Senior user
Posts: 2180
Joined: 2017-09-12 17:57

Re: Display number of emails pending in each account?

Post by palinka » 2020-10-06 03:54

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:

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>";

?>
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.

User avatar
johang
Senior user
Senior user
Posts: 349
Joined: 2008-09-01 09:20

Re: Display number of emails pending in each account?

Post by johang » 2020-10-06 07:53

.
i think the cmailserver interface spoken of is this ( i might be wrong )
cmail3.gif
___________________________________________________________end of the line

palinka
Senior user
Senior user
Posts: 2180
Joined: 2017-09-12 17:57

Re: Display number of emails pending in each account?

Post by palinka » 2020-10-06 12:04

johang wrote:
2020-10-06 07:53
.
i think the cmailserver interface spoken of is this ( i might be wrong )
c < h

:mrgreen:

What is the flag for unread? 0 or null?

palinka
Senior user
Senior user
Posts: 2180
Joined: 2017-09-12 17:57

Re: Display number of emails pending in each account?

Post by palinka » 2020-10-06 13:03

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:

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 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:

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.

User avatar
SorenR
Senior user
Senior user
Posts: 3829
Joined: 2006-08-21 15:38
Location: Denmark

Re: Display number of emails pending in each account?

Post by SorenR » 2020-10-06 21:42

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';
You are welccome... :mrgreen:
SørenR.

“Those who don't know history are doomed to repeat it.”
― Edmund Burke

palinka
Senior user
Senior user
Posts: 2180
Joined: 2017-09-12 17:57

Re: Display number of emails pending in each account?

Post by palinka » 2020-10-06 21:58

SorenR wrote:
2020-10-06 21:42

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';
You are welccome... :mrgreen:
Beeyooteefool! I'll push out an update tonight.

User avatar
SorenR
Senior user
Senior user
Posts: 3829
Joined: 2006-08-21 15:38
Location: Denmark

Re: Display number of emails pending in each account?

Post by SorenR » 2020-10-06 22:02

palinka wrote:
2020-10-06 21:58
SorenR wrote:
2020-10-06 21:42

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';
You are welccome... :mrgreen:
Beeyooteefool! I'll push out an update tonight.
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 ...
SørenR.

“Those who don't know history are doomed to repeat it.”
― Edmund Burke

palinka
Senior user
Senior user
Posts: 2180
Joined: 2017-09-12 17:57

Re: Display number of emails pending in each account?

Post by palinka » 2020-10-06 22:46

SorenR wrote:
2020-10-06 22:02
palinka wrote:
2020-10-06 21:58
SorenR wrote:
2020-10-06 21:42

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';
You are welccome... :mrgreen:
Beeyooteefool! I'll push out an update tonight.
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 ...
That's probably true, but it was way easier to recycle! I recycle everything. :D

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>";

?>

User avatar
SorenR
Senior user
Senior user
Posts: 3829
Joined: 2006-08-21 15:38
Location: Denmark

Re: Display number of emails pending in each account?

Post by SorenR » 2020-10-06 23:01

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;
OK, it will ONLY list accounts with unread messages... :roll:

BUT ! You only need to run one SQL query so it should be faster :mrgreen:
SørenR.

“Those who don't know history are doomed to repeat it.”
― Edmund Burke

palinka
Senior user
Senior user
Posts: 2180
Joined: 2017-09-12 17:57

Re: Display number of emails pending in each account?

Post by palinka » 2020-10-06 23:22

SorenR wrote:
2020-10-06 23:01

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;
OK, it will ONLY list accounts with unread messages... :roll:

BUT ! You only need to run one SQL query so it should be faster :mrgreen:
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?

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>";

?>

palinka
Senior user
Senior user
Posts: 2180
Joined: 2017-09-12 17:57

Re: Display number of emails pending in each account?

Post by palinka » 2020-10-06 23:32

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. :mrgreen:

tunis
Senior user
Senior user
Posts: 256
Joined: 2015-01-05 20:22
Location: Sweden

Re: Display number of emails pending in each account?

Post by tunis » 2020-10-07 13:30

palinka wrote:
2020-10-06 23:32
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. :mrgreen:

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 B2494.25 on Windows Server 2019 Core VM.
HMS 5.6.8 B2505.27 on Windows Server 2016 Core VM.
HMS 5.6.7 B2425.16 on Windows Server 2012 R2 Core VM.

palinka
Senior user
Senior user
Posts: 2180
Joined: 2017-09-12 17:57

Re: Display number of emails pending in each account?

Post by palinka » 2020-10-07 13:46

tunis wrote:
2020-10-07 13:30

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)
Haha! That's great, man! Crowd sourcing is awesome!

palinka
Senior user
Senior user
Posts: 2180
Joined: 2017-09-12 17:57

Re: Display number of emails pending in each account?

Post by palinka » 2020-10-07 15:27

tunis wrote:
2020-10-07 13:30

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)
I made one small modification.

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! :mrgreen:

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>

Post Reply