Mail traffic accounting

[kilburn]

The attached patch solves both accounting problems for me. All the above test cases come up with correct traffic counts, can you test it please?

Thanks for your help ispcomm!

[ispcomm]

Patches correctly for me and has no errors. I don't have the setup to test it as you do, however I'll put it in a production server immediately because I think it's good enough ;-)

If there're problems, I'll let you know.

well done Kilburn!

ispcomm.

[Zothos]

ill commit this patch this evening
Well done guys.

[kilburn]

Zothos do NOT commit it please. I've an svn account but this is not enough tested yet.

Bug: it doesn't work well if amavis is disabled (test cases 1-3 fail!)
Bug 2: when there are multiple forwarded recipients (amavis on), maillogconvert outputs n_forwarders -1 delivery lines, and all of them get counted with an added n_forwarders*size size, so you end up counting too much.

I added 6 more test cases. 3 of them use an address both@{dmn,sub.dmn,als}.tld, which are addresses that have both a local mailbox and a forward to me@external.dmn. The other 3 use an address multiple@{dmn,sub.dmn,als}.tld which are addresses forwarded (no mailbox) to 2 mails: me@external.dmn and other@external.dmn.

This is the output of maillogconvert.pl after running the total 12 test cases (each test case is separated with a blank line) on a sistem with amavis enabled:

Code:

2008-06-19 08:17:10 me@external.dmn user@dmn.tld test.server.tld 127.0.0.1 SMTP - 1 778295
2008-06-19 08:17:10 me@external.dmn user@dmn.tld test.server.tld virtual SMTP - 1 778666

2008-06-19 08:17:19 me@external.dmn user@sub.dmn.tld test.server.tld 127.0.0.1 SMTP - 1 222777
2008-06-19 08:17:19 me@external.dmn user@sub.dmn.tld test.server.tld virtual SMTP - 1 223156

2008-06-19 08:17:27 me@external.dmn user@als.tld test.server.tld 127.0.0.1 SMTP - 1 225379
2008-06-19 08:17:27 me@external.dmn user@als.tld test.server.tld virtual SMTP - 1 225750

2008-06-19 08:17:35 me@external.dmn redir@dmn.tld test.server.tld 127.0.0.1 SMTP - 1 778303
2008-06-19 08:17:35 me@external.dmn me@external.dmn test.server.tld mx.external.dmn SMTP - 1 778674

2008-06-19 08:17:41 me@external.dmn redir@sub.dmn.tld test.server.tld 127.0.0.1 SMTP - 1 222785
2008-06-19 08:17:42 me@external.dmn me@external.dmn test.server.tld mx.external.dmn SMTP - 1 223156

2008-06-19 08:17:50 me@external.dmn redir@als.tld test.server.tld 127.0.0.1 SMTP - 1 225387
2008-06-19 08:17:51 me@external.dmn me@external.dmn test.server.tld mx.external.dmn SMTP - 1 225758

2008-06-19 08:18:00 me@external.dmn both@dmn.tld test.server.tld 127.0.0.1 SMTP - 1 778297
2008-06-19 08:18:01 me@external.dmn both@dmn.tld test.server.tld 127.0.0.1 SMTP - 1 778297
2008-06-19 08:18:01 me@external.dmn both@dmn.tld test.server.tld virtual SMTP - 1 778628
2008-06-19 08:18:01 me@external.dmn both@dmn.tld test.server.tld mx.external.dmn SMTP - 1 778628
2008-06-19 08:18:01 me@external.dmn me@external.dmn test.server.tld mx.external.dmn SMTP - 1 778628

2008-06-19 08:18:10 me@external.dmn both@sub.dmn.tld test.server.tld 127.0.0.1 SMTP - 1 222779
2008-06-19 08:18:10 me@external.dmn both@sub.dmn.tld test.server.tld 127.0.0.1 SMTP - 1 222779
2008-06-19 08:18:10 me@external.dmn me@external.dmn test.server.tld mx.external.dmn SMTP - 1 223110
2008-06-19 08:18:10 me@external.dmn both@sub.dmn.tld test.server.tld mx.external.dmn SMTP - 1 223110
2008-06-19 08:18:10 me@external.dmn both@sub.dmn.tld test.server.tld virtual SMTP - 1 223110

2008-06-19 08:18:35 me@external.dmn both@als.tld test.server.tld 127.0.0.1 SMTP - 1 225381
2008-06-19 08:18:35 me@external.dmn both@als.tld test.server.tld 127.0.0.1 SMTP - 1 225381
2008-06-19 08:18:35 me@external.dmn both@als.tld test.server.tld mx.external.dmn SMTP - 1 225712
2008-06-19 08:18:35 me@external.dmn me@external.dmn test.server.tld mx.external.dmn SMTP - 1 225712
2008-06-19 08:18:35 me@external.dmn both@als.tld test.server.tld virtual SMTP - 1 225712

2008-06-19 08:19:02 me@external.dmn multiple@dmn.tld test.server.tld 127.0.0.1 SMTP - 1 778323
2008-06-19 08:19:02 me@external.dmn multiple@dmn.tld test.server.tld 127.0.0.1 SMTP - 1 778323
2008-06-19 08:19:03 me@external.dmn other@external.dmn test.server.tld mx.external.dmn SMTP - 1 778654
2008-06-19 08:19:03 me@external.dmn me@external.dmn test.server.tld mx.external.dmn SMTP - 1 778654

2008-06-19 08:19:29 me@external.dmn multiple@sub.dmn.tld test.server.tld 127.0.0.1 SMTP - 1 222805
2008-06-19 08:19:29 me@external.dmn multiple@sub.dmn.tld test.server.tld 127.0.0.1 SMTP - 1 222805
2008-06-19 08:19:29 me@external.dmn other@external.dmn test.server.tld mx.external.dmn SMTP - 1 223136
2008-06-19 08:19:29 me@external.dmn me@external.dmn test.server.tld mx.external.dmn SMTP - 1 223136

2008-06-19 08:19:47 me@external.dmn multiple@als.tld test.server.tld 127.0.0.1 SMTP - 1 225407
2008-06-19 08:19:47 me@external.dmn multiple@als.tld test.server.tld 127.0.0.1 SMTP - 1 225407
2008-06-19 08:19:48 me@external.dmn other@external.dmn test.server.tld mx.external.dmn SMTP - 1 225738
2008-06-19 08:19:48 me@external.dmn me@external.dmn test.server.tld mx.external.dmn SMTP - 1 225738

And this is the output of running the exact same tests on a system with amavis off:

Code:

2008-06-19 08:21:06 me@external.dmn user@dmn.tld test.server.tld virtual SMTP - 1 778297

2008-06-19 08:21:13 me@external.dmn user@sub.dmn.tld test.server.tld virtual SMTP - 1 222779

2008-06-19 08:21:18 me@external.dmn user@als.tld test.server.tld virtual SMTP - 1 225381

2008-06-19 08:21:26 me@external.dmn redir@dmn.tld test.server.tld mx.external.dmn SMTP - 1 778305

2008-06-19 08:21:33 me@external.dmn redir@sub.dmn.tld test.server.tld mx.external.dmn SMTP - 1 222787

2008-06-19 08:21:39 me@external.dmn redir@als.tld test.server.tld mx.external.dmn SMTP - 1 225389

2008-06-19 08:21:49 me@external.dmn both@dmn.tld test.server.tld mx.external.dmn SMTP - 1 778299
2008-06-19 08:21:49 me@external.dmn both@dmn.tld test.server.tld virtual SMTP - 1 778299

2008-06-19 08:21:55 me@external.dmn both@sub.dmn.tld test.server.tld virtual SMTP - 1 222781
2008-06-19 08:21:55 me@external.dmn both@sub.dmn.tld test.server.tld mx.external.dmn SMTP - 1 222781

2008-06-19 08:22:00 me@external.dmn both@als.tld test.server.tld virtual SMTP - 1 225383
2008-06-19 08:22:00 me@external.dmn both@als.tld test.server.tld mx.external.dmn SMTP - 1 225383

2008-06-19 08:22:08 me@external.dmn multiple@dmn.tld test.server.tld mx.external.dmn SMTP - 1 778325
2008-06-19 08:22:08 me@external.dmn multiple@dmn.tld test.server.tld mx.external.dmn SMTP - 1 778325

2008-06-19 08:22:17 me@external.dmn multiple@sub.dmn.tld test.server.tld mx.external.dmn SMTP - 1 222807
2008-06-19 08:22:17 me@external.dmn multiple@sub.dmn.tld test.server.tld mx.external.dmn SMTP - 1 222807

2008-06-19 08:22:24 me@external.dmn multiple@als.tld test.server.tld mx.external.dmn SMTP - 1 225409
2008-06-19 08:22:24 me@external.dmn multiple@als.tld test.server.tld mx.external.dmn SMTP - 1 225409

I'm trying to figure out an algorithm that counts the expected total value in all those test cases. Feel free to give me ideas!

[kilburn]

Well... I got all this test cases right!

Theory of operation (for each maillogconvert output line):

if Amavis DISABLED
- Count every line
- Add mail_size * n_forwarders to mail_size of FORWARDED ONLY accounts.

if Amavis ENABLED
- Ignore non-local-address to non-local-address
- Drop the virtual transport lines
- Add mail_size / n_forwarders to mail_size of FORWARDED ONLY accounts.

The "Add mail_size.." step must be there because accounts that are forwarded only miss the local delivery line (input traffic count). mail_size / n_forwarders is the extra size that must be added to each outgoing delivery of this "forwarded only" accounts.

Test case 4: redir@dmn.tld -> me@external.dmn (forwarded only)

Code:

2008-06-19 08:21:26 me@external.dmn redir@dmn.tld test.server.tld mx.external.dmn SMTP - 1 778305

We have just one (outgoing delivery) line, so the extra size to this delivery line is:

Code:

extra_size = mail_size / n_forwarders = 778305 / 1 = 778305

Total count: 778305 + 778305 = 1556610

Test case 10: multiple@dmn.tld -> me@external.dmn, other@external.dmn (forwarded only)

Code:

2008-06-19 08:19:02 me@external.dmn multiple@dmn.tld test.server.tld 127.0.0.1 SMTP - 1 778323
2008-06-19 08:19:02 me@external.dmn multiple@dmn.tld test.server.tld 127.0.0.1 SMTP - 1 778323

We have two (outgoing delivery) lines, so the extra size to this delivery line is:

Code:

extra_size = mail_size / n_forwarders = 778305 / 2 = 389152.5

Total count: 2 lines * (778305 + 389152.5) bytes/line = 2334915

Warning: Do not use it on production yet please, we are missing test cases where a local user sends mails (both to other local users and external addresses).

[ispcomm]

I missed something: When external mail arrives for a forwarded account do you count the size * 2 ? That is... 1MB arrives and 1MB leaves to the forwarded account. 2MB total for this email should be added to the domain.

I'll try to propose a simpler solution for amavis-on case. It assumes that deliveries to amavis will be on consecutive lines (probably true 99% of the time):

-) Count all deliveries to 127.0.0.1, but skip consecutive lines of the same value (i.e. the multiple recipients of the same mail).

-) Ignore all virtuals (they were accounted for on step 1).

-) Count all other SMTP traffic, mapping every destination address to the domain that forwarded it.

Another issue: What happens when a delivery is deferred?

ispcomm.

[kilburn]

ispcomm Wrote:I missed something: When external mail arrives for a forwarded account do you count the size * 2 ? That is... 1MB arrives and 1MB leaves to the forwarded account. 2MB total for this email should be added to the domain.

This is the Test case 4 example. Final count is mail_size * 2 so it's ok with my last patch.

ispcomm Wrote:I'll try to propose a simpler solution for amavis-on case. It assumes that deliveries to amavis will be on consecutive lines (probably true 99% of the time):

This may work, I don't really know if this is true 99% of the time or just 80% or whatever though.

ispcomm Wrote:-) Count all deliveries to 127.0.0.1, but skip consecutive lines of the same value (i.e. the multiple recipients of the same mail).

Hmm... just piping the awk's output through "uniq" may work here

ispcomm Wrote:-) Ignore all virtuals (they were accounted for on step 1).

Last patch version does just this (only if amavis is on, of course).

ispcomm Wrote:-) Count all other SMTP traffic, mapping every destination address to the domain that forwarded it.

Outgoing (forwarded) deliveries have no info about the original recipient, as in test case 4 (second smtp):

Code:

2008-06-19 08:17:35 me@external.dmn redir@dmn.tld test.server.tld 127.0.0.1 SMTP - 1 778303
2008-06-19 08:17:35 me@external.dmn me@external.dmn test.server.tld mx.external.dmn SMTP - 1 778674

There is no easy way of reverse-mapping "me@external.dmn" to "redir@dmn.tld" (it may seem easy here, but there could be some more deliveries between those lines). We would probably need to modify maillogconvert in order to do it...

ispcomm Wrote:Another issue: What happens when a delivery is deferred?

Deferred deliveries are filtered out by maillogconvert. As my current approach doesn't use any context info (no "same as last line" nor reverse-mappings or queue id tracking), the traffic will be properly accounted when the delivery is done correctly. The only problem in this regard is that incoming traffic may never be accounted if a mail sent to a forwarded address is undeliverable, but it's not a common case (why would someone redirect an account they own to one that doesn't work?).

Actually, my solution isn't that complicated. The relevant logic lies on those lines (virtual deliveries are stripped out before by awk, so both solutions would be the same in this regard):

Code:

+                if ((!exists $main::smtp_traffic{$smtp_from_domain}) && (!exists $main::smtp_traffic{$smtp_to_domain})) {
+                    next;
+                }
+                if (exists($forwarders_table{$smtp_to_mail})) {
+                    $extra_size = $traffic_size / $forwarders_table{$smtp_to_mail};
+                    $main::smtp_traffic{$smtp_to_domain} += $extra_size;
+                }

So 7 lines of logic that may be further reduced to 6 (I only use $external_size var to debug, it should be removed in a final version patch). Could you implement the reverse-mapping that easily?

[ispcomm]

Quote:<ispcomm>
-) Count all other SMTP traffic, mapping every destination address to the domain that forwarded it.

<kilburn>
Outgoing (forwarded) deliveries have no info about the original recipient, as in test case 4 (second smtp):
Code:
2008-06-19 08:17:35 me@external.dmn redir@dmn.tld test.server.tld 127.0.0.1 SMTP - 1 778303
2008-06-19 08:17:35 me@external.dmn me@external.dmn test.server.tld mx.external.dmn SMTP - 1 778674

There is no easy way of reverse-mapping "me@external.dmn" to "redir@dmn.tld" (it may seem easy here, but there could be some more deliveries between those lines). We would probably need to modify maillogconvert in order to do it...

Maybe I'm missing something. The hash table that can be constructed using data from the "big" sql query will give you a "forwarded address" -> "local domain" mapping.

You'll have modify the AWK script to also print the original destination in addition to the domains.

Having that destination->domain table you don't need to modify maillogconvert because you can directly map every SMTP ougoing address to a local domain and sum the mail size to the usage.

It does not matter if the delivery is deferred or if there are other deliveries in the middle because you don't need to keep the state.

This is why incoming and outgoing mail size computing is separated from each other: When mail arrives for a local recipient: Count its size once. When mail leaves to a recipient that is a known-forwarded address of a domain, count it once again to that domain.

There's a special case when 2 domains map to a single forwarding address. You will need state information to correctly account this type of situation or you could choose to either add size/number of domains to each of them or size to each of them (counting too-much). Counting size/number of domains would be a good-enough solution because the domains would be related to the same customer anyway.

As the ease of implementation... it's as easy as you did it: me@external.tld is a forward for redir@local.tld so mapping "destination mail" to the local domain that originated is a matter of a lookup in the forwarders table.

Whatever solution you choose, the current accounting is much better than what we started with anyway.

ispcomm.

[kilburn]

ispcomm Wrote:Maybe I'm missing something. The hash table that can be constructed using data from the "big" sql query will give you a "forwarded address" -> "local domain" mapping.

You are missing the fact that there may be multiple local domains that forward to the same address. Example:

user@local-dmn1.com -> user@external.dmn
user@local-dmn2.com -> user@external.dmn

So how do you know if the delivery should be accounted to local-dmn1 or local-dmn2?

[ispcomm]

Quote:So how do you know if the delivery should be accounted to local-dmn1 or local-dmn2?

I can't. I need state info for this or I must approximate the count averaging on all domains (not a good solution).

ispcomm.