Reports to show how many ticket responses staff have sent in a month

Is there a report that shows how many tickets a staff member has replied to in a month? Not the number of closures but just a count on the number of responses the staff have sent (grouped by staff name).

I think this query is what you are looking for.

Code:

SELECT count(*) from 'Ticket Audit Logs' where 'Ticket Audit Logs.Creator' = 'Staff' and 'Ticket Audit Logs.Message' like '%reply created%' and 'Ticket Audit Logs.Creation Date' = Lastmonth() GROUP BY 'Ticket Audit Logs.Full Name' 

Thanks for this Drew, I have been wanting to do this for a while and now can (just upgraded to latest version where reports now work!). Found this and it is great.

Just wondering, is it possible to generate one per day? Or from a certain date to be server for a similar purpose?

Thanks in advance.

Duncan,

You just need to change the LastMonth to the date you want. you might need to use the PHP mktime(h, min, s, m, d, y) to get the correct date format.

Hope this helps

You can also use

SELECT count(*) from 'Ticket Audit Logs' where 'Ticket Audit Logs.Creator' = 'Staff' and 'Ticket Audit Logs.Message' like '%reply created%' and 'Ticket Audit Logs.Creation Date' Between '2012-06-01' And '2012-07-13' GROUP BY 'Ticket Audit Logs.Full Name'

Obviously use the dates that you are after in place of those above.

However if you need time specifics then Drew's mktime will serve you much better.

I tried the above query with the dates, but no matter what date range I hardcode in the query, the results are always the same. There is no error message either. Any ideas?

Try using:

Code:

... 'Ticket Audit Logs.Creation Date' > MKTIME(0, 0, 0, d, m, y) AND 'Ticket Audit Logs.Creation Date' < MKTIME(59, 59, 23, d, m, y)

Replace dates with what you want.

Thanks! I added this, however it does not show per staff. Any suggestions?

Also the result of 177.842 for a month seems a bit high to me (for one month).

For staff use:

Code:

... GROUP BY 'Ticket Audit Logs.Full Name' 

The "Full Name" shows the users, not the staff. Instead, I've added 'GROUP BY 'Tickets.Owner' which works fine.

Note also re. the dates:
MKTIME(0, 0, 0, d, m, y)

Should be
MKTIME(0, 0, 0, m, d, y)


I have noticed that with this query the amount of ticket replies seem too high. I personally did some tickets last week, and the number shown can't be the amount of replies I actually did.

Also there is an "empty" staff member in the list which has a result too. Any idea where this nameless staff member comes from?

Can you post your actual query, so we can have a look and give it a try.

ALso what version of Kayako are you on?

My original query

Code:

SELECT count(*) from 'Ticket Audit Logs' where 'Ticket Audit Logs.Creator' = 'Staff' and 'Ticket Audit Logs.Message' like '%reply created%' and 'Ticket Audit Logs.Creation Date' = Lastmonth() GROUP BY 'Ticket Audit Logs.Full Name'

for me only returns what I have audited to be correct values (we are only a small team so its easy to check)

i suspect your "empty" staff will be tickets where they are unassigned as you are using tickets.owner not the 'Ticket Audit Logs.Full Name' which is who actually replied. (this field can show both customer or staff name which is why I included 'Ticket Audit Logs.Creator' = 'Staff'.

I have this:

yeah this query will show all entries in the audit log not just ticket replies.

Try

Code:

SELECT count(*) from 'Ticket Audit Logs' where 'Ticket Audit Logs.Creator' = 'Staff' and 'Ticket Audit Logs.Message' like '%reply created%' and ('Ticket Audit Logs.Creation Date' > MKTIME(0, 0, 0, 10, 01, 2012) AND 'Ticket Audit Logs.Creation Date' < MKTIME(59, 59, 23, 10, 31, 2012) ) GROUP BY 'Ticket Audit Logs.Full Name'

Perfect, this result makes more sense. Also no emtpy staff person. Thanks a lot for your help Drew!

but I would say that given your current date ranges you could just use

Code:

SELECT count(*) from 'Ticket Audit Logs'
where 'Ticket Audit Logs.Creator' = 'Staff'
and 'Ticket Audit Logs.Message' like '%reply created%'
and ('Ticket Audit Logs.Creation Date' = thismonth() 
GROUP BY 'Ticket Audit Logs.Full Name'

Yes, but I want to have a flexible date range to be able to show e.g. May 2012.

No problems, just remember if you are using the ticket audit log that it contains all auditable activity so if you are after something specific you need to make sure your where statement limits the results to exactly what you need. Which is why the query I gave you has both

Code:

'Ticket Audit Logs.Creator' = 'Staff'

and

Code:

'Ticket Audit Logs.Message' like '%reply created%'

Yes, this is why I contacted the forum as the filters can be very tricky and you end up with wrong data sets quickly.

I need a report that shows how many tickets each staff member worked...not necessarily opened or closed but worked.

drjoaaquin,

What do you class as being worked.
Status change?
Reply?
Just open?

Let me know and i will see what i can do.