Martyn Houghton Posted November 1, 2018 Share Posted November 1, 2018 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? Cheers Martyn Link to comment Share on other sites More sharing options...
Gerry Posted November 1, 2018 Share Posted November 1, 2018 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 More sharing options...
Martyn Houghton Posted November 2, 2018 Author Share Posted November 2, 2018 @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 More sharing options...
Daniel Dekel Posted November 2, 2018 Share Posted November 2, 2018 @Martyn Houghton, Just bare in mind that not all the columns are indexed. The following ones are indexed: h_msg_id h_msg_date_received h_msg_flag h_folder_id h_msg_status h_msg_size All other columns are not indexed and can make the query considerably slower. Daniel. Link to comment Share on other sites More sharing options...
Martyn Houghton Posted November 2, 2018 Author Share Posted November 2, 2018 @Daniel Dekel Thanks, for confirming the indexes. I indeed can query okay on date range, but not the to/from addresses. Is there any documentation on the meaning of h_msg_flag and h_msh_status columns? Cheers Martyn Link to comment Share on other sites More sharing options...
Daniel Dekel Posted November 2, 2018 Share Posted November 2, 2018 @Martyn Houghton, h_msg_flag is the star you see next to the email. 1 is True. h_msg_status is the read/unread. 1 means unread. Daniel. 1 Link to comment Share on other sites More sharing options...
Gerry Posted November 2, 2018 Share Posted November 2, 2018 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 More sharing options...
Martyn Houghton Posted November 2, 2018 Author Share Posted November 2, 2018 @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 More sharing options...
Gerry Posted November 9, 2018 Share Posted November 9, 2018 @Martyn Houghton Using a measure is the way to go here. I believe that the date fields are indexed so should act as good pre-selectors as far as query efficiency is concerned. Gerry Link to comment Share on other sites More sharing options...
Martyn Houghton Posted November 14, 2018 Author Share Posted November 14, 2018 @Gerry Date fields are indeed indexed but the to/from are not so not able to determine between incoming and out going. Cheers Martyn Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now