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

Discussion in 'Kayako Reports' started by Tfx77, Jun 17, 2012.

  1. Tfx77

    Tfx77 New Member

    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).
  2. Drew Keller

    Drew Keller Just one person in a world of millions.

    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' 
  3. duncan-wa

    duncan-wa New Member

    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.
  4. Drew Keller

    Drew Keller Just one person in a world of millions.

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

    wildkiwi Member

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

    dicks Established Member

    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?
  7. Andriy Lesyuk

    Andriy Lesyuk Developer

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

    dicks Established Member

    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).
  9. Andriy Lesyuk

    Andriy Lesyuk Developer

    For staff use:
    Code:
    ... GROUP BY 'Ticket Audit Logs.Full Name' 
  10. dicks

    dicks Established Member

    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?
  11. Drew Keller

    Drew Keller Just one person in a world of millions.

    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'.
  12. dicks

    dicks Established Member

    I have this:

  13. Drew Keller

    Drew Keller Just one person in a world of millions.

    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'
  14. dicks

    dicks Established Member

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

    Drew Keller Just one person in a world of millions.

    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'
  16. dicks

    dicks Established Member

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

    Drew Keller Just one person in a world of millions.

    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%'
  18. dicks

    dicks Established Member

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

    drjoaquin Member

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

    Drew Keller Just one person in a world of millions.

    drjoaaquin,

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

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

Share This Page