An efficient way to track user login dates and IPs

问题:

I'm trying to track user login history for stat purposes but its not clear to me what the best way to go about it would be. I could have a separate table that records users and their login stats with a date, but that table could get REALLY big. I could track some historic fields in the User model/object itself in a parse-able field and just update it (them) with some delimited string format. e.g. split on :, get the last one, if an included date code isn't today, add an item (date+count) otherwise increment, then save it back. At least with this second approach it would be easy to remove old items (e.g. only keep 30 days of daily logins, or IPs), as a separate table would require a task to delete old records.

I'm a big fan of instant changes. Tasks are useful, but can complicate things for maintenance reasons.

Anyone have any suggestions? I don't have an external data caching solution up or anything yet. Any pointers are also welcome! (I've been hunting for similar questions and answers)

Thanks!

回答1:

If you have the :trackable module, I found this the easiest way. In the User model (or whichever model you're authenticating)

  def update_tracked_fields!(request)
    old_signin = self.last_sign_in_at
    super
    if self.last_sign_in_at != old_signin
      Audit.create :user => self, :action => "login", :ip => self.last_sign_in_ip
    end
  end

(Inspired by https://github.com/plataformatec/devise/wiki/How-To:-Turn-off-trackable-for-admin-users)

回答2:

There is a nice way to do that through Devise.

Warden sets up a hook called after_set_user that runs after setting a user. So, supposed you have a model Login containing an ip field, a logged_in_at field and user_id field, you can only create the record using this fields.

Warden::Manager.after_set_user :except => :fetch do |record, warden, options|
  Login.create!(:ip => warden.request.ip, :logged_in_at => Time.now, :user_id => record.id)
end

回答3:

Building upon @user208769's answer, the core Devise::Models::Trackable#update_tracked_fields! method now calls a helper method named update_tracked_fields prior to saving. That means you can use ActiveRecord::Dirty helpers to make it a little simpler:

def update_tracked_fields(request)
  super

  if last_sign_in_at_changed?
    Audit.create(user: self, action: 'login', ip: last_sign_in_ip)
  end
end

This can be simplified even further (and be more reliable given validations) if audits is a relationship on your model:

def update_tracked_fields(request)
  super
  audits.build(action: 'login', ip: last_sign_in_ip) if last_sign_in_at_changed?
end

回答4:

Devise supports tracking the last signed in date and the last signed in ip address with it's :trackable module. By adding this module to your user model, and then also adding the correct fields to your database, which are:

:sign_in_count,      :type => Integer, :default => 0
:current_sign_in_at, :type => Time
:last_sign_in_at,    :type => Time
:current_sign_in_ip, :type => String
:last_sign_in_ip,    :type => String

You could then override the Devise::SessionsController and it's create action to then save the :last_sign_in_at and :last_sign_in_ip to a separate table in a before_create callback. You should then be able to keep them as long you would like.

回答5:

Here's an example (scribd_analytics)

create_table 'page_views' do |t|
  t.column 'user_id', :integer 
  t.column 'request_url', :string, :limit => 200
  t.column 'session', :string, :limit => 32
  t.column 'ip_address', :string, :limit => 16
  t.column 'referer', :string, :limit => 200
  t.column 'user_agent', :string, :limit => 200
  t.column 'created_at', :timestamp
end

Add a whole bunch of indexes, depending on queries

Create a PageView on every request

We used a hand-built SQL query to take out the ActiveRecord overhead on this

Might try MySQL's 'insert delayed´

Analytics queries are usually hand-coded SQL

Use 'explain select´ to make sure MySQL isusing the indexes you expect

Scales pretty well

BUT analytics queries expensive, can clog upmain DB server

Our solution:

use two DB servers in a master/slave setup

move all the analytics queries to the slave

http://www.scribd.com/doc/49575/Scaling-Rails-Presentation-From-Scribd-Launch

---

Another option to check is Gattica with Google Analytics

回答6:

I hate answering my own questions, especially given that you both gave helpful answers. I think answering my question with the approach I took might help others, in combination with your answers.

I've been playing with the Impressionist Gem (the only useful page view Gem since the abandoned RailStat) with good results so far. After setting up the basic migration, I found that the expected usage follows Rail's MVC design very closely. If you add "impressionist" to a Controller, it will go looking for the Model when logging the page view to the database. You can modify this behaviour or just call impressionist yourself in your Controller (or anywhere really) if you're like me and happen to be testing it out on a Controller that doesn't have a Model.

Anyways, I got it working with Devise to track successful logins by overriding the Devise::SessionsController and just calling the impressionist method for the @current_member: (don't forget to check if it's nil! on failed login)

class TestSessionController < Devise::SessionsController
  def create
    if not @current_member.nil?
      impressionist(@current_member)
    end
    super
  end
end

Adding it to other site parts later for some limited analytics is easy to do. The only other thing I had to do was update my routes to use the new TestSessionController for the Devise login route:

post 'login' => 'test_session#create', :as => :member_session

Devise works like normal without having to modify Devise in anyway, and my impressionist DB table is indexed and logging logins. I'll just need a rake task later to trim it weekly or so.

Now I just need to work out how to chart daily logins without having to write a bunch of looping, dirty queries...

回答7:

There is also 'paper_trail' gem, that allows to track model changes.