Reports on Mailboxes

[SJEaton]

Hi

Am I able to run a report that shows the number of emails received to a Mailbox each day or set up a measure for this?  If yes, how?

Thanks

Sam

 

[TrevorKillick]

@SJEaton

You can report on h_msg_messages where h_msg_date = CURDATE();
 

select * from h_msg_messages where h_msg_date = CURDATE();

You could do this as a measure using h_msg_date as a date ranging column with a refresh rate of daily. 

Filtering by mailbox will be a little tricky as you would need to get the list of folderId for a given mailbox and use where h_folder_id in (x,y,z); 

Kind Regards

Trevor Killick 

[SJEaton]

Hi @TrevorKillick, can you let me know where I can find the folderId for a given mailbox so I give this a try?  thanks

Sam

[Victor]

@SJEaton folder information is stored in "h_msg_folders" table... but to get the folder ID, in case you have multiple mailboxes, you now need the mailbox ID ...which is stored in "h_msg_mailboxes" table

So, get the mailbox ID from h_msg_mailboxes table then you can get the folder IDs with a statement like:

SELECT h_folder_id, h_folder_name FROM h_msg_folders WHERE h_mbid = <mailbox_id>

Replace <mailbox_id> with the ID of the mailbox...

[SJEaton]

Hi @Victor thanks but I'm still failing to determine what I need to enter in the relevant fields to get the measure working.  I can find h_msg_mailboxes in tables list and key column as h_msg_id but I still don't know how to determine what the mailbox id is for the mailbox I want to report on in order to enter it in the statement.  I've also tried to build a report but can't get that working either. I'm spending too much time on this without any luck so just need an idiots guide I think !

Sam

[Victor]

@SJEaton what is the name of the mailbox you want to report?

[SJEaton]

Neighbourhoods

[Victor]

@SJEaton 

This report: Neighbourhoods - Emails Received in Mailbox per day needs to filter on folder ID ... because h_msg_messages table only reference a folder ID not a mailbox ID. So you would need to get a list of folder IDs first. This can be done using this statement:

SELECT h_folder_id, h_folder_name FROM h_msg_folders WHERE h_mbid = 'Neighbourhoods'

Once you have the list of folder IDs then you can use it in the report. I already amended Neighbourhoods - Emails Received in Mailbox report to used these folder IDs. You can exclude certain folders here if you don't want to report all...

Another way would be to automatically obtain these folder IDs in the report (this if you want to count for all folders). I have created another report Neighbourhoods - Emails Received in Mailbox per day - Copy Hornbill which joins h_msg_mailboxes table as well and filters on mailbox name directly. The filter is much more simple and you don't need to worry about folder IDs in report configuration.

[SJEaton]

OK thanks @Victor I'll have another look at the report.  What about the measure?  What would I need to put in the fields to report on emails received per day? 

Sam

[Victor]

@SJEaton not sure if using measure will work to get the daily count... I'll do a test and see what we get

 

EDIT: I have amended the measure, seems to be working fine. Have a look and see if the results are correct...

[SJEaton]

Hi @Victor thanks for editing these, its definitely a lot better and getting data that looks more like what I was expecting but will need to do some cross-checking to see if the data is accurate.  

Can the report be amended so that it actually just bring back a list of all emails received on the date entered in the parameter please?  This way I can cross-check with the measure figures.  I tried to amend myself but changing to date is equal to in the relevant filter but it didn't work.  (I don't think reporting is my strong point! haha)

Sam

(p.s. just to add that obviously the other issue where dates aren't pulling through on csv's is affecting this report too)  

[SJEaton]

@Victor something else I need to consider now I'm looking at the data is that I only want to report on emails received in the inbox.  I think it's currently showing sent items also i.e. emails from itself!  Sam 

[Victor]

13 minutes ago, SJEaton said:

Can the report be amended so that it actually just bring back a list of all emails received on the date entered in the parameter please?

@SJEaton I have amended the "Hornbill Copy" one as an example...  The way you can achieve this is to have two parameters (greater than and less than)... so for example, if you want to get emails from 22nd then for Date 1 you select 22/11/2017 (no time) and for Date 2 you select 23/11/2017 (no time). The report will return all results with: date > 22/11 (00:00:00) and date < 23/11 (00:00:00). Date 1 and Date 2 are just random label I choose, can be changed if needed...

3 minutes ago, SJEaton said:

I only want to report on emails received in the inbox.  I think it's currently showing sent items also i.e. emails from itself!

Yes, I did say the reports are returning results from all folders and if you want only specific folders the filter will have to be amended. So, in the original report (not the copy), you need to remove the folder IDs you don't want in the report. So, run this statement:

SELECT h_folder_id, h_folder_name FROM h_msg_folders WHERE h_mbid = 'Neighbourhoods'

Make a note of all the folder IDs you want to count and only put these in the report filter. (Note: if you want to copy report to exclude certain folder then it needs different filters) 

19 minutes ago, SJEaton said:

the other issue where dates aren't pulling through on csv's is affecting this report

Yes, it affects all reports. I created and pinned a thread yesterday that is an issue that is being addressed in the next platform (ESP) update.

[SJEaton]

3 minutes ago, Victor said:

way you can achieve this is to have two parameters (greater than and less than)...

Its now showing just for one date, thanks @Victor.  Can you not just have a parameter 'value equals' rather than entering 2 dates though

 

7 minutes ago, Victor said:

So, run this statement:

SELECT h_folder_id, h_folder_name FROM h_msg_folders WHERE h_mbid = 'Neighbourhoods'

Where do I run this statement??

[Victor]

5 minutes ago, SJEaton said:

Can you not just have a parameter 'value equals' rather than entering 2 dates though

@SJEaton unfortunately not... the reason being we only have a date and time selector, not just date... so if you would have a "value equals" it will match the time as well, which you don't need...

7 minutes ago, SJEaton said:

Where do I run this statement??

You can run in it Database Direct... or create a temporary report to get the values... (report is better rather than meddling with Database Direct)

[SJEaton]

7 minutes ago, Victor said:

create a temporary report to get the values

@Victor I'm really sorry but I'm still struggling, why is this so complicated! lol

When you are saying SELECT h_folder_id, h_folder_name FROM h_msg_folders WHERE h_mbid = 'Neighbourhoods'  - I don't understand still where you 'run this statement'.  I've created a report called 'Folder IDs' that I thought might work but its not bringing anything back

 

[Victor]

@SJEaton actually is my fault, I made it complicated... the statement I gave you is wrong, I do apologise...   

SELECT h_folder_id, h_folder_name FROM h_msg_folders WHERE h_mbid = 192

This is the correct one... because we are filtering on mailbox ID not mailbox name... *sigh

I have amended the Folder ID report to reflect this...

[SJEaton]

Phew! thanks.  I now have my folder IDs so can exclude the ones I don't want  

Sam

[SJEaton]

Thanks for you help and patience on this @Victor, I think I've now got what I need for now.

Sam

[Victor]

@SJEaton no worries, happy to help