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.

GDPR: Anonymise or delete users over 5 years old?

Discussion in 'Using Kayako Classic' started by brooky, May 7, 2018.

  1. brooky

    brooky Established Member

    Hot topic!

    I've rewritten our companies privacy policy to be inline with upcoming GDPR laws coming in VERY soon!

    Once of my policies is that we only store customer data for five years since the last time we heard form them. For this reason I need to delete or anonymise customer accounts who haven't created or replied to a ticket in the last five years.

    Has anyone found a way to do this? If not I'll code something myself.

    Thanks.
     
  2. brooky

    brooky Established Member

    I've written the following SQL which seem to work really well. PLEASE BE CAREFUL if you choose to try it and take a backup first!!!!

    Code:
    DELETE FROM `swticketposts` WHERE `dateline` < UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 5 YEAR);
    DELETE FROM `swticketnotes` WHERE `dateline` < UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 5 YEAR);
    DELETE `swtickets`.* FROM `swtickets` LEFT JOIN `swticketposts` ON `swticketposts`.`ticketid` = `swtickets`.`ticketid` WHERE `swticketposts`.`email` IS NULL;
    DELETE `swticketmessageids`.* FROM `swticketmessageids` LEFT JOIN `swticketposts` ON `swticketposts`.`ticketid` = `swticketmessageids`.`ticketid` WHERE `swticketposts`.`email` IS NULL;
    DELETE `swticketauditlogs`.* FROM `swticketauditlogs` LEFT JOIN `swticketposts` ON `swticketposts`.`ticketid` = `swticketauditlogs`.`ticketid` WHERE `swticketposts`.`email` IS NULL;
    DELETE `swticketrecipients`.*FROM `swticketrecipients` LEFT JOIN `swticketposts` ON `swticketposts`.`ticketid` = `swticketrecipients`.`ticketid` WHERE `swticketposts`.`email` IS NULL;
    DELETE FROM `swusers` WHERE `lastvisit` < UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 5 YEAR);
    DELETE `swuseremails`.* FROM `swuseremails` LEFT JOIN `swusers` ON `swusers`.`userid` = `swuseremails`.`useremailid` WHERE `swusers`.`fullname` IS NULL;
    DELETE `swuserorganizations`.* FROM `swuserorganizations` LEFT JOIN `swusers` ON `swusers`.`userorganizationid` = `swuserorganizations`.`userorganizationid` WHERE `swusers`.`fullname` IS NULL;
    
     
    Last edited: May 7, 2018
  3. brooky

    brooky Established Member

    I've since written this into a cron to keep my database purged and over 1GB of data removed. :p
     
    Last edited: May 7, 2018
  4. brooky

    brooky Established Member

    WARNING: This line is bad!

    Code:
    DELETE `swuseremails`.* FROM `swuseremails` LEFT JOIN `swusers` ON `swusers`.`userid` = `swuseremails`.`useremailid` WHERE `swusers`.`fullname` IS NULL;
    
     
  5. callenords

    callenords New Member

    Thanks! Keep us updated. You don’t have chats to delete (only tickets)?

    How did you come up with 5 years?
     
  6. brooky

    brooky Established Member

    Five years just seemed right to me. The SQL above is bugggy. If I don't visit or login toa website for five years I wouldn't want my details kept.

    I've got a scheduled task running this code every 10 mins and it's working well.

    I'll give it a few more days and post the code.

    Correct I don't use the live chat. I'm sure it's easy to purge that too but I'm not sure my instance even has the tables.
     
    Last edited: May 10, 2018

Share This Page