1. Kayako Download customers: we will continue to develop and support Kayako Download beyond July 2017, alongside the new Kayako for existing customers.

    Find out more.

  2. The forum you are viewing relates to Kayako Classic. If you signed up or upgraded to the new Kayako (after the 4th July 2016), the information in this thread may not apply to you. You can visit the forums for the new Kayako here.

Post your helpful reports here!

Discussion in 'Using Kayako Classic' started by scravid, Nov 5, 2010.

  1. teejayuu

    teejayuu Established Member

    Has anyone got a time worked by staff report?
  2. Gary McGrath

    Gary McGrath Staff Member

    Hi Tony,

    This should do it for you, just make sure to chose the dates to suit, ( weekly, daily, monthly etc.. )

    select workerstaffname, (sum(timespent)/60/60) as 'Time Worked (hours)'
    from swtickettimetracks
    where ((date(FROM_UNIXTIME(dateline)) between '2011/01/01' AND '2011/10/31'))
    Group by workerstaffname

  3. jlc

    jlc Established Member


    Any way to schedule reports to run on a weekly basis for management from within Kayako?
  4. jlc

    jlc Established Member

    I forgot to mention email the reports to management on a weekly basis?.

  5. Gary McGrath

    Gary McGrath Staff Member

    Hi Jlc,

    Thats not possible inside kayako, but you can use an external script to do it, which you could schedule to run every week on a server or something

  6. Derren

    Derren New Member

    Well thanks Gary for all your advice you have given, it has helped so much.

    What we are missing though is a way to find a way to see how many tickets where open at the beginning of the day (say 00:01) and how many where open at the end of day (say 23:59)

  7. Gary McGrath

    Gary McGrath Staff Member

    Hi Derren,

    This here will do it

    SELECT swdepartments.title as Department,swticketstatus.title as Status, count(swtickets.ticketmaskid) as 'Number of Tickets'
    from swtickets
    Right Join swticketstatus
    On swtickets.ticketstatusid = swticketstatus.ticketstatusid
    Right Join swdepartments
    On swdepartments.departmentid = swtickets.departmentid
    where swtickets.isresolved = 0 and ((date(FROM_UNIXTIME(swtickets.dateline)) between '2011/10/06' AND '2011/10/06'))
    Group by swdepartments.title, swticketstatus.title

    Pretty much if you use the day before, it will list how many open tickets upto midnight, if you use the next day, it will show how many of those you closed

  8. Derren

    Derren New Member

    Thanks Gary. You make it to easy

    So I shall bug you just once more if you have the time.

    What would also like is something like the following

    3/10/11 34 33
    4/10/11 14 15

  9. Gary McGrath

    Gary McGrath Staff Member

    Hi Derren,

    Ok, here is the code, please please please, do not remove the where clause, or you will turn your SQL server into a doorstop while it tries to work it out for every date in your entire database.

    This is actually quite an intensive query, so if you dont limit the search range to something sensible, it will take a long time.

    SELECT date(FROM_UNIXTIME(swtickets.lastactivity)) as 'CDate' , ( select count(ticketmaskid) from swtickets where date(FROM_UNIXTIME(swtickets.dateline)) = CDate and isresolved=0 ) as 'Open', ( select count(ticketmaskid) from swtickets where date(FROM_UNIXTIME(swtickets.lastactivity)) = CDate and isresolved=1 ) as 'Closed'
    from swtickets
    where ((date(FROM_UNIXTIME(swtickets.lastactivity)) between '2011/10/01' AND '2011/10/31'))
    Group by date(FROM_UNIXTIME(swtickets.lastactivity))

  10. Derren

    Derren New Member

    Thanks once again Gary

    with regards to my first query;

    am I right in thinking that this will list tickets that are still open THAT WHERE CREATED within the date range?

    what I am looking at now is, can I chose a date, and then be shown how many tickets where still open at the end of that day, regardless of department or when the ticket was created. ie last thurday there where 5 tickets still open at close of business, 3 of them where over 5days old. etc

    thanks Derren
  11. Gary McGrath

    Gary McGrath Staff Member

    Hi Darren,

    This is quite tricky, as the only two fields you can check dates against are "date created" and "date last activity", neither of these technically give you what your looking for. ( if tickets are open for an extended period it gets blurry

    What your looking for is changing this: where swtickets.isresolved = 0 and ((date(FROM_UNIXTIME(swtickets.dateline)) between '2011/10/06' AND '2011/10/06'))
    to this: where swtickets.isresolved = 0

    That will show "all open tickets right now", and you would have to ensure you ran that every day, you could not go "back in time to check"

  12. Derren

    Derren New Member

    thanks gary. thats what i feared.

    i was even looking into audit trails to find and exclude everything that was switched to close.
  13. zztemp

    zztemp Established Member

    I would like to have a query that :

    gets the tickets that were created "today" (as a variable) and that were closed "today". So each day today auto changes.

    if possible also query that shows how many tickets were closed by a technician/staff on a specific date
  14. BobC

    BobC Member

    does anyone have any idea why this SQL doesn't work? (Doesn't produce any results)

    SELECT 'Tickets.Ticket Mask ID', 'Ticket Billing.Creation Date', 'Tickets.Subject', 'Tickets.Priority', 'Ticket Billing.Time Billable' FROM 'Ticket Billing' WHERE 'Ticket Billing.Creation Date' <= LastMonth();
  15. Gary McGrath

    Gary McGrath Staff Member

    Hi bob,

    I think you would just need to add in the tickets table, e.g. from 'Tickets', 'Ticket Billing'

  16. Pedro Reis

    Pedro Reis Established Member


    I need to generate a report to retrieve info with the count of tickets by user in descending order , like top 10 of ticket post users..any guideline will be appreciated. thanks
  17. latic

    latic New Member


    I've been fiddling around with kayako and cant get this one working right.

    We are trying to do a report based on the amount of replies a particular staff member has made both daily (shows each day and the number of replies by each staff member) and weekly (shows 4 weeks and the number of replies by each staff member).

    Any ideas?
  18. jlc

    jlc Established Member


    Any idea how I would be able to pull Custom Fields?
    I need to generate a report based on our defined categories.
  19. jlc

    jlc Established Member

    Does anyone know how?
  20. Alex Ivaylov

    Alex Ivaylov New Member

    Hi jlc, I came here to ask exactly the same question.

    I need to pull a table that contains the tickets and the rows are ticketid, full name, customfield 8 and customfield 9. So far I cant find the relationship between swtickets and swcustomfieldvalues.

    Anyone any ideas?

Share This Page