Jump to content

Recommended Posts

Posted

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?

 

last update on call.png

Posted

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

 

Posted
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?

Posted

@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

Posted
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? :unsure:

Posted

@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...

Posted
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.

Posted
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? :unsure:

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.

Posted
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

Posted
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.

  • Victor changed the title to Report on first and last email update on requests
Posted

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):

image.png

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

  • Like 1
Posted

@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

Posted

@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?

Posted

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

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
×
×
  • Create New...