Report - updates / mails per agent

[m.vandun]

Hi,

I'm looking for a report that shows the amount of updates / emails our agents make on a daily / weekly basis. What is the best way of getting this report?

Kind Regards,

Mark

[Guest]

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

[m.vandun]

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

[Guest]

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:

 

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

[m.vandun]

@Bob Dickinson

This looks great! Thanks for the clear explanation and help! Will try this.

Regards,

Mark