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.

Help with Ticket post content report

Discussion in 'Kayako reports (Kayako Classic)' started by Torbjörn S., Nov 14, 2017.

  1. Torbjörn S.

    Torbjörn S. Reputed Member

    Hello.

    I have made a report that looks like this.
    Code:
    SELECT 'Ticket Posts.Contents', 'Ticket Posts.Creator', 'Ticket Posts.Creation Date', 'Ticket Posts.Full Name' FROM 'Ticket Posts', 'Tickets' GROUP BY 'Tickets.Ticket ID', 'Tickets.Subject', 'Tickets.User',  CUSTOMFIELD('Tickets', 'Customfieldname') 
    It works well but it only gets the first user post.
    I wan't it to show the full conversation. I can find it in the DB under swticketposts.

    Can anyone please help me out here so I can get the report showing what I need?

    Thanks in advance.
     
  2. Torbjörn S.

    Torbjörn S. Reputed Member

    I got it working but now I'm stuck ocn getting the ticketnotes connected to the tickets. It's the 2 lines with ?????? that I need help with.
    I only get error.

    Code:
    SELECT
    tickets.ticketid AS 'tickets_ticketid',
    ticketposts.ishtml AS 'ticketposts_contents_ishtml',
    ticketposts.contents AS 'ticketposts_contents',
    ticketposts.creator AS 'ticketposts_creator',
    ticketposts.ticketpostid AS 'ticketposts_ticketpostid',
    ticketposts.fullname AS 'ticketposts_fullname',
    tickets.subject AS 'tickets_subject',
    users2.fullname AS 'users_fullname',
    customfield2.fieldvalue AS '_cf_2',
    customfield2.isserialized AS '_cf_2_isserialized',
    customfield2.isencrypted AS '_cf_2_isencrypted',
    ticketnotes.note AS 'ticket_notes'  ???????????????????????????
    
    FROM swticketposts AS ticketposts
    LEFT JOIN swtickets AS tickets ON ticketposts.ticketid = tickets.ticketid
    LEFT JOIN swusers AS users ON tickets.userid = users.userid
    LEFT JOIN swusers AS users2 ON tickets.userid = users2.userid
    LEFT JOIN swtickets AS notes ON tickets.ticketid = ticketnotes.linktypeid ??????????????????????????????
    LEFT JOIN swcustomfieldvalues AS customfield2 ON customfield2.customfieldid = '2' AND customfield2.fieldvalue != '' AND customfield2.typeid = tickets.ticketid
    WHERE tickets.departmentid IN ('17','20','15','14','13','12','3','6','19','11','10','18','9','5','4','16')
    
    
    GROUP BY `tickets_ticketid`, `ticketposts_ticketpostid`, `tickets_subject`, `users_fullname`, `_cf_2`
    ORDER BY `tickets_ticketid`, `ticketposts_ticketpostid`, `tickets_subject`, `users_fullname` 
     
  3. Torbjörn S.

    Torbjörn S. Reputed Member

    I figured it out and I thought I post it if anyone else needs anything similar in the future.
    many values are unique to my installation but you get the idea.

    Code:
    SELECT
    tickets.ticketid AS 'tickets_ticketid',
    ticketposts.ishtml AS 'ticketposts_contents_ishtml',
    ticketposts.contents AS 'ticketposts_contents',
    ticketposts.creator AS 'ticketposts_creator',
    ticketposts.ticketpostid AS 'ticketposts_ticketpostid',
    ticketposts.fullname AS 'ticketposts_fullname',
    tickets.subject AS 'tickets_subject',
    users2.fullname AS 'users_fullname',
    customfield2.fieldvalue AS '_cf_2',
    customfield2.isserialized AS '_cf_2_isserialized',
    customfield2.isencrypted AS '_cf_2_isencrypted',
    notes.note AS Note
    
    
    FROM swticketposts AS ticketposts
    
    LEFT JOIN swticketnotes AS notes on ticketposts.ticketid = notes.linktypeid
    LEFT JOIN swtickets AS tickets ON ticketposts.ticketid = tickets.ticketid
    LEFT JOIN swusers AS users ON tickets.userid = users.userid
    LEFT JOIN swusers AS users2 ON tickets.userid = users2.userid
    LEFT JOIN swcustomfieldvalues AS customfield2 ON customfield2.customfieldid = '2' AND customfield2.fieldvalue != '' AND customfield2.typeid = tickets.ticketid
      
    
    WHERE tickets.departmentid IN ('17','20','15','14','13','12','3','6','19','11','10','18','9','5','4','16')
    
    GROUP BY `tickets_ticketid`, `ticketposts_ticketpostid`, `ticketposts_fullname`, `tickets_subject`,`users_fullname`, `_cf_2`, `note`
    
    ORDER BY `tickets_ticketid`, `ticketposts_ticketpostid`, `tickets_subject`, `users_fullname` LIMIT 100, 2000               
     

Share This Page