Need a few queries written

Discussion in 'Kayako Reports' started by mvt2244, Apr 11, 2012.

  1. mvt2244

    mvt2244 New Member

    Hi All!
    My cousin's business has recently had an installation of the Kayako Ticketing system and he would like some reports built. He is not computer savvy and has reached out to me! I am not familiar with Kayako at all but I know SQL which is why he approached me. I have never even seen the system which is why I am posting some questions here.

    I was wondering if anyone could help me in creating about 6-7 call summary related queries.
    One example would be: For staff member, John Doe, I would like to know how many tickets has John Doe resolved in a day. I would like to know daily statistics for, say, a month.
    Basically, one axis with dates (Apr 1, Apr 2, Apr 3) and the other axis with the number of resolved tickets for the day.

    Please contact me if you can help me generate a few such queries.

    Cheers
    Pam
  2. chrisrouse

    chrisrouse Reputed Member

    Have you looked through the included reports yet? You'll probably find a number of these in those.
  3. mvt2244

    mvt2244 New Member

    Hi Chris,
    Unfortunately, I do not have access to the system. I am away from the physical location of my cousin. My cousin says the reports he is lookng for is not included. He wants some custom reports.
    Honestly, I'm thrown in the deep end here and looking for any kind of support. I thought I'll start looking at some queries posted here and get a feel for the system!
  4. chrisrouse

    chrisrouse Reputed Member

    You may want your cousin to chime in here directly or search through the reports forum. There are already a lot of helpful reports provided from users, and a couple of users can come up with the report configurations in no time at all. Since there are a lot of variables to the reports, it's hard to just provide arbitrary options without more specifics for what is needed in the reports.
  5. Drew Keller

    Drew Keller Just one person in a world of millions.

    Pam.

    As Chris said there are so many variables and a few ways to get the data, without knowing exactly what you want I will share a report I use. ITs not exactly what you mention but similar.

    SELECT 'ticket audit logs.Full Name', count(*) as Total from 'Ticket Audit Logs' where 'Ticket Audit Logs.Creator' = 'Staff' and 'Ticket Audit Logs.Message' like '%closed%' and 'Ticket Audit Logs.Creation Date' = ThisMonth() group by 'Ticket Audit Logs.Creation Date':Day, 'Ticket Audit Logs.Creation Date':Monthname

    the results look like;

    Creation Date Creation Date Full Name Total
    10 April Derek 21
    11 April Drew Keller 18
    12 April Drew Keller 19
    1 April Drew Keller 16
    2 April Drew Keller 27
    3 April Derek Morton 21
    4 April Drew Keller 7
    5 April Drew Keller 5
    6 April Drew Keller 12
    7 April Drew Keller 14
    8 April Drew Keller 10
    9 April Drew Keller 9

    As Kayako is web based I would suggest you get your Cousin to give you access so you can have a play (Your access can even be limited to just reports)

    You will find alot of suggestions within the Forum as to reports but you will alos find alot of the reports that are provided give you a good basis for writing your own. At the moment Kayako do not provide a Data Dictionary so I use the existing reports as a guide.

    Also when typing in a query you get prompts for auto complete for tables and columns (Although there was a bug effecting IE9 so you might find it easier in Firefox or Chrome, if your Cousin has not patched this bug)

    Also note that at this stage KSQL (The reporting engine) does not support reporting on custom fields so if you needed to report at this level you would ahve to have access to the MySQL database directly (IE. self hosted only)

    If you have some more requirements let us know ans we will see what we can draft to help.

    Hope this helps.
  6. mvt2244

    mvt2244 New Member

    Hi All,

    Thanks for your messages. Much appreciated.
    Drew, thanks for particularly useful tips and the query you have posted. That could be exactly what I am looking for. Since Kayako does not have a data dictionary, I will try and see if I can access the system myself.

    Would I be able to request my cousin to give me rights to access the system remotely? By that I mean, we are not in the same city. Would I be able to ask for him to give me access to the reporting module like you've mentioned. This way I can try to access the module through Firefox and run some queries.

    Thanks!
  7. Drew Keller

    Drew Keller Just one person in a world of millions.

    Pam.

    Kayako is a full web applications so as long as your Cousin has it installed on an externally accessable web server then you can be granted access (In my case my server is hosted in a data centre in New Zealand while all our staff are based in Melbourne Australia so we are in Different Countries).

    I think the first step is to get that access, alternatively you should sign up for a tiral of Kayako using there hosted solution but the issue would be no data to play with to know your queries are close to being correct.
  8. Sivonei Neneve

    Sivonei Neneve New Member

    How to get an orderer result ?

    I Tried with ': Day' selector, but apparently, it is in String format :( , causing the wrong ordering

    like :
    1 April Drew Keller 16
    2 April Drew Keller 27
    3 April Derek Morton 21
    4 April Drew Keller 7
    5 April Drew Keller 5
    6 April Drew Keller 12
    7 April Drew Keller 14
    8 April Drew Keller 10
    9 April Drew Keller 9
    10 April Derek 21
    11 April Drew Keller 18
    12 April Drew Keller 19
  9. Sivonei Neneve

    Sivonei Neneve New Member

  10. masterctrl1

    masterctrl1 Kayako Guru

    What is probably not obvious to 99% of the posters on this forum is -- you have to login to JIRA to vote on bug reports or suggested tweaks and enhancements.
    Otherwise they just sit here on the forum until someone makes the effort to enter them in JIRA.
    If they then sit in JIRA with little or no votes, the likelyhood of any action being taken remains low.
  11. Sivonei Neneve

    Sivonei Neneve New Member

    Well , now I have nothing less than 3 acounts, just to deal with Kayako ... :D
    How about unified logins ?
  12. Drew Keller

    Drew Keller Just one person in a world of millions.

    Add the wiki and you have 4 accounts yes unified login would be nice, and maybe a nice portal with links to all these disparate sites
  13. Sivonei Neneve

    Sivonei Neneve New Member

    Great to 'heard' that !
    Thank you
    :)

Share This Page