How to empty customer doctype activity log?

[pvanthony]

I am try to create a test site without the customer data. Managed to do much of it.
Checkout the following link.

Anonymize user and customer data on test VM

We cloned a live erpnext with real customer data. We would like to anonymize the user and customer data so that we can truly us it for testing. Does anyone have a script to anonymize the customer and user data? Or is there some bench option to do anonymize customer data?

The last part of the puzzle is to delete the activity log at the bottom of the customer doctype because it has customer data.

Does anyone know how to do it? Like some sql script or even a python script.

[peterg]

Hi there,

The activity log synthesizes a number of different doctypes, so it depends on what type of activity you’re trying to remove. Most activity types have a corresponding doctype, often with a link, though things like Comment docs work more behind the scenes.

If you really need to make sure there’s no customer data, though, I hope you’re doing a proper sql dump audit. You’ll need to make sure your also removing things like logs, deleted docs, etc. I would expect traces in many different locations, some of which won’t be obvious top-down.

[pvanthony]

Thank you for the reply and advice.

Just to clarify, I am talking about the activity lines that appear at the bottom of the customer doctype page.

Like in the image below.

The area that I have circled is what I want to remove.

Is it very difficult?

[peterg]

Yep, that’s the activity log I’m talking about.

The two events you circled here are stored in the created_by and modified_by fields of the customer doctype. Other things will be stored in other places. There’s not one place where it’s all stored. The answer to your question will depend on the specifics of what you’re trying to remove.

[pvanthony]

Thank you very much for the reply. I was hoping for this to be easy but looks like it is not that easy.

I need to remove the following activity.

• change in email address
• change in mobile number
• created_by
• modified_by

Hope there is table that I can remove the rows of data in one go for all the customer doctype.

[peterg]

Changes are logged in the Versions doctype, but unfortunately I think that’s the least of your problems here.

If your Customers are also Users, as seems to be the case, it’s going to be very complicated to do what you want to do. User activity is logged extensively throughout the system, and the User document identifier field is the email address.

I don’t think you’re going to find a reliable way to anonymize live data without a lot of manual work.

[pvanthony]

Once again, thank you very much for replying and for the advice.

I have already de-linked the user from all the customer doctype. Then deleted the users.

Here are the steps I have already done. I hope that will get rid of the customer/user emails address.

Steps done so far.

@pvanthony

Just updating what I have done so far on erpnext 15.

Login as admininstrator.
Go to customer doctype.
Then select all customers in the list view.
Click on action button and select edit
Select the field “user” and for the data leave it blank.

This deletes the user doctype link to the customer doctype.

then go to user doctype
filter “User Type” with website user
Then select all customers in the list view
Delete all the users

Next go back to customer doctype
select all the customers in the list view
Click on action button and select edit
Select the field “Customer mobile” and for the data leave it blank
next select the field “Customer email” and for the data leave it blank.

Did I miss anything?

[peterg]

@pvanthony

I hope that will get rid of the customer/user emails address.

It depends on how thorough you need to be. Do you need to remove references in the Email Queue? Activity Logs? OAuth Bearer Tokens? Comment links?

There are dozens, if not a hundred+, different places that user ids might be logged.

@pvanthony

Did I miss anything?

Definitely, but it’s hard to provide a comprehensive list of what. The only way I can think of to figure that out is to manually inspect an sql dump.

[pvanthony]

ok. up hill task. But I am learning a lot.

One last question, do you know where the “You created this. 1 month ago” in the activity log, is stored?

I ask this because I deleted everything from the “Version List” but the “You created this” is still in the activity log.

I am referencing to the image I sent earlier.

[peterg]

I’m not at my computer to check at the moment, but I’m pretty sure that’s the created_by field in the document itself.

[pvanthony]

You are correct. It was in the tabCustomer table.

Here is the sql used to change the “You created this” activity log.
UPDATE tabCustomer SET owner = 'Guest' WHERE owner != 'Guest';

Unfortunatey cannot get rid of the customer name. I think it is the primary key.

I think I have got the emails and mobile numbers out.

I will dig further.

[pvanthony]

Just like mentioned by @peterg there are more tables with email and mobile numbers.

tabContact
tabContact Email
tabContact Phone
tabDefaultValue
tabEmail Queue
tabEmail Receipient
tabPayment Entry
tabPayment Entry Reference
tabPayment Ledger Entry
tabPayment Schedule
tabSales Invoice
tabSales Invoice Item
tabSales Order
tabSales Order Item
tabSales Taxes and Charges

Please note these are for my setup. It may not be for your setup. So please check.

[pvanthony]

To do the database data search I used this wonderful open source software call DbGate from https://dbgate.io