Jump to content

Reporting time from email to request


m.vandun

Recommended Posts

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

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

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

  • Like 1
Link to comment
Share on other sites

  • 1 year later...

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

@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

  • 3 weeks later...

@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

  • 3 months later...

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

image.png

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:

image.png

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

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