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.

Cleaning up unused / old user accounts.

Discussion in 'Using Kayako Classic' started by MvdL79, Nov 20, 2019.

  1. MvdL79

    MvdL79 Member

    Hi all,

    I have a questions. During the years we have used Kayako we noticed that we have a ton of users who;

    1. Are not with our company anymore
    2. Have never logged in after a certain date
    3. Have zero tickets to their name

    Is there some way to clean up these old user accounts?

    I know the database stores the information when an user has logged in for the last time. Maybe there is a way to delete older users prior for (example) january 2015.

    That way we have a smaller database and old users who have not logged in after january 2015 will be removed. This is also in accordance with the European GDPR obviously.

    Regards
     
  2. Evgeny Kuchkov

    Evgeny Kuchkov Established Member

    You have a table 'swusers' in your kayako_fusion database. There are the following columns -
    'dateline' - timestamp when user was created
    'lastupdate' - ts of last update
    'lastvisit' - ts of last visit obviously
    'lastvisit2' - current time when 'lastvisit' function executed (basically you dont need it)

    bear in mind you have different tables related to these users which should be cleared before you delete records from swusers:
    swuseremails
    swusernotes and swusernotedata
    swuserorganizationlinks
    swuserprofileimages
    swuserproperties
    swusersettings

    so you can go with this sql:
    (IMPORTANT!!! if you dont understand whats happening below you need to find someone who does!!!)
    to get userids you need to remove based on lastvisit:
    select userid from swusers where lastvisit < unix_timestamp('2015-01-01 00:00:00')
    or based on number of tt:
    select userid from swusers where userid not in (select distinct userid from swtickets)

    so with these userids you can work with tables I mentioned above:
    delete from swuserorganizationlinks where userid in (select userid from swusers where lastvisit < unix_timestamp('2015-01-01 00:00:00'))
    etc
     
  3. MvdL79

    MvdL79 Member

    Sorry for the very late reply Evgeny Kuchkov!

    I was in the hospital for a brief period of time.

    I read your post and information. Thank you for that, however I do have a few questions.

    I ran your command:
    select userid from swusers where lastvisit < unix_timestamp('2015-01-01 00:00:00')

    This indeed reports back a >TON< of users.
    I checked several ones to make sure they are old accounts. I think it's better to test first with 2009 or similar to be sure.

    And I do understand the command you mentioned here:
    delete from swuserorganizationlinks where userid in (select userid from swusers where lastvisit < unix_timestamp('2015-01-01 00:00:00'))

    This will delete the the mentioned users, rigth?


    However I don't understand what you mean with:
    bear in mind you have different tables related to these users which should be cleared before you delete records from swusers:
    swuseremails
    swusernotes and swusernotedata
    swuserorganizationlinks
    swuserprofileimages
    swuserproperties
    swusersettings

    Do I also have to delete them manually, or is your command enough for that?
    Because your command will only remove swusers before 2015. Right?

    Thanks in advance and once again sorry for the very late reply!
     
  4. MvdL79

    MvdL79 Member

    Uhmz... I also experimented with older dates, but the amount of results returned is the same?
    Even when using: select userid from swusers where lastvisit < unix_timestamp('1990-01-01 00:00:00')

    I am getting the same amount of users returned. So are your sure that's the correct command? Sorry, don't mean no offence.
     
  5. MvdL79

    MvdL79 Member

    Maybe a combined search of lastupdate, lastvisit, lastvisit2 and lastactivity would be better to make sure of non-used / abosolete accounts?

    See for example of pretty new user, who registered apparently, but didn't make a post.
    Example:

    [​IMG]
     
  6. MvdL79

    MvdL79 Member

    Okay sorry for bumping, though my MySQL knowledge is limited, I came up with a better search result:

    select userid from swusers where lastvisit < unix_timestamp('2015-01-01 00:00:00') AND lastvisit2 < unix_timestamp('2015-01-01 00:00:00') AND lastactivity < unix_timestamp('2015-01-01 00:00:00') AND dateline < unix_timestamp('2015-01-01 00:00:00')

    This returns less hits and seems to be more legit. Checking a few users to make sure obviously.


    //edit:

    Seems everything returned here are indeed old accounts (prior to 2015). So what is the next step remove all the necessary details from these old users. I think I already cleaned old posts.
     
  7. MvdL79

    MvdL79 Member

    @Gurpreet Singh can you please answer this?

    Before I remove active / used accounts by accident?
    We really need to clean out old and unused accounts especially considering the GDPR.

    Thanks!
     
  8. Evgeny Kuchkov

    Evgeny Kuchkov Established Member

    Hi there. This forum is dead and not visiting it much.

    So, since you have a list of userids' for removal next step is to clean up related tables:

    /* emails */
    delete from swuseremails where linktypeid in (select userid from swusers where lastvisit < unix_timestamp('2015-01-01 00:00:00') AND lastvisit2 < unix_timestamp('2015-01-01 00:00:00') AND lastactivity < unix_timestamp('2015-01-01 00:00:00') AND dateline < unix_timestamp('2015-01-01 00:00:00')) and linktype = 1
    /* linktype = 1 is for user account, 2 is for organization account. Ids can be the same, you need to drop only users */

    /* notes */
    delete from swusernotes where linktypeid in (select userid from swusers where lastvisit < unix_timestamp('2015-01-01 00:00:00') AND lastvisit2 < unix_timestamp('2015-01-01 00:00:00') AND lastactivity < unix_timestamp('2015-01-01 00:00:00') AND dateline < unix_timestamp('2015-01-01 00:00:00')) and linktype = 1

    /* notes contents */
    delete from swusernotedata where usernotid not in (select usernoteid from swusernotes)
    /* also deletes dead notes date if they appeared in database somehow */

    /* organization links */
    delete from swuserorganizationlinks where userid in (select userid from swusers where lastvisit < unix_timestamp('2015-01-01 00:00:00') AND lastvisit2 < unix_timestamp('2015-01-01 00:00:00') AND lastactivity < unix_timestamp('2015-01-01 00:00:00') AND dateline < unix_timestamp('2015-01-01 00:00:00'))

    /* all other stuff */
    delete from swuserprofileimages where userid in (...)
    delete from swuserproperties where userid in (...)
    delete from swusersettings where userid in (...)
    delete from swuserverifyhash where userid in (...)

    /* and finally delete users from swusers */
    delete from swusers where userid in (select userid from swusers where lastvisit < unix_timestamp('2015-01-01 00:00:00') AND lastvisit2 < unix_timestamp('2015-01-01 00:00:00') AND lastactivity < unix_timestamp('2015-01-01 00:00:00') AND dateline < unix_timestamp('2015-01-01 00:00:00'))
     
  9. MvdL79

    MvdL79 Member

    Hi Evgeny,

    Yeah, I understand the forum is pretty much dead. But still hoping this will help others, who still use Kayako Classic in general.
    Anyways, I followed up your SQL commands. Two of them give out errors:

    delete from swusernotedata where usernotid not in (select usernoteid from swusernotes)
    Error: #1054 - Onbekende kolom 'usernotid' in IN/ALL/ANY subquery


    delete from swusers where userid in (select userid from swusers where lastvisit < unix_timestamp('2010-01-01 00:00:00') AND lastvisit2 < unix_timestamp('2010-01-01 00:00:00') AND lastactivity < unix_timestamp('2010-01-01 00:00:00') AND dateline < unix_timestamp('2010-01-01 00:00:00'))

    Error: #1093 - Table 'swusers' is specified twice, both as a target for 'DELETE' and as a separate source for data

    As you can see I am first testing it with 2010 instead of 2015.


    //edit

    Since the last one didn't work and the users are still there I used the following method:
    select userid from swusers where lastvisit < unix_timestamp('2010-01-01 00:00:00') AND lastvisit2 < unix_timestamp('2010-01-01 00:00:00') AND lastactivity < unix_timestamp('2010-01-01 00:00:00') AND dateline < unix_timestamp('2010-01-01 00:00:00')

    Then I get a list of several users and selected them in phpMyAdmin manually and deleted them. Would have the same effect right?
     
  10. Evgeny Kuchkov

    Evgeny Kuchkov Established Member

    last one should be
    delete from swusers where where lastvisit < unix_timestamp('2015-01-01 00:00:00') AND lastvisit2 < unix_timestamp('2015-01-01 00:00:00') AND lastactivity < unix_timestamp('2015-01-01 00:00:00') AND dateline < unix_timestamp('2015-01-01 00:00:00')

    but yes. Since you get the list of userids' for removal you can delete from manually with phpMyAdmin from other tables
     
  11. Gurpreet Singh

    Gurpreet Singh Staff Member

Share This Page