Jump to content

Report - updates / mails per agent


m.vandun
 Share

Recommended Posts

Hi @m.vandun

I'm not sure if its going to be possible to easily extract this data but I'll certainly have a look - before I do, could you provide a little more information about what you are specifically looking for?

Emails:

  • Is this just for emails that have been sent from against a request - or simply ALL emails sent from the mailbox over the course of a time period?
  • Does this count require to be group by the individual who sent it?
Updates
  • Is the count purely for the "Update" type of action against a request timeline or for any interaction they have made with a request? (i.e. do you want to also include updates such as changes of priority, changes of status, adding the resolution etc?
Can I also ask what you are planning to use these counts for? Purely in case there is something else that could be reported on within Hornbill that could provide an alternative/additional approach?
 
Kind Regards
 
Bob
Link to comment
Share on other sites

Hi @Bob Dickinson,

Thanks for the reply. There are 2 reasons for getting this information.

1) I would like to know where my agents are spending their time on. Not only for time reporting but mainly for optimization of processes. I would really like to know, if there are specific incidents that need a lot of updates (could be email to customers or updates internally, before it is resolved). If I have this info I could work to better optimising the workflow.
2) I would like to know where my agents are spending their time on. The only information I currently have, is how many tickets are created and closed per agent. If I only use this info, my agents are not doing anything on a daily basis :).

Emails:

  • Is this just for emails that have been sent from against a request - or simply ALL emails sent from the mailbox over the course of a time period?
  • Does this count require to be group by the individual who sent it?

Emails by agent against a request.

Updates
  • Is the count purely for the "Update" type of action against a request timeline or for any interaction they have made with a request? (i.e. do you want to also include updates such as changes of priority, changes of status, adding the resolution etc?

Some of these other interactions could be useful, but i'm more interested in updates that have been made.

Kind Regards,

Mark

Link to comment
Share on other sites

  • 2 weeks later...

Hi @m.vandun

Sorry for the delay in coming back to you since you posted. So this is a complex report to generate as it is incorporating information from various different tables. I have managed to get something working that uses the List of Data widget and the "Custom" option we have to enter a SQL statement. Due to its complexity, this cannot currently be incorporated into the standard reports. 

The output  for "Request Updates By User this week" looks as follows:

Screenshot_4.png

 

This was achieved with the following SQL Statement:

Quote

SELECT SUBSTR(b.h_actor,14) as User, count(SUBSTR(b.h_actor,14)) as Posts
FROM h_buz_activities as b
JOIN h_buz_activity_streams as a ON a.h_id = b.h_target
JOIN h_itsm_requests as c ON c.h_pk_reference = RIGHT(a.h_display_name, 10)
JOIN h_sys_accounts as d ON SUBSTR(b.h_actor,14) = d.h_user_id
WHERE CAST(b.h_type AS CHAR CHARACTER SET latin1) COLLATE latin1_general_cs = 'update' AND d.h_class = 1 AND b.h_updated >= CURDATE() - INTERVAL (WEEKDAY(CURDATE())+1) DAY and c.h_requesttype = 'Incident'
GROUP BY User

A few notes about this:

1) This is joining 4 tables so it is important to add the specific criteria wherever possible. I have included a date range in this ("all requests update from the start of this week") to minimise the results and the load on the search. Also be wary of how frequently this is run. 

2) I have also added criteria to only look for Incidents, and only search for "Manual Updates" by analysts (e.g. if you update the request details, it gets automatically posted to the timeline - we call this an auto update. If you use the "Update" action tab, this is a "Manual Update"). If you want to include ALL updates, simply remove the text "CAST(b.h_type AS CHAR CHARACTER SET latin1) COLLATE latin1_general_cs = 'update' AND"

3) To perform the same for email updates, simply replace the line 

Quote

CAST(b.h_type AS CHAR CHARACTER SET latin1) COLLATE latin1_general_cs = 'update'

with 

Quote

b.h_type = 'Email'

 

I hope this helps,

Kind Regards

Bob

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
 Share

×
×
  • Create New...