The police want to know how the IP address is recorded for members

Stuart Wright

Well-known member
So one of our members who scammed other members is being prosecuted and the police have asked me when the IP address and timestamp information seen against the member is recorded.

When we view the list of a member's IP addresses, is it a list of unique IPs for that member recorded when they start a new session when logged in?
 
IPs are logged:
  • When a user logs in (front end/back end)
  • When a user registers
  • When a user confirms their user account
  • When a user resets their password
  • When various content is created
  • When some content is edited
  • When a user upgrades XenForo
Important thing to remember about the first one: that's when a user goes from being logged out, to being logged in, e.g. specifically when a log in has occurred rather than being logged in via a cookie.

The IP addresses you see will be from a number of these events. They are ordered by the date they were logged in descending order. When you view them, they are grouped by IP address, so if someone does 20 things using the same IP address, you will only see it once (though it will be logged 20 times in the DB).
 
Last edited:
By the way, this query will display the IP addresses "normally":

Code:
SELECT ip_id, user_id, content_type,
    content_id, action, log_date,
    INET_NTOA(CONV(HEX(ip), 16, 10)) AS ip
FROM xf_ip

Don't run that query as is, on your board ;) You'll want to limit that by user ID at least...

But, this will give you a more verbose view of what the user has been doing and when.

upload_2015-5-6_17-26-56.webp

Oh, and also to note that the query will only return an IPv4 address - if they're using IPv6 addresses the syntax will need to be different.
 
Thanks, Chris.
For the purposes of reporting illegal activity, it would be useful to be able to see some kind of full report on activity based on time and IP address. I appreciate it could certainly be a big report, but the police require more information sometimes.
 
For the purposes of reporting illegal activity, it would be useful to be able to see some kind of full report on activity based on time and IP address. I appreciate it could certainly be a big report, but the police require more information sometimes.
Would just running the SQL query work for you? SQL has a huge amount of flexibility built in.

I don't know what database you used, but on my MySQL system, things were pretty straightforward. As you may know, PHPMyAdmin makes it easy to run arbitrary SQL and has export capabilities. For small reports, I was able to run the following from the command line:
Code:
mysql -u abc_test --password --database=abc_testxf --execute='\
SELECT ip_id, user_id, content_type, \
    content_id, action, log_date, \
    INET_NTOA(CONV(HEX(ip), 16, 10)) AS ip \
FROM xf_ip \
WHERE user_id = '1' \
'> file.txt
Obviously your forum is huge, but MySQL is obviously a beast and bash should be fine for moderately sized reports. I made a 6 MB report this way (by deleting the "Where user_id ='1'" line) and my tiny VPS didn't even blink.

If report file size is an issue, then (at least for a MySQL database), the "SELECT ... INTO OUTFILE" syntax could work.

If the issue is in constructing the actual SQL query, if you write down precisely the data that you want to search for, someone might be willing to help.
 
IPs are logged:
  • When a user logs in (front end/back end)
  • When a user registers
  • When a user confirms their user account
  • When a user resets their password
  • When various content is created
  • When some content is edited
  • When a user upgrades XenForo
Important thing to remember about the first one: that's when a user goes from being logged out, to being logged in, e.g. specifically when a log in has occurred rather than being logged in via a cookie.

@Chris D
Sorry to bring this thread up again, but it's relevant to my confirmation of this IP logging.

So, if a user visits the site and is already authenticated a session by a cookie, the IP of the access will *not* be logged?

If that's the case when an IP isn't logged, could an addon be written to log that user activity no matter what?

We just need to be able to record the user IP each time the site is accessed with an authenticated session. This may increase our data storage a bit, but it's becoming a requirement to be able to pinpoint when an IP address is used to access the site and be able to tie that back to a specific user account.
 
So, if a user visits the site and is already authenticated a session by a cookie, the IP of the access will *not* be logged?
Pretty much.

I think perhaps the ideal part to extend would be the "userLogin" method in the "XenForo_Session" class. This method is called when a user is successfully logged in via a cookie. I would recommend having an add-on created (or creating one yourself, this is a fairly trivial thing to add if you're comfortable with PHP), however if you were to feel more comfortable editing the file and then re-applying that after each upgrade, this is what the code looks like by default:
PHP:
public function userLogin($userId, $passwordDate)
{
   $this->changeUserId($userId);
   if ($passwordDate)
   {
      $this->set('password_date', $passwordDate);
   }
}

And all you'd need to do is change it to:
PHP:
public function userLogin($userId, $passwordDate)
{
   $this->changeUserId($userId);
   if ($passwordDate)
   {
      $this->set('password_date', $passwordDate);
   }
   XenForo_Model_Ip::log($userId, 'user', $userId, 'session_login');
}
 
As always, super helpful Chris. Thanks so much. I think I can handle that with ease. ;)

Cheers, have a great Friday. Mine is ending on a good note!
 
This is probably one for the suggestions forum, but what would be *most* useful for us when tracking the activities of fraudsters would be a graphical representation of their activity in a calendar showing when they have visited the site and all activity while visiting. Rather like items in Google Calendar. Being able to add several users would also be useful so that we can visually compare the activity of suspected duplicate accounts.
 
Pretty much.

I think perhaps the ideal part to extend would be the "userLogin" method in the "XenForo_Session" class. This method is called when a user is successfully logged in via a cookie. I would recommend having an add-on created (or creating one yourself, this is a fairly trivial thing to add if you're comfortable with PHP), however if you were to feel more comfortable editing the file and then re-applying that after each upgrade, this is what the code looks like by default:
PHP:
public function userLogin($userId, $passwordDate)
{
   $this->changeUserId($userId);
   if ($passwordDate)
   {
      $this->set('password_date', $passwordDate);
   }
}

And all you'd need to do is change it to:
PHP:
public function userLogin($userId, $passwordDate)
{
   $this->changeUserId($userId);
   if ($passwordDate)
   {
      $this->set('password_date', $passwordDate);
   }
   XenForo_Model_Ip::log($userId, 'user', $userId, 'session_login');
}
Any chance this could be rolled into the next version of XF?
 
Yeah we've just committed that, though a slightly different implementation.

We've actually moved it to be explicitly when the public session starts. Where it was before would have triggered it during registration and other things (essentially double logging the IP address for a newly registered user), so this moves it to be explicitly when a user is logged in via their remember cookie (after 2FA):

PHP:
if ($allowLogin)
{
   $userModel->setUserRememberCookie($user['user_id']);
   $session->userLogin($user['user_id'], $user['password_date']);
   XenForo_Model_Ip::log($userId, 'user', $userId, 'cookie_login');
}

We've also named it 'cookie_login' to make that more clear.

This will be in XF 1.5.8.
 
By the way, this query will display the IP addresses "normally":

Code:
SELECT ip_id, user_id, content_type,
    content_id, action, log_date,
    INET_NTOA(CONV(HEX(ip), 16, 10)) AS ip
FROM xf_ip

Don't run that query as is, on your board ;) You'll want to limit that by user ID at least...

But, this will give you a more verbose view of what the user has been doing and when.

View attachment 105537

Oh, and also to note that the query will only return an IPv4 address - if they're using IPv6 addresses the syntax will need to be different.
Hey Chris I am not the most amazing with SQL queries but how I would do the same but dump IPv6 ones as well? I would do a separate query for each. This one worked great for IPv4 by itself.
 
Code:
SELECT ip_id, user_id, content_type,
    content_id, action, log_date,
    INET6_NTOA(ip) AS ip
FROM xf_ip

Should work... I've used this to pull IPv6 from the DB successfully.
(could be a better way to do it though! ;) )
 
Last edited:
Top Bottom