These SQL commands should allow to delete a Customer in not valid state Someone can 'confirm? -- 1) delete from service_customer_user where customer_user_login in (select login from customer_user where valid_id=2) -- 2) delete from customer_preferences where user_id in (select login from customer_user where valid_id=2) -- 3) delete from group_customer_user where user_id in (select login from customer_user where valid_id=2) -- 4) delete from customer_user where valid_id=2 By Rocco Il 21/02/2013 12.12, Rafa Quiñonero Figuerola ha scritto:

All that things must be done from the database, you can delete tickets, users, partners, services... from database. But pay attention to don´t delete something you need!!

2013/2/21 Jignesh Kakka (jkakka) mailto:jkakka@cisco.com>

Hi Team,

As of now we only have an option to make customer invalid by setting Valid=0.

Do we have any option to permanently delete the customer? If yes, then is are similar way for Services also?

Thanks, Jignesh _______________________________________________ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev

-- Rafa Quiñonero Figuerola Tlf: 0151-61436385 rafakorp@gmail.com mailto:rafakorp@gmail.com

_______________________________________________ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev

-- Rocco Tocci Application Architect Strada Prov. 1 BariModugno n. 4 70123 Bari Italy Tel. +39 0803855483 TBS IT -- Telematic & Biomedical Services Srl a socio unico Sede Legale : Via Giacinto Gallina, 4 -- 34122 Trieste Tel. 040 - 92291 Sede Operativa : Via Francesco Bettini, 13 -- 06034 Foligno Tel. 0742- 32661 C.F., P.IVA e Registro Imprese (TS) : 01165260322 Cap.Soc. EUR 8.000.000,00 i.v. una società di TBS Group SpA Ai sensi del D.Lgs. 196/2003 e della normativa internazionale, vi informiamo che questo messaggio è riservato esclusivamente al destinatario. Se esso vi fosse pervenuto per errore, vi invitiamo a eliminarlo immediatamente e a darcene comunicazione. Grazie. According to the Italian and international laws in force, we inform you that this message is reserved only for the addressee. If you are not the addressee, please delete it immediately and notify us. Thank you. P Per favore, pensa all'ambiente prima di stampare questo messaggio. Please, consider the environment before printing this e-mail.

As of now we only have an option to make customer invalid by setting Valid=0. Do we have any option to permanently delete the customer? If yes, then is are similar way for Services also? This is not a good idea. What happens when you go back to an old ticket, and OTRS no longer has the customer data to populate the display? Bad Things. Removing these data points breaks referential integrity in the database, which is important for proper operation.

Hi Martin, I don't understand the second step: 2. **Look for all tickets of invalid customers and set a dummy user. Why do you need to set a dummy user to that tickets if they are already deleted? Thanks a lot Rafa 2013/2/22 Zeiler, Martin

Hello,****

** **

because of the data privacy act we have to delete customers after they left university.****

We want to go live with otrs soon. I think there are two options to delete customers:****

** **

**1. **Look for all tickets of invalid customers and delete them. Then delete customers in database.****

**2. **Look for all tickets of invalid customers and set a dummy user. ****

In both options you have to take care of other otrs modules, e.g. linked CI’s.****

** **

Best regards****

** **

Martin Zeiler****

** **

*Von:* dev-bounces@otrs.org [mailto:dev-bounces@otrs.org] *Im Auftrag von *David Boyes *Gesendet:* Donnerstag, 21. Februar 2013 16:45 *An:* Development community of OTRS

*Betreff:* Re: [dev] How to Delete Customer?****

** **

As of now we only have an option to make customer invalid by setting Valid=0.

Do we have any option to permanently delete the customer? If yes, then is are similar way for Services also?****

This is not a good idea. What happens when you go back to an old ticket, and OTRS no longer has the customer data to populate the display? Bad Things. ****

Removing these data points breaks referential integrity in the database, which is important for proper operation. ****

_______________________________________________ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev

-- Rafa Quiñonero Figuerola Tlf: 0151-61436385 rafakorp@gmail.com

Ok, I thought they were 2 different steps, not 2 different options 2013/2/22 Zeiler, Martin

Hi Rafa,****

** **

this point is one of two options I’m thinking about.****

** **

Best regards****

** **

Martin****

** **

*Von:* dev-bounces@otrs.org [mailto:dev-bounces@otrs.org] *Im Auftrag von *Rafa Quiñonero Figuerola *Gesendet:* Freitag, 22. Februar 2013 10:24

*An:* Development community of OTRS *Betreff:* Re: [dev] How to Delete Customer?****

** **

Hi Martin, I don't understand the second step:****

** **

2. Look for all tickets of invalid customers and set a dummy user.** **

** **

Why do you need to set a dummy user to that tickets if they are already deleted? ****

** **

Thanks a lot****

** **

Rafa****

2013/2/22 Zeiler, Martin ****

Hello,****

****

because of the data privacy act we have to delete customers after they left university.****

We want to go live with otrs soon. I think there are two options to delete customers:****

****

1. Look for all tickets of invalid customers and delete them. Then delete customers in database.****

2. Look for all tickets of invalid customers and set a dummy user. * ***

In both options you have to take care of other otrs modules, e.g. linked CI’s.****

****

Best regards****

****

Martin Zeiler****

****

*Von:* dev-bounces@otrs.org [mailto:dev-bounces@otrs.org] *Im Auftrag von *David Boyes *Gesendet:* Donnerstag, 21. Februar 2013 16:45 *An:* Development community of OTRS****

*Betreff:* Re: [dev] How to Delete Customer?****

****

** **

As of now we only have an option to make customer invalid by setting Valid=0.

Do we have any option to permanently delete the customer? If yes, then is are similar way for Services also?****

This is not a good idea. What happens when you go back to an old ticket, and OTRS no longer has the customer data to populate the display? Bad Things. ****

Removing these data points breaks referential integrity in the database, which is important for proper operation. ****

_______________________________________________ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev****

****

** **

-- Rafa Quiñonero Figuerola Tlf: 0151-61436385 rafakorp@gmail.com ****

_______________________________________________ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev

-- Rafa Quiñonero Figuerola Tlf: 0151-61436385 rafakorp@gmail.com

Hi all, David Boyes schrieb am 21.02.2013 16:45:

As of now we only have an option to make customer invalid by setting Valid=0.

Do we have any option to permanently delete the customer?

David is right, there is no need and no good reason to risk the integrity of the database by simply deleting the records. If you need to comply with a data privacy act, why not simply blank the fields with personal data but leave the record itself in place? Have you tried something like UPDATE `customer_user` SET `last_name`=`` WHERE `valid`=0; ? (SQL untested; you have to add the other fields) (please do a backup before playing with the database) If you blank or NULL the respective fields there would be no personal data left and legal aspects would be o.k. Greets, Henning

Hi Martin, Zeiler, Martin schrieb am 22.02.2013 13:53:

yes, you are right. This would be the easiest way. But I'm not very happy with this, because every year 10,000 students are leaving our university and all would produce empty entries.

I'm not very familiar with databases. Will we run into performance problems, if there are so many new entries every year?

TBH I can't seriously tell. Usually a MySQL table can hold a million records before things become slower, a table with 90.000 x 8 nulled fields (9 years nulled students) shouldn't be too slow. Otherwise you might want to write a little script updating all database references from the deleted users to a single dummy user for this case? You will have to gather in depths database knowledge about OTRS for this case. What is about OTRS built in archiving mechanisms? I haven't read into this. Regards, Henning

Hi all, thanks a lot for your replies. Yes, my process for deleting customers includes deleting tickets too. I hope, I can do this in GenericAgent. Because of personal information in tickets I have to delete them after the agreed period of time. And yes, there is a separate machine for the database. Best regards Martin

-----Ursprüngliche Nachricht----- Von: dev-bounces@otrs.org [mailto:dev-bounces@otrs.org] Im Auftrag von David Boyes Gesendet: Freitag, 22. Februar 2013 17:22 An: Development community of OTRS Betreff: Re: [dev] How to Delete Customer?

...

yes, you are right. This would be the easiest way. But I'm not very happy with this, because every year 10,000 students are leaving our university and all would produce empty entries. I'm not very familiar with databases. Will we run into performance problems, if there are so many new entries every year?

It's more of a function of how many tickets they generate, not how many users (the user table is tiny compared to the ticket transactions, and judicious addition of indexes for commonly used tables like "users" will help a lot).

Overall performance will depend on how well your database servers are configured. If you want to be totally safe, make sure that the database server is not on the same host as the OTRS server, and that it is clustered (even if you start with only one database server; if you configure it that way, you can horizontally scale it if you need to w/o taking an outage). Cluster and/or replication for both MySQL/MariaDB and Postgres are very stable, and not terribly hard to configure.

...

...

If you need to comply with a data privacy act, why not simply blank the fields with personal data but leave the record itself in place? [snip] If you blank or NULL the respective fields there would be no personal data left and legal aspects would be o.k.

Blanking would work too. I like to replace it with Deleted User to flag to an agent that this is an archival record that doesn't connect to a real user. Note that none of these solutions (including deleting the customer record) will catch references or names/personal info included in ticket transactions. If you have to scrub that hard, then you'll have to totally purge their tickets and CIs as well. At that point, you *could* delete the customer record, because there wouldn't be any dangling references. That will lose a lot of useful historical data, though.

_______________________________________________ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev