Reporting time from email to request

[m.vandun]

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

[Steve Giller]

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.

[Lyonel]

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

[m.vandun]

Hi @Lyonel,

That works perfectly. Thanks!

Do you know a way to exclude the time outside our working calendar?

[Steve Giller]

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.

[Lyonel]

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

 

[m.vandun]

@DeadMeatGF @Lyonel,

Thanks for the help, wil ltry and fiddle with these.

[dwalby]

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?

[Lyonel]

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

 

[dwalby]

@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

[Lyonel]

@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

[dwalby]

@Lyonel LEGEND - thanks! 

[Lyonel]

You're welcome @dwalby 

 

[dwalby]

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

 

[Lyonel]

@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

 

[Victor]

@Lyonel @dwalby iirc you actually can produce a widget to show you the number of emails sent per hour and per day...

[Lyonel]

@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

 

[Victor]

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

[Lyonel]

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

[Victor]

@Lyonel ah... chart... well no then, you're right