m.vandun Posted February 15, 2017 Share Posted February 15, 2017 Hi, Is there a way to report on the time it took between receiving an email and the creation of a request? I want to avoid creating requests automatically as I like having the freedom of setting it to the correct service but I would like to be able and see if we are meeting our service levels. Regards, Mark Link to comment Share on other sites More sharing options...
Steve Giller Posted February 15, 2017 Share Posted February 15, 2017 I haven't done this, but my instinct suggests that if you log automatically from the email, then have that run a simple Process a Select Service node in it this will cause it to skip to the correct Progressive Capture once the service is chosen. That way the time from the initial logging of the call to the selection of the service is the time taken to respond to the email. Link to comment Share on other sites More sharing options...
Lyonel Posted February 15, 2017 Share Posted February 15, 2017 Hi @m.vandun, I had the same question asked to me by my boss a few month back, so I created a report to do so. If you are familiar with SQL, here is the code: SELECT h_pk_reference, h_datelogged, h_msg_date FROM h_itsm_requests INNER JOIN h_msg_messages ON h_source_id = h_msg_id WHERE h_source_type = 'email' NOTE: This piece of SQL will explicitly return the list of requests created from an email. Just in case you are not familiar with SQL statements, I also attached a definition of a report (generating this SQL): get-email-date.report.txt I hope this will help you achieve what you want, it certainly did for me 1 Link to comment Share on other sites More sharing options...
m.vandun Posted February 15, 2017 Author Share Posted February 15, 2017 Hi @Lyonel, That works perfectly. Thanks! Do you know a way to exclude the time outside our working calendar? Link to comment Share on other sites More sharing options...
Steve Giller Posted February 15, 2017 Share Posted February 15, 2017 I think to take working calendars into account you'd have to go down the route of automatic logging on receipt, starting the Timer and stopping it once a service is selected. 1 Link to comment Share on other sites More sharing options...
Lyonel Posted February 15, 2017 Share Posted February 15, 2017 You will have to use Excel for this as you cannot perform calculations in a report... Other method is to use the Database direct option and use MySQL functions. I am sure you can find useful scripts on Google 1 Link to comment Share on other sites More sharing options...
m.vandun Posted February 16, 2017 Author Share Posted February 16, 2017 @DeadMeatGF @Lyonel, Thanks for the help, wil ltry and fiddle with these. Link to comment Share on other sites More sharing options...
dwalby Posted March 15, 2018 Share Posted March 15, 2018 Hi @Lyonel - I'm trying to achieve the same as the OP, ideally I'd also like to be able to do this as a measure so we can track this as the 'Response' time between e-mail arrival and being raised as a request. Do you any suggestions on how to do that? Link to comment Share on other sites More sharing options...
Lyonel Posted March 15, 2018 Share Posted March 15, 2018 @dwalby as a measure it will be incredibly difficult... Probably impossible actually. Why a measure? Why not a report, now that we can schedule reports to run? Link to comment Share on other sites More sharing options...
dwalby Posted March 15, 2018 Share Posted March 15, 2018 @Lyonel - I thought that by having it as a measure it'd allow tracking of the 'average time to log as request' over a period of time. A report could work if not though, how can this be done? I've tried looking at where to enter your SQL code but can't see where to put it Link to comment Share on other sites More sharing options...
Lyonel Posted March 15, 2018 Share Posted March 15, 2018 @dwalby, Download this file and import it as a report: extract---time-to-log-a-call-from-an-e-mail.report.txt Then run the report and export it to Excel. From there, you will be able to do whatever you want See my example: Extract - Time to log a call from an e-mail_1126.xlsx 1 Link to comment Share on other sites More sharing options...
dwalby Posted March 16, 2018 Share Posted March 16, 2018 @Lyonel LEGEND - thanks! Link to comment Share on other sites More sharing options...
Lyonel Posted March 16, 2018 Share Posted March 16, 2018 You're welcome @dwalby Link to comment Share on other sites More sharing options...
dwalby Posted April 3, 2018 Share Posted April 3, 2018 @Lyonel just on the off chance, as per my post below, I'm also trying to track the number of e-mails sent to our service desk throughout a day per hour in order to calculate demand, etc. I'd like to be able to produce a chart that shows the number of e-mails received per hour and have it mapped to a bar graph so we can understand our busiest times. Do you know of a way of doing this without needing to export to Excel? Link to comment Share on other sites More sharing options...
Lyonel Posted April 3, 2018 Share Posted April 3, 2018 @dwalby as far as I know, it is not possible to have a widget displaying this kind of information. So you will have to go through export and Excel I am afraid... An alternative solution is to use PowerBI: https://wiki.hornbill.com/index.php/PowerBI_Reporting Link to comment Share on other sites More sharing options...
Victor Posted July 6, 2018 Share Posted July 6, 2018 @Lyonel @dwalby iirc you actually can produce a widget to show you the number of emails sent per hour and per day... Link to comment Share on other sites More sharing options...
Lyonel Posted July 6, 2018 Share Posted July 6, 2018 @Victor I would be curious to see how. I have not played with measures in a long time, but as far as I remember, I could not achieve something like that last time I tried Link to comment Share on other sites More sharing options...
Victor Posted July 6, 2018 Share Posted July 6, 2018 @Lyonel it would be a widget with a custom SQL Query and the query would be something like this: SELECT CONCAT(DAY(h_msg_date),'/',MONTH(h_msg_date),'/', YEAR(h_msg_date)) AS 'Day', CONCAT(HOUR(h_msg_date),' - ',HOUR(h_msg_date)+1) AS 'Interval', COUNT(HOUR(h_msg_date)) AS 'Count' FROM h_msg_messages WHERE h_msg_date BETWEEN 'YYYY-MM-DD HH:MM:SS' AND 'YYYY-MM-DD HH:MM:SS' GROUP BY CONCAT(DAY(h_msg_date),'/',MONTH(h_msg_date),'/', YEAR(h_msg_date)), HOUR(h_msg_date) The result would be something like this: The query can be amended if you like, for example, to display the email count per hour regardless of the day to give you an overall of the busiest time of the day. In this case the query would be: SELECT CONCAT(HOUR(h_msg_date),' - ',HOUR(h_msg_date)+1) AS 'Interval', COUNT(HOUR(h_msg_date)) AS 'Count' FROM h_msg_messages WHERE h_msg_date BETWEEN 'YYYY-MM-DD HH:MM:SS' AND 'YYYY-MM-DD HH:MM:SS' GROUP BY HOUR(h_msg_date) The result would be something like this: The above queries contain a WHERE clause to filter out emails received between two dates. Obviously this can be adjusted as per your needs (even eliminated if you like) Link to comment Share on other sites More sharing options...
Lyonel Posted July 6, 2018 Share Posted July 6, 2018 @Victor that's what I thought But my understanding of what @dwalby wanted was a chart, hence my previous response. I think his objective was to make this more visual. Maybe something like that: Link to comment Share on other sites More sharing options...
Victor Posted July 6, 2018 Share Posted July 6, 2018 @Lyonel ah... chart... well no then, you're right 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