Post your helpful reports here!

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

  1. Alex Ivaylov

    Alex Ivaylov New Member

    After a few hours of pulling my hair, I managed to figure it out.

    It turns out that the `typeid` field in`swcustomfieldvalues` table is the `ticketid` field in the `swtickets` table. I have no idea why the kayako developers have chosen to call it with a different name.

    I have managed to put together this query:

    Code:
    SELECT `swtickets`.`ticketid` AS "id", `swtickets`.`ticketmaskid` AS "Mask ID", `swtickets`.`fullname` AS "Full Name", `swtickets`.`phoneno` AS "Telephone",
    `swcustomfieldvalues`.`fieldvalue` AS "Additional"
    FROM `swtickets`
    RIGHT JOIN `swcustomfieldvalues`
    ON `swcustomfieldvalues`.`typeid` = `swtickets`.`ticketid`
     
    --optimal if you only need particular fields:
    WHERE `swcustomfieldvalues`.`customfieldid` = '9' OR `swcustomfieldvalues`.`customfieldid` = '10'
    ORDER BY `swtickets`.`ticketid`
    LIMIT 0, 50;
     
  2. JWiggins

    JWiggins New Member

    This seems like a very helpful and active thread, so forgive me if I shouldn't post this inquiry/req for help:

    I need to run a report that shows all requests/tickets submitted for a given date range, 10/1/2011 - 3/31/2012. I do need the results returned to be agnostic of owner, type, status, and department. Just literally a SUM / count of all tickets submitted within said date range.

    I've scanned this thread, and done searches in this forum, and have found these (2) queries,

    1)

    SELECT COUNT(*) AS Total FROM 'Tickets' WHERE 'Tickets.Is Resolved' = '1' AND 'Tickets.Resolved Date' = 'Tickets.Resolved Date' > MKTIME (0,0,0,10,1,2011) AND 'Tickets.Resolved Date' < MKTIME (0,0,0,3,31,2012)

    For this query, i get a result of "0" returned

    2)

    select Monthname((date(FROM_UNIXTIME(dateline)))),departmenttitle as Department,count(*) as 'Number of Tickets'
    from tickets
    where ((date(FROM_UNIXTIME(dateline)) between '2011/10/01' AND '2012/3/31'))
    Group by Month((date(FROM_UNIXTIME(dateline)))), Departmentid

    For this query, I get the error below:

    Parse error: Expected EOQ on line 1 select Monthname((date(FROM_UNIXTIME(dateline)))),departmenttitle as Department,count(*) as 'Number of Tickets' ^ found: ")"

    Any help is greatly appreciated. My KQL/mySQL isn't so great. Thanks!
     
  3. Gary McGrath

    Gary McGrath Kayako Staff Staff Member

    Hi there,

    You need to use this I would suggest:

    Code:
     
    SELECT COUNT(*) AS Total FROM 'Tickets' WHERE 'Tickets.creation Date' > MKTIME (0,0,0,10,1,2011) AND 'Tickets.creation Date' < MKTIME (0,0,0,3,31,2012)
     
    
    If you want it to show totals per department, or staff member etc.. then just add at the end "group by department" for example, e.g.

    Code:
     
    SELECT COUNT(*) AS Total FROM 'Tickets' WHERE 'Tickets.creation Date' > MKTIME (0,0,0,10,1,2011) AND 'Tickets.creation Date' < MKTIME (0,0,0,3,31,2012) group by 'tickets.department'
     
    
    Gary
     
  4. JWiggins

    JWiggins New Member

    @Gary

    Hey, I appreciate the quick response, sorry it took me a bit to get back to this. The first query, just showed me a #, the second query, grouped it by Dept w/ a tally. So the second query is good.. But what the audit mgr here needs is, a full list, at least by ticket #, subject, time/date stamp. B/c from what I know, he is going to randomly be selecting tickets to inspect per certain criteria.

    Is that possible? I know 5k+ lines is a lot, to dump out, but that's what I need. Thoughts?
     
  5. Gary McGrath

    Gary McGrath Kayako Staff Staff Member

    Ah ok, misread what you were after.

    Code:
    
    SELECT 'Tickets.Ticket Mask ID', 'Tickets.Department', 'Tickets.Status','tickets.type', 'Tickets.Priority', 'Tickets.Owner', 'Tickets.Subject', 'Tickets.Creation Date' from 'tickets' WHERE 'Tickets.creation Date' > MKTIME (0,0,0,10,1,2011) AND 'Tickets.creation Date' < MKTIME (0,0,0,3,31,2012)
     
    
    Gary
     
    DonLapeno likes this.
  6. JWiggins

    JWiggins New Member

    dang! that's it. Are you a mySQL ninja or what? Much like some others on here, I fooled around w/ many queries and read through the wiki, and just didnt see a way to do anything w/ the date-range I needed.

    I really appreciate your help. Do we get to nominate people here for something ;)
     
  7. Gary McGrath

    Gary McGrath Kayako Staff Staff Member

    There is nothing to nominate for, we are just a community forum where we all help each other, about the most anyone can do is like someone elses posts, glad I could help though :)

    Gary
     
    DonLapeno and JWiggins like this.
  8. JWiggins

    JWiggins New Member

    then this is a very proactive, quick forum then. I've interacted w/ some that were nowhere near this fast, or as helpful -- and without some of the attitude you encounter on some forums.

    Again, thanks!
     
  9. Exie Smith

    Exie Smith Member

    Hi All,

    I created this query for a Kayako report I produce with the result set from excel. The query works, but I have noticed that I am not getting all of the tickets in the database and I am not sure why:
    use kayako_fusion;
    select * from swtickets t
    join swcustomfieldvalues cfv
    on t.ticketid = cfv.typeid
    inner join swcustomfieldoptions cfo
    on cfv.fieldvalue = cfo.customfieldoptionid;


    Theoretically, it should not exclude any tickets based on the code but for some reason there are some tickets that are just not picked up.

    As a caveat, I noticed this specifically for one of the users. Is there anything in the user settings that would prevent their ticket from being in the database?


    Any help / direction regarding this would be greatly appreciated.
     
  10. Drew Keller

    Drew Keller Just one person in a world of millions.

    Exie,

    If the ticket exists in the system it exists in the database, I would expect the issue is actually with your inner join, because with that I the custom field did not exist it would not show the data from swtickets, try changing to to an outer Join or dropping it fully to see of the missing tickets are returned
     
  11. Exie Smith

    Exie Smith Member

    Drew,

    Thanks so much for your help with this. I changed my joins to left joins and I appear to be getting tons more data returned.. almost double!
     
  12. pstarnfeld

    pstarnfeld Member

    Hi,

    Hope you can give me a hand with a report that is driving me crazy. I need to report that shows a list of all tickets of the last 30 days that include ticket ID, state of the ticket, Organization Name, User Name, organization custom field 1, organization custom field 2, organization custom field 3.

    Thanks for your help
     
  13. Drew Keller

    Drew Keller Just one person in a world of millions.

    Hi,

    At the moment Custom Fields are not available within KQL so you will need to have access to the MySQL database to be able to write this report.

    Also as custom fields are different for everyone (the Nature of Custom) to help we would only be able to provide an outline of the query and you would have to change it to suit your environment.

    So firstly are you self hosted or On Demand?
     
  14. Drew Keller

    Drew Keller Just one person in a world of millions.

    Also, you will need to advise the types of fields that organization custom field 1, organization custom field 2, organization custom field 3 are as different types look different in the Database.
     
  15. teejayuu

    teejayuu Established Member

    Hello
    I need to create a reports that shows all tickets created between 16:00 and 20:00 for the past year. Is this possible?
    Thanks
     
  16. Drew Keller

    Drew Keller Just one person in a world of millions.

    Tony, that's an interesting report. I can't have a look tonight, but possibly will have time tomorrow. Are you self hosted? Only asking incase it cannot be done via the KQL reporting module.
     
  17. teejayuu

    teejayuu Established Member

    Thanks Drew - we are self hosted
     
  18. Drew Keller

    Drew Keller Just one person in a world of millions.

    Tony,

    Yes does not look like it is supported in KQL (no option to just return time like you can day, month etc)

    Possibly something could be written against the MySQL database or via the API, unfortunetly I dont have time to look at writing the query. Sorry I cant help.
     
  19. teejayuu

    teejayuu Established Member

    Thanks Drew,
    I've actually sorted it (I think)
    Code:
    SELECT COUNT('Tickets.Creation Date') FROM 'Tickets' WHERE 'Tickets.Creation Date' >= LastMonth() GROUP BY 'Tickets.Creation Date':Hour
     
  20. Drew Keller

    Drew Keller Just one person in a world of millions.

    Tony, yes I must have been a little distracted when I looked at it. I was looking for time but hour is exactly what you needed.

    You should be able to have a where statement like where( create date:hour >15 and create date:hour < 21). But to do it fr the whole your you would need to use a maketime statement as there is no this year statement in KQL.
     

Share This Page