measure Messages Measures (Inbox)

[QEHNick]

Hello, I'm trying to put together some "measures" to record how many emails come through the mailbox from INTERNAL and EXTERNAL sources.

I've tried several different ways, most recently.....

...using a "Query where clause" in this vein.

h_msg_rfc822_hdr LIKE '%From%OURDOMAINNAME%' 

..or..

h_msg_rfc822_hdr NOT LIKE '%From%OURDOMAINNAME%' 

The results are incorrect, too low to match what is actually coming through.

Does anyone have a way of doing this?

[Steve Giller]

I can't see how that would work in the first place.
"from" appears a whole bunch of times in that record, and before the "To" address, so it would pick up anything sent to your domain as well as from it.

[QEHNick]

@Steve Giller Yeah, I can't find a row which would have what I need to determine it.

Ah! I suppose if h_msg_from had  sender address which had "somethingsomething@DOMAIN.com", that would be my external since internals do not show the email address, just the senders name.

 

So something like...

h_msg_from NOT LIKE '%@%'         for internal emails?

[Steve Giller]

You'd have to validate that for your configuration, I don't have the exact criteria to hand for what records a name vs an email address, but assuming that these match internal Customers this sounds logical.

[QEHNick]

@Steve Giller yeah, I narrowed it down to the actual domain ('%@domainname.co.uk') however it's producing ridiculously high figures now. We have never had 100+ emails in a week.

So I tried replacing "LIKE" for "=" and "NOT LIKE" for "<>"; this then tells me we had over 4000 emails from external sources and zero from internal in a month. That's nowhere near reality.

[Steve Giller]

Are you putting a condition in to specify received emails?

[QEHNick]

Ah, good point @Steve Giller. I'm just looking at the mailbox. What would I need to use there then? One of the h_msg_status codes?

[QEHNick]

Yeah I think it's h_msg_status code "1" for received.

[QEHNick]

Ok, after much fiddling about I have used the following.

For Internal emails - h_msg_rfc822_hdr NOT LIKE '%Return-path% %@OUR_DOMAIN_NAME%' AND h_msg_status = 1

For Internal emails - h_msg_rfc822_hdr LIKE '%Return-path% %@OUR_DOMAIN_NAME%' AND h_msg_status = 1

Since there is no column entry for sender domain I have to look through the HDR content to find the necessary info to filter on.

So far, the output compares to the emails in the Inbox (and other) folders.

[Met]

2 hours ago, QEHNick said:

Yeah I think it's h_msg_status code "1" for received.

I think h_msg_status determines whether the email has been read or not. 1 = read, 2 = unread.

[QEHNick]

Ah that would make some sense.  Cheers @Met, I'll remove that statement as it's not relevant.

[QEHNick]

Weirdly, removing it made the figures go way high! More than the number of emails we had in. I'll keep it in...