1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'General XenForo Discussion and Feedback' started by Stuart Wright, May 6, 2015.

  1. Stuart Wright

    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?
     
  2. Chris D

    Chris D XenForo Developer Staff Member

    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: May 6, 2015
  3. Chris D

    Chris D XenForo Developer Staff Member

    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.png

    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.
     
  4. Stuart Wright

    Stuart Wright Well-Known Member

    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.
     
    thumped likes this.
  5. ForestForTrees

    ForestForTrees Well-Known Member

    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.
     
    Stuart Wright likes this.
  6. Stuart Wright

    Stuart Wright Well-Known Member

    Thanks. I'm familiar with SQL.
     
    Joeychgo and ForestForTrees like this.
  7. ENF

    ENF Well-Known Member

    @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.
     
    Stuart Wright likes this.
  8. Chris D

    Chris D XenForo Developer Staff Member

    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');
    }
     
    Neal and ENF like this.
  9. ENF

    ENF Well-Known Member

    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!
     
  10. Stuart Wright

    Stuart Wright Well-Known Member

    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.
     
    ENF and Neal like this.
  11. Xon

    Xon Well-Known Member

    Any chance this could be rolled into the next version of XF?
     
    Mert, bill78, tajhay and 1 other person like this.
  12. Chris D

    Chris D XenForo Developer Staff Member

    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.
     
  13. ENF

    ENF Well-Known Member

    One less mod to manage, thanks for that.
     
    StarArmy likes this.

Share This Page