How to Delete Users with NO tickets

[PaulMacFarlane]

As a result of SPAM, I have lots of Users (50+pages) that are not real users.

I have deleted the spam tickets as they have come in.

Is there a way to delete all users that have NO tickets?

Thanks,
Paul

[Jamie Edwards]

Hi Paul,

I am unsure - but I am inclined to think it will have to involve a script to execute a lookup query of tickets created for each user in your database, and then a query to delete the user entry if no tickets exist for that user.

You may find the ERD diagram in the help/support folder of your package useful (telling you what fields link within tables).

[PaulMacFarlane]

Thanks. I figured I could do that but I've not used scripting in MySql.

I'll give it a try though...

[craigbrass]

Would be a nice feature tho Jamie. Maybe move it to feature requests.

[PaulMacFarlane]

Yes . something that could be put on the CRON to run every night....

Just part of the spam battle...!

[craigbrass]

Putting it on a cron would be a good idea too. In this case there should be a manual page you can go to that would clear the users out as well as the ability to enable it on Kayako's Crons which could then be added to a cron job. Certainly needs moving to feature requests as this would be a nice feature.

[bear]

Since Kayako has the ability to make the KB visible only to logged in users, is'nt is possible that some of the users without tickets could be actual clients that just haven't needed help since the KB you have is just that good?

I think a better way to handle this is a way to mark something as spam when deleting, and that Kayako removes the relevant email/user in the process of deleting the spam ticket. Adding a zero-ticket delete to the cron will quite likely catch real users.

[craigbrass]

I see your point but this is an optional process. The admin has the ability to do it manually or by enabling the cron. They are not forced to do it.

[bear]

I realize it's optional (and didn't say it was mandatory, actually), but if the ability is there, it's possible some users unfamiliar with the script's operation will enable it, and delete legitimate users. Then they come here and complain how "Kayako deleted my users!".

[craigbrass]

A nice red warning at the top should do the trick me thinks.

[PaulMacFarlane]

I actually like Bears suggestion. Add SPAM along with Delete where SPAM deletes the ticket and the user record....

[hbouma]

The real problem here is how eSupport handles user registrations over the web compared to user registrations over email. With web based registrations, eSupport lets you add a CAPTCHA stage to make sure you're a human person signing up and then lets you add a verification step (user or staff based) to make sure the email address is valid. eSupport then has a cron job to delete any user who doesn't respond to the verification emails. This system is effective at reducing the number of

However, when it comes to email based registrations, there are no additional safeguards eSupport offers to make sure the user is valid. Any email that makes it into the queue, you're golden as far as the helpdesk is concerned. It will will register the sender as a validated registered user into the system.

This design doesn't make any sense. It is like building a wall around your castle to keep the Mongolian Horde out but only around the front half of it. But you're still left with the back half open to anyone who wants to come in and make a mess.

At a minimum, what eSupport should do is give the option for the new user to be created as non-validated when an email comes in from the unknown user. After all, why should a new email user automatically become validated and registered? This way if they don't become validated (i.e. its a spammer and the spam ticket is deleted), they'll get purged out the next time the system removes unvalidated registrations. Kayako could also modify the cron job so that non-validated users with no tickets get purged instead of just just being non-validated. Any non-validated users who have tickets that are being handled won't get automatically deleted - i.e. sales tickets.

As for validating the email initiated registration, if a staff member replies to the ticket rather than deletes it, that could validate the user. Or probably easier is to have eSupport send the standard registration verification email to the new user asking them to validate themselves for the help desk.

Implementing something like this would help filter out the bogus registered users eSupport is generating right now from the spam mails.

Hal

[supportskins]

As for validating the email initiated registration, if a staff member replies to the ticket rather than deletes it, that could validate the user.

I feel this is a great suggestion if Kayako could implement it!

Or probably easier is to have eSupport send the standard registration verification email to the new user asking them to validate themselves for the help desk.

I believe this feature is already available in the existing Kayako build. When you set the email queue, it allows you to accept incoming emails only from "Registered" users. If the user is not registered, it does not accept the incoming email rather it can be configured to send out an autoresponder asking the User to register first.

[hbouma]

Originally Posted by supportskins

I believe this feature is already available in the existing Kayako build. When you set the email queue, it allows you to accept incoming emails only from "Registered" users. If the user is not registered, it does not accept the incoming email rather it can be configured to send out an autoresponder asking the User to register first.

Yes, the feature is there for this, but this suggestion isn't about forcing everyone to register via the web, its about improving the current system for registering over email. Right now its an open door system with no restrictions in place. Also, there are two problems with forcing web based registrations:

1) The user now has to go through extra steps to register themselves with the help desk before they can resubmit their ticket (as the first one got rejected by the help desk). If they're in a hurry (need a server rebooted), the added delay can really add to their frustration. Plus they may get turned off to the idea of having to register to get support. This is a lot of work when the help desk already has everything it needs to initiate the registration process.

2) The user may not be in a position to use the web but are instead able to send email. They would not be able to register on the help desk in order to submit a ticket.

Hal