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.
     

Share This Page