chriscorcoran Posted January 21, 2019 Share Posted January 21, 2019 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? Link to comment Share on other sites More sharing options...
Steven Boardman Posted January 21, 2019 Share Posted January 21, 2019 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 Link to comment Share on other sites More sharing options...
chriscorcoran Posted January 22, 2019 Author Share Posted January 22, 2019 @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? Link to comment Share on other sites More sharing options...
Victor Posted January 22, 2019 Share Posted January 22, 2019 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? Link to comment Share on other sites More sharing options...
chriscorcoran Posted January 22, 2019 Author Share Posted January 22, 2019 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, Link to comment Share on other sites More sharing options...
Victor Posted January 22, 2019 Share Posted January 22, 2019 @chriscorcoran - any and every entry in the timeline of a request is in that table... 1 Link to comment Share on other sites More sharing options...
chriscorcoran Posted January 23, 2019 Author Share Posted January 23, 2019 @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 Link to comment Share on other sites More sharing options...
Victor Posted January 23, 2019 Share Posted January 23, 2019 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? Link to comment Share on other sites More sharing options...
Victor Posted January 23, 2019 Share Posted January 23, 2019 @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... Link to comment Share on other sites More sharing options...
chriscorcoran Posted January 23, 2019 Author Share Posted January 23, 2019 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. Link to comment Share on other sites More sharing options...
chriscorcoran Posted January 23, 2019 Author Share Posted January 23, 2019 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. Link to comment Share on other sites More sharing options...
Victor Posted January 23, 2019 Share Posted January 23, 2019 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 Link to comment Share on other sites More sharing options...
chriscorcoran Posted January 23, 2019 Author Share Posted January 23, 2019 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. Link to comment Share on other sites More sharing options...
chriscorcoran Posted January 28, 2019 Author Share Posted January 28, 2019 @Steven Boardman@Victor do you know which column gives me the most recent update? Link to comment Share on other sites More sharing options...
Victor Posted January 28, 2019 Share Posted January 28, 2019 @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... Link to comment Share on other sites More sharing options...
Steve G Posted January 28, 2019 Share Posted January 28, 2019 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 1 Link to comment Share on other sites More sharing options...
chriscorcoran Posted January 30, 2019 Author Share Posted January 30, 2019 @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. Link to comment Share on other sites More sharing options...
samwoo Posted January 31, 2019 Share Posted January 31, 2019 @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 Link to comment Share on other sites More sharing options...
Steve G Posted January 31, 2019 Share Posted January 31, 2019 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 1 Link to comment Share on other sites More sharing options...
chriscorcoran Posted February 1, 2019 Author Share Posted February 1, 2019 @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? Link to comment Share on other sites More sharing options...
Steve G Posted February 4, 2019 Share Posted February 4, 2019 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 Link to comment Share on other sites More sharing options...
chriscorcoran Posted February 5, 2019 Author Share Posted February 5, 2019 @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. Link to comment Share on other sites More sharing options...
Victor Posted February 5, 2019 Share Posted February 5, 2019 @chriscorcoran you can add this: m.h_msg_date > '2018-01-01 00:00:00' right before ORDER BY ... Link to comment Share on other sites More sharing options...
chriscorcoran Posted February 6, 2019 Author Share Posted February 6, 2019 @Victor thanks, is this in the filter do you mean? Link to comment Share on other sites More sharing options...
Victor Posted February 6, 2019 Share Posted February 6, 2019 @chriscorcoran - in the JOIN condition configuration. Here: 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