Post your helpful reports here!

Discussion in 'Using your helpdesk' started by scravid, Nov 5, 2010.

  1. Gary McGrath

    Gary McGrath Kayako Staff Staff Member

    Hi there,

    Ok, this is going to be a wall of text, so bear with me, I am going to post various SQL searches for things which people might like ( sorry! )

    This is the SQL you can use to pull out chat information into a table that you can then use to build a pivot table and view various stats

    select chatobjectmaskid, (FROM_UNIXTIME(dateline)) as "Date of Chat", staffname, waittime, TIMEDIFF(FROM_UNIXTIME(lastpostactivity),FROM_UNIXTIME(dateline) ) as "Length of Chat"
    from swchatobjects
    where chatstatus = 3 and ((date(FROM_UNIXTIME(dateline)) between '2011/05/06' AND '2011/06/15'))

    But for your more specific number of chats per staff, you can use the below:

    N.B. If gives you staff name, number of chatsm average wait time to answer a chat request, length of all chats added together, per date range.

    select staffname, chatobjectmaskid,count(*) as "No. Chats", avg(waittime), Sec_to_Time(Sum(Time_to_Sec( TIMEDIFF(FROM_UNIXTIME(lastpostactivity),FROM_UNIXTIME(dateline) ) )))as "Length of Chat"
    from swchatobjects
    where chatstatus = 3 and ((date(FROM_UNIXTIME(dateline)) between '2011/05/06' AND '2011/06/15'))
    group by staffname

    Then onto ticket SQL!

    List number of open tickets per department

    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
    Group by swdepartments.title, swticketstatus.title

    ( I made it ignore "resolved tickets" and display everything else )

    This here is the SQL you will need for number of tickets per week per staff member.

    SELECT swticketstatus.title as Status, count(*) as "No of Tickets", swstaff.fullname
    from swtickets
    Right Join swticketstatus
    On swtickets.ticketstatusid = swticketstatus.ticketstatusid
    Right Join swstaff
    on swstaff.staffid = swtickets.ownerstaffid
    where ((date(FROM_UNIXTIME(swtickets.lastactivity)) between '2011/06/06' AND '2011/06/15'))
    Group by swticketstatus.title, swstaff.fullname
    Order by swstaff.fullname

    If you want to create a pivot table to give you all sorts of views, this code here can create the raw table:

    N.B. The ratings for feedback are going to be unique to the ones you created, if you run this SQL here:

    SELECT benchmarkid, benchmarktitle from swbenchmarks

    It will list your benchmark name and ID, you then just need to amend the SQL below to put the correct benchmark and name in. ( you can also delete all the benchmark stuff if you dont need them )

    SELECT swtickets.ticketmaskid, swtickets.isescalated as Escalated, swticketstatus.title as Status, swticketpriorities.title as Priority, swdepartments.title as Department, swstaff.fullname as "Ticket Owner", FROM_UNIXTIME(swtickets.dateline) as Created,
    (select benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=1 ) as "Overall Satisfaction",
    (select benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=2 ) as "Communication",
    (select benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=3 ) as "Response Time",
    (select benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=4 ) as "friendliness"
    from swtickets
    Right Join swticketstatus
    On swtickets.ticketstatusid = swticketstatus.ticketstatusid
    Right Join swticketpriorities
    On swticketpriorities.priorityid = swtickets.priorityid
    Right Join swdepartments
    On swdepartments.departmentid = swtickets.departmentid
    Right Join swstaff
    on swstaff.staffid = swtickets.ownerstaffid

    I hope all that made sense for you, holla back if its gibberish lol

    Gary
     
  2. malocite

    malocite Member

    I hope they're paying you for all of this :)

    I'm going to give those a try, you are a life saver Gary.
     
    Edward Cox likes this.
  3. Gary McGrath

    Gary McGrath Kayako Staff Staff Member

    Glad to help :D

    I know what its like with a boss on your back about stats and not being able to get them.

    Gary
     
  4. Rafael Uemura

    Rafael Uemura New Member

    hi everyone:)
    I've heard about this new reporting tool months ago, do you guys have any idea when it will be available?
    Rafael
     
  5. Gary McGrath

    Gary McGrath Kayako Staff Staff Member

    Hi Rafael,

    We were told this month at some point, but honestly I would not expect it until Aug

    Gary
     
  6. doc2MD

    doc2MD Member

    I feel your pain. I see there is a report options in the v 4.0, I dont want to have to write my own program to create a simple report.
    Please, I just want to be able to show how many ticket for a specified period and total time worked from within the CP.
     
  7. teejayuu

    teejayuu Established Member

    Hi

    Can anyone help me create a report that counts all tickets closed within their SLA by Department and Priority?
     
  8. Gary McGrath

    Gary McGrath Kayako Staff Staff Member

    Hi Teejay,

    Do you mean you want a count of tickets that have "not been" escalated? ( i.e. closed off / replied before time ran out )

    Gary
     
  9. teejayuu

    teejayuu Established Member

    Yes please
     
  10. Gary McGrath

    Gary McGrath Kayako Staff Staff Member

    Hi Tony,

    This SQL should do the trick for you:

    SELECT swdepartments.title as Department,prioritytitle as Priority, 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 = 1 and isescalated = 0 and escalationruleid != 0
    Group by swdepartments.title, prioritytitle

    Gary
     
  11. Evilution

    Evilution New Member

    Gary,
    I am needing a SQL for:

    Current Open tickets per department:
    Closed tickets per department in the last month

    Think you can help me out with this?
     
  12. Gary McGrath

    Gary McGrath Kayako Staff Staff Member

    Hi Evilution,

    Not a problem :)

    Current "Open" tickets per department:

    SELECT swdepartments.title as Department, 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 swticketstatus.title = "Open"
    Group by swdepartments.title

    Closed tickets per department ( Past month )

    SELECT swdepartments.title as Department, 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 = 1 and ((date(FROM_UNIXTIME(swtickets.resolutiondateline)) between '2011/05/01' AND '2011/05/31'))
    Group by swdepartments.title

    ( Note on that one, you just need to adjust the dates to suit, so you can make it weekly, two weekly, monthly, yearly etc.. )

    Gary
     
  13. Southerncentralrain

    Southerncentralrain Established Member

    We are on the hosted version so we can not use this. We have been reports since we have upgraded. The crazy thing is that we were mainly motivated to upgrade earlier because they said that the reporting was much better. This has gotten past ridiculous.
     
  14. Sunny Suggs

    Sunny Suggs New Member

    Hi! Thanks so much for posting these queries... they're saving my life!

    I was wondering if anyone knew how to count how many times a specific macro was used?

    Thanks!

    Sunny Suggs!
     
  15. Gary McGrath

    Gary McGrath Kayako Staff Staff Member

    Hi Sunny,

    You can track this, but as there is no "logging" of macro use, the only way you can count it up would be to run a series of sql searches against your actual ticket replies.

    This SQL below shows how many staff have used a specific macro ( per staff member )

    SELECT swstaff.fullname, count(*) FROM swticketposts
    Right Join swstaff
    on swstaff.staffid = swticketposts.staffid
    WHERE contents like "%There are a couple of things you can check which will help ensure your able to log in.%"
    group by swticketposts.staffid

    But you would need to change the "%There are a couple of things you can check which will help ensure your able to log in.%" to match the text you have in your macro. Please note that the % sign is actually an SQL symbol for "anything", so you need to keep them on both ends of the string, so that it will find the macro text "anywhere" within the reply.

    Also note that on a large database, this will be quite an intensive search and make take a little while.

    Gary
     
  16. Sunny Suggs

    Sunny Suggs New Member

    Oh fantastic.. thank you Gary! you ARE my new hero!
     
  17. teejayuu

    teejayuu Established Member

    Hi

    Using this from earlier posts:
    Code:
    SELECT swtickets.ticketmaskid, swticketstatus.title AS Status, swticketpriorities.title AS Priority, swdepartments.title AS Department, swstaff.fullname AS "Ticket Owner", FROM_UNIXTIME(swtickets.dateline) AS Created,
    (SELECT benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=1 ) AS "Overall Satisfaction",
    (SELECT benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=5 ) AS "Communication",
    (SELECT benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=6 ) AS "Response Time",
    (SELECT benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=7 ) AS "friendliness"
    FROM swtickets
    RIGHT JOIN swticketstatus
    ON swtickets.ticketstatusid = swticketstatus.ticketstatusid
    RIGHT JOIN swticketpriorities
    ON swticketpriorities.priorityid = swtickets.priorityid
    RIGHT JOIN swdepartments
    ON swdepartments.departmentid = swtickets.departmentid
    RIGHT JOIN swstaff
    ON swstaff.staffid = swtickets.ownerstaffid
    WHERE ((date(FROM_UNIXTIME(dateline)) between '2011/08/01' AND '2011/08/16')) AND (swticketstatus.title = 'Closed')
    ORDER BY swtickets.dateline ASC
    and it works a dream. Our users also leave comments when leaving ratings, in which table are these stored?
     
  18. Gary McGrath

    Gary McGrath Kayako Staff Staff Member

    Hi Tony,

    This here should do the trick for you:

    Code:
    SELECT swtickets.ticketmaskid, swticketstatus.title AS Status, swticketpriorities.title AS Priority, swdepartments.title AS Department, swstaff.fullname AS "Ticket Owner", FROM_UNIXTIME(swtickets.dateline) AS Created,
    (SELECT benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=1 ) AS "Overall Satisfaction",
    (SELECT benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=5 ) AS "Communication",
    (SELECT benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=6 ) AS "Response Time",
    (SELECT benchmarkresult from swbenchmarkresults where swbenchmarkresults.typeid = swtickets.ticketid and benchmarkid=7 ) AS "friendliness",
    (SELECT contents from swticketposts where swticketposts.ticketid = swtickets.ticketid and issurveycomment=1 ) AS "FeedBack Comment"
    FROM swtickets
    RIGHT JOIN swticketstatus
    ON swtickets.ticketstatusid = swticketstatus.ticketstatusid
    RIGHT JOIN swticketpriorities
    ON swticketpriorities.priorityid = swtickets.priorityid
    RIGHT JOIN swdepartments
    ON swdepartments.departmentid = swtickets.departmentid
    RIGHT JOIN swstaff
    ON swstaff.staffid = swtickets.ownerstaffid
    WHERE ((date(FROM_UNIXTIME(dateline)) between '2011/08/01' AND '2011/08/16')) AND (swticketstatus.title = 'Closed')
    ORDER BY swtickets.dateline ASC
    
    
    Gary
     
  19. teejayuu

    teejayuu Established Member

    Thanks Gary
     
  20. Gary McGrath

    Gary McGrath Kayako Staff Staff Member

    Your welcome :)

    Gary
     

Share This Page