I went ahead and coded my own solution. Follow these steps to implement it:
(1) Create an account named "CheckUser" on your forum. (Note that the username is case-sensitive.)
(2) Execute the following bash commands to create a folder for your checkuser CSV files:
mdkir checkuser
chmod 755 checkuser
chown www-data:www-data checkuser
(3) Execute the following MySQL statements to create a useragents table:
CREATE TABLE mybb_useragents (
useragentid int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
uid INT unsigned,
username VARCHAR(255),
time INT unsigned,
timestamp VARCHAR(255),
ip VARCHAR(255),
xffip VARCHAR(255),
useragent VARCHAR(255)
);
CREATE INDEX mybb_uid_time ON mybb_useragents (uid, time);
CREATE INDEX mybb_useragent_time ON mybb_useragents (useragent, time);
CREATE INDEX mybb_xffip_time ON mybb_useragents (xffip, time);
(4) Add the following PHP code to the end of global.php:
// CheckUser
if ( $mybb->user['uid'] ) {
$thisMysqli = new mysqli(
$config['database']['hostname'],
$config['database']['username'],
$config['database']['password']
);
$thisMysqli->select_db( $config['database']['database'] );
// Get data about the current session
$query = 'SELECT uid,time,ip,useragent FROM '
. $config['database']['table_prefix'] . 'sessions'
. ' WHERE uid=' . "'" . $mybb->user['uid'] . "'";
$result = $thisMysqli->query( $query );
// Look up the most recent session in the useragents table
while ( $result && $row = $result->fetch_assoc() ) {
$query = 'SELECT uid,time,ip,xffip,useragent FROM '
. $config['database']['table_prefix'] . "useragents WHERE uid='" . $row['uid']
. "' ORDER BY time DESC LIMIT 1";
$userAgentRow = false;
$userAgentResult = $thisMysqli->query( $query );
if ( $userAgentResult ) {
$userAgentRow = $userAgentResult->fetch_assoc();
}
// Get the XFF IP
$row['xffip'] = '';
if ( $row['uid'] == $mybb->user['uid'] ) {
$row['xffip'] = get_the_ip();
}
// If the current session's XFF IP or useragent are different from what was most
// recently saved in the useragents table, then insert a new row into the useragents
// table with that data
if ( !$userAgentRow
|| $userAgentRow['xffip'] != $row['xffip']
|| $userAgentRow['useragent'] != $row['useragent']
) {
$epoch = $row['time'];
$dt = new DateTime("@$epoch"); // convert UNIX timestamp to PHP DateTime
// output = 2017-01-01 00:00:00
$row['timestamp'] = $dt->format('Y-m-d H:i:s');
$query = 'INSERT INTO '
. $config['database']['table_prefix'] . "useragents "
. "(uid, username, time, timestamp, ip, xffip, useragent ) VALUES ('"
. $row['uid'] . "', '"
. $mybb->user['username'] . "', '"
. $row['time'] . "', '"
. $row['timestamp'] . "', '"
. $session->ipaddress . "', '"
. $row['xffip'] . "', '"
. $thisMysqli->escape_string( $row['useragent'] ) . "')";
$thisMysqli->query( $query );
}
}
}
// If we're logged into the "CheckUser" account then dump the useragent data
// to CSV files
if ( $mybb->user['username'] === 'CheckUser' ) {
$filenames = array(
'uid,time' => 'checkuser/checkuser_by_uid_time.txt',
'useragent,time' => 'checkuser/checkuser_by_useragent_time.txt',
'xffip,time' => 'checkuser/checkuser_by_xffip_time.txt'
);
$thisMysqli = new mysqli(
$config['database']['hostname'],
$config['database']['username'],
$config['database']['password']
);
$thisMysqli->select_db( $config['database']['database'] );
$orderbys = array( 'uid,time', 'useragent,time', 'xffip,time' );
foreach( $orderbys as $orderby ) {
$query = 'SELECT uid,username,time,timestamp,ip,xffip,useragent FROM '
. $config['database']['table_prefix'] . 'useragents'
. ' ORDER BY ' . $orderby;
$userAgentResult = $thisMysqli->query( $query );
$fhandle = fopen( $filenames[$orderby], 'w' );
while ( $userAgentResult && $userAgentRow = $userAgentResult->fetch_assoc() ) {
fputcsv( $fhandle, $userAgentRow );
}
}
}
function get_the_ip() {
//Just get the headers if we can or else use the SERVER global.
if ( function_exists( 'apache_request_headers' ) ) {
$headers = apache_request_headers();
} else {
$headers = $_SERVER;
}
//Get the forwarded IP if it exists.
if ( array_key_exists( 'X-Forwarded-For', $headers ) && filter_var( $headers['X-Forwarded-For'], FILTER_VALIDATE_IP, FILTER_FLAG_IPV4 ) ) {
$the_ip = $headers['X-Forwarded-For'];
} elseif ( array_key_exists( 'HTTP_X_FORWARDED_FOR', $headers ) && filter_var( $headers['HTTP_X_FORWARDED_FOR'], FILTER_VALIDATE_IP, FILTER_FLAG_IPV4 ) ) {
$the_ip = $headers['HTTP_X_FORWARDED_FOR'];
} else {
$the_ip = filter_var( $_SERVER['REMOTE_ADDR'], FILTER_VALIDATE_IP, FILTER_FLAG_IPV4 );
}
return $the_ip;
}
(5) Whenever you want to generate CSV files, login to your CheckUser account. It will generate three CSV files using data from the useragents table:
checkuser/checkuser_by_uid_time.txt
checkuser/checkuser_by_useragent_time.txt
checkuser/checkuser_by_xffip_time.txt
Then you just navigate there using your browser and download them. Note that nothing has been done to ensure the security of your users' IP and useragent data from being downloaded by others. This is just a proof-of-concept to show how data could be gathered. You would want to develop some other features to actually secure that data before using this in production.