Report on first and last email update on requests

[chriscorcoran]

Hi I'm trying to get the last action\update on a call into a report. I have tried to use the table h_itsm_requesthistupdates with column h_description but Its not displaying, Any ideas?

 

[Steven Boardman]

Hi @chriscorcoran the activity stream updates for each request are held in the b_buz_activites table.  You will need to join the tables as described in the forum post referenced below. 

You might want to apply filters to this based on update type (System, Update, Email etc) and others to limit it to just the update/s you want.

Hope that helps

Steve

 

[chriscorcoran]

@Steven Boardmanany idea where I could find inbound email update? Also is there a way to detect if it came form a customer or coworker?

[Victor]

57 minutes ago, chriscorcoran said:

any idea where I could find inbound email update?

Do you mean how to filter request updates which are inbound emails applied to that request?

[chriscorcoran]

5 minutes ago, Victor said:

Do you mean how to filter request updates which are inbound emails applied to that request?

@Victor Are they updates in the same table? If so I must have missed them, 

[Victor]

@chriscorcoran - any and every entry in the timeline of a request is in that table...

[chriscorcoran]

@victor for a report I'm doing I'm trying to get the following, do you think that is possible?

A report where it has the first email and most recent email contact on it (ideally append onto the all incidents report, though I’ll take a separate one as long as it has the incident ref on it.

An ideally sight of last customer update as well as last update on the views in hornbill

[Victor]

6 minutes ago, chriscorcoran said:

sight of last customer update

Ok, so you want a report showing the last/latest customer update made via an email per request...right?

7 minutes ago, chriscorcoran said:

as well as last update on the views in hornbill

I am not sure what this means, can you detail, please? Or clarify? 

[Victor]

@chriscorcoran - we received some notifications of slow running queries caused by your instance... are you building and running a report querying requests and activities table? If so please let us know what you need to report because current queries will cause issues on your service performance...

[chriscorcoran]

15 minutes ago, Victor said:

@chriscorcoran - we received some notifications of slow running queries caused by your instance... are you building and running a report querying requests and activities table? If so please let us know what you need to report because current queries will cause issues on your service performance...

@Victor thanks. No I haven't built anything new yet. I have just checked with our support manager and he was running something. I need to check what it was. He has stopped now, so we should be all good. Thanks for the flag.

[chriscorcoran]

57 minutes ago, Victor said:

Ok, so you want a report showing the last/latest customer update made via an email per request...right?

I am not sure what this means, can you detail, please? Or clarify? 

Yes so what I'm trying to do is build a  report that shows all calls for a service but it needs to include the first inbound email from the customer and the latest email update added to a call, so I guess a filter.

[Victor]

22 minutes ago, chriscorcoran said:

the first inbound email from the customer

and

22 minutes ago, chriscorcoran said:

the latest email update added to a call

The issue here is "the first" and "the latest" ... to put this in some technical terms you need some sort of a MIN() and MAX() functions on a specific type of values something that is not possible with Hornbill current reporting engine  ... I think the best idea at this point would be to look at a PowerBI integration as you have much more functionality using PowerBi... https://wiki.hornbill.com/index.php/PowerBI_Reporting

[chriscorcoran]

32 minutes ago, Victor said:

and

The issue here is "the first" and "the latest" ... to put this in some technical terms you need some sort of a MIN() and MAX() functions on a specific type of values something that is not possible with Hornbill current reporting engine  ... I think the best idea at this point would be to look at a PowerBI integration as you have much more functionality using PowerBi... https://wiki.hornbill.com/index.php/PowerBI_Reporting

@Victorthanks, I will give that go.

[chriscorcoran]

@Steven Boardman@Victor do you know which column gives me the most recent update?

[Victor]

@chriscorcoran - It appears I was wrong, @Steve G , our dev guru, found a way to create a report in Hornbill that will give you what you are after. I understand he will come here with more info as soon as possible...

[Steve G]

Hi @chriscorcoran,

What you need is actually possible with reporting when using JOINs and some funky custom criteria to define the JOIN clauses. I've built and attached a basic report that will return a list of requests, and against each request returned it will output the first and latest timeline updates for emails received: requests-with-email-updates.report.txt

The custom JOINs are using sub-queries to retrieve the unique IDs for request timeline posts that had an email as its source, where the email was sent to a specific email address (you need to replace yourservicedesk@email.com with the relevant email address against BOTH actA and actB joins):

Note, I've also added a filter to the report, so that it will only return requests that have had a timeline update via email. Feel free to add other filters and/or columns to the report to retrieve the data you need.

Let me know how you get on with this.

Cheers
Steve

[chriscorcoran]

@Steve G Thanks Steve, I was out the office the last couple of days so missed your post. I will  have a play now. Many thanks for taking the time to look into this and do the report, much appreciated.

[samwoo]

@Steve G

Can you clarify what JSON_VALUE does?

This looks like something of benefit to us as well so will have to sit down and have a go.

Thanks,

Samuel

[Steve G]

Hi @samwoo,

It's a function that natively parses and retrieves data from JSON objects stored in a database column - h_buz_activities.h_extra contains some JSON data, so it's just a way of getting to its properties via SQL. Documentation for the function can be found in the MariaDB knowledgebase:  https://mariadb.com/kb/en/library/json_value/

Cheers,

Steve

[chriscorcoran]

@Steve GHi Steve I had a play on the report, its close but its not picking up the latest email, As an example if you look at IN00025440, it’s only pulling the first inbound message for both first and last message (10/1) but the last email was outbound on the 17/1.
any thoughts. as an aside I dont mind if we have to purchase some dev time to nail this report?

[Steve G]

Hi @chriscorcoran,

The report I provided was set to retrieve timeline updates for incoming emails only, and not outgoing emails. If you remove the AND m.h_msg_to... clause from your joins then that will retrieve the first and last timeline updates for emails incoming or outgoing from a request. I've attached a copy of the report from above, but with these clauses removed from the join sub-queries. 

Cheers,
Steve

requests-with-email-updates.report.txt

[chriscorcoran]

@Steve G That's spot on, thanks so much. Is it easy enough to add in a filter to select everything from 01/01/18 to current date? If I add in a user prompt for dates it breaks my R script.

 

 

[Victor]

@chriscorcoran you can add this:

m.h_msg_date > '2018-01-01 00:00:00'

right before ORDER BY ...

[chriscorcoran]

@Victor thanks, is this in the filter do you mean?

[Victor]

@chriscorcoran - in the JOIN condition configuration. Here: