PiwikCleaning

Deleting data from PIWIK tables

1. Identifying Suspicious IPs

create a temporary table group by IP action count

CREATE TEMPORARY TABLE IPCOUNT as (select location_ip, count(1) as c FROM piwik_log_visit v, piwik_log_link_visit_action a WHERE v.idvisit = a.idvisit AND v.idsite=4 GROUP BY location_ip);

SELECT inet_ntoa(conv(hex(location_ip), 16, 10)) AS ip, c FROM IPCOUNT ORDER BY c;

This will give the list of ip address with most amount of actions performed overall according to idsite where 2 is website views and 4 is for downloads

Suspicious, having more actions in 1 visit

SELECT inet_ntoa(conv(hex(location_ip), 16, 10)) AS ip, idvisit, conv(hex(idvisitor), 16, 10) AS visitorId, visit_first_action_time, location_country, visit_total_actions FROM piwik_log_visit WHERE visit_total_actions > 100 AND idsite=4;

Get detail information of a particular IP actions

SELECT inet_ntoa(conv(hex(location_ip), 16, 10)) AS ip, idvisit, conv(hex(idvisitor), 16, 10) AS visitorId, visit_first_action_time, location_country, visit_total_actions, referer_url FROM piwik_log_visit WHERE inet_ntoa(conv(hex(location_ip), 16, 10))='0.0.0.0';

2. Deleting the records from the database

Delete by visitorid

DELETE FROM piwik_log_link_visit_action WHERE conv(hex(idvisitor), 16, 10) = '5156074757056607084';

DELETE FROM piwik_log_visit WHERE conv(hex(idvisitor), 16, 10) = '5156074757056607084';

Delete all actions by ip

DELETE FROM piwik_log_link_visit_action a JOIN piwik_log_visit v ON v.idvisit = a.idvisit WHERE v.idsite=4 AND inet_ntoa(conv(hex(location_ip), 16, 10))='0.0.0.0';

DELETE FROM piwik_log_visit WHERE v.idsite=4 AND inet_ntoa(conv(hex(location_ip), 16, 10))='0.0.0.0';

Then we have to delete the summary tables of affected dates

DROP TABLE piwik_archive_numeric_Y, piwik_archive_blob_Y;

Where Y is the date.

3. Regenerate the summary tables

From the /var/www/piwik directory execute

./console core:archive --force-date-range YYYY-MM-DD,YYYY-MM-DD