Tool to easily remove visitors


#1

Hello !

Having some visitors I’d like to easily remove made me looking for a so called plugin for Piwik or some alternative to do this.

Since I have searched in vain, here is my solution:
This removes all entries refering to an IP address, alternatively, one could alter the code in such a way, a single visitor is removed (c.f. Piwik CP: Visitors -> Visitor Log, click ‘user profile’ on the right in any row, see visitor profile ID is ‘idvisitor’)


<?
 /*
  
   == Easily remove any IP and all referring entries == 
   
  Do database connection, assuming mysqli with $con as link/handler
    
  by < jamjam > presented @forum.piwik.org ['Tool to easily remove visitors']  
  .2014 Sep 19.
  
 */
?>

<title>Piwik Clean</title>
<h1>Piwik Clean</h1>

<?

$ip = "";
if (isset($_POST['su_pc'])) {
	$ip = trim($_POST['ip']);
}

?>

<form name="pc" method="post">
<fieldset>
<p>IP: <input type="text" name="ip" placeholder="<?
echo htmlout($_SERVER["REMOTE_ADDR"]);
?>" value="<?
echo htmlout($ip);
?>" /> </p>
<p><input type="submit" name="su_pc" value="remove this" /> </p>
</fieldset>
</form>

<?

if (empty($ip)) {
	echo "You must provide IP address.";
	exit();
}

if (!filter_var($ip, FILTER_VALIDATE_IP)) {
	echo "This IP is not valid address.";
	exit();
}

// dechex(ip2long($ip)) == bin2hex(inet_pton($ip)) , for IPv4 addresses

$iph = strtoupper(bin2hex(inet_pton($ip)));

echo "<code><b>ip:</b> " . $ip . "</code><br/>\r\n";
echo "<code><b>hex: </b> " . $iph . "</code><br/>\r\n";

// TABLE : "piwik_log_visit"

$result = mysqli_query($con, "SELECT * FROM `piwik_log_visit` WHERE hex(location_ip) = '" . $iph . "'");
if (!$result) {
	echo ("<b>SELECT Error</b> " . mysqli_error($con) . "\r\n");
	exit();
}

$nrow = mysqli_num_rows($result);
echo "<p><b>[piwik_log_visit] Total:</b> " . $nrow . "</p>\r\n";

echo "\r\n<table border=1><tr>\r\n";
echo "<th>#</th>\r\n";
echo "<th>idvisit</th>\r\n";
echo "<th>idvisitor</th>\r\n";
echo "<th>visit_last_action_time</th>\r\n";
echo "</tr>\r\n\r\n";

$n          = 0;
$visitorids = array();
while ($row = mysqli_fetch_assoc($result)) {
	$vid = $row['idvisit'];
	$vis = strtoupper(bin2hex($row['idvisitor']));
	$vtm = $row['visit_last_action_time'];
	
	echo "<tr>\r\n";
	echo "<td>" . $n . "</td>\r\n";
	echo "<td>" . $vid . "</td>\r\n";
	echo "<td>" . $vis . "</td>\r\n";
	echo "<td>" . $vtm . "</td>\r\n";
	echo "</tr>\r\n";
	
	$visitorids[] = $vis;
	
	$n++;
}

echo "</table>\r\n";


// TABLE : "piwik_log_link_visit_action"

echo "<p><b>[piwik_log_link_visit_action]</b></p>\r\n";

echo "\r\n<table border=1><tr>\r\n";
echo "<th>#</th>\r\n";
echo "<th>got</th>\r\n";
echo "</tr>\r\n\r\n";

$n = 0;
foreach ($visitorids as $visitorid) {
	
	$result = mysqli_query($con, "SELECT * FROM `piwik_log_link_visit_action` WHERE hex(idvisitor) = '" . $visitorid . "'");
	if (!$result) {
		echo ("<b>SELECT Error</b> " . mysqli_error($con) . "\r\n");
		exit();
	}
	
	$got = mysqli_num_rows($result);
	
	echo "<tr>\r\n";
	echo "<td>" . $n . "</td>\r\n";
	echo "<td>" . $got . "</td>\r\n";
	echo "</tr>\r\n";
	
	$result = mysqli_query($con, "DELETE FROM `piwik_log_link_visit_action` WHERE hex(idvisitor) = '" . $visitorid . "'");
	if (!$result) {
		echo ("<b>DELETE Error</b> " . mysqli_error($con) . "\r\n");
		exit();
	}
	$affrow = mysqli_affected_rows($con);
	if ($affrow != $got) {
		echo ("<b>check mismatch (affected,got)</b> (" . $affrow . "/" . $got . ")\r\n");
		exit();
	}
	
	$n++;
}
echo "</table>\r\n";
echo "<p>OK</p>\r\n";

// remove user entries in first table
echo "Delete . ";
$result = mysqli_query($con, "DELETE FROM `piwik_log_visit` WHERE hex(location_ip) = '" . $iph . "'");
if (!$result) {
	echo ("<b>DELETE Error</b> " . mysqli_error($con) . "\r\n");
	exit();
}
$affrow = mysqli_affected_rows($con);
if ($affrow != $nrow) {
	echo ("<b>check mismatch (affected,got)</b> (" . $affrow . "/" . $nrow . ")\r\n");
	exit();
}
echo " OK<br/><br/>\r\n";

// delete archive
echo "Delete archive . ";
$result = mysqli_query($con, "SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) AS statement FROM information_schema.tables WHERE table_name LIKE 'piwik_archive_%';");
if (!$result) {
	echo ("<b>SELECT Error</b> " . mysqli_error($con) . "\r\n");
	exit();
}
$res = mysqli_fetch_object($result)->statement;
if (empty($res)) {
	echo ("<b>skip:statement</b>\r\n");
} else {
	echo "<p>Statement: <code>" . htmlout($res) . "</code></p>\r\n";
	$result = mysqli_query($con, $res);
	if (!$result) {
		echo ("<b>QRY Statement Error</b> " . mysqli_error($con) . "\r\n");
		exit();
	}
}

echo "<p><b>All fine.</b></p>";

/* close connection */
mysqli_close($con);

?>

+note: htmlout() is something like a wrapper for a function handling like htmlentities() , can be replaced with it as of simplification

Regards,
jamjam :wink:


#2

May I suggest a dive into Introduction: Develop - Matomo Analytics (formerly Piwik Analytics) - Developer Docs - v3 ?

:slight_smile:

Dali


#3

I am looking for a way to remove all entries from an IP range from the logs of one website. Is there a relatively easy way to do this? Can this script help me with this?


#4

You’d need to change the selecting + deleting of IPs, this is line 58 and 139. Maybe you’d use a mysql LIKE statement or I’d rather recommend to ask this in a PHP forum.