Jump to content

Email Volume Reporting - h_msg_message


Martyn Houghton
 Share

Recommended Posts

I am looking to report and generate measures on the number of emails we receive and send via Hornbill. There does not appear to be any definition on the Wiki for the h_msg_message database table. I can of course data range this via the h_msg_date, but I trying to determine which fields determine if it is incoming or outgoing, or is it just a matter of checking the the h_msg_from and h_msg_to?

image.thumb.png.c488aa9c6346db3f677dbef5d2ab2b0c.png

Cheers

Martyn

Link to comment
Share on other sites

Hi Martyn,

We do everything off the recipient's table, related to the message.  Consider a message being sent from one co-worker to another, or one shared mailbox to another which ones would be incoming or outgoing...  the only way to tell this is to look at the from and two recipients and determine that.  Alternatively, you also know that any messages that are sat in the sent items folder are probably outgoing but you would need to also exclude any mailbox to mailbox messages.  There are quite a lot of variations to that theme. For your case you can probably just consider stuff in the sent items as outgoing. 

Gerry

 

Link to comment
Share on other sites

@Gerry

Thanks, I have tried to do a query based on the to and from, but though database direct reports the in green the query has run successfully the output is not returned. Same SQL without the where clause does return the count value. I suspect that as the query take so long to run with the where clause the UI is not returning it.

The reason for doing from the message table was to get metrics on the number of emails the service desk is receiving, which not all of them will be used to create or update an incidents.

Cheers

Martyn

Link to comment
Share on other sites

Martyn,

One of the reasons we don't really document system type tables is they are designed for performance and scale rather than reporting, that often means reporting can be a right royal...  this table can be quite big, please be aware of the queries you are running as you could quite easily bog down the database servers and our techs will be killing off your queries if that happens.  Limit your queries by date ranges, if anything takes longer than a few seconds then do the query in smaller chunks. 

It might actually be better here if you could moc-up the report output you would like to see, we could perhaps implement something to get what you need thats already made for you. 

 

Gerry

Link to comment
Share on other sites

@Gerry

My initial volume queries where to ascertain how many emails we have received/sent, but in the end just went with the combined total for a date period, i.e. last year, before last.

I am looking to monitor, i.e. use a measure, to track monthly/weekly email volumes, ideally broken down into whether they are incoming to the mailbox or outgoing, with different measures for each mailbox (we currently have 5).

Cheers

Martyn

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...