SensorCacheJob issue

[therock11]

I have been stuck unable to get past events from months ago in the sensor cache job. Is there a way to force it past the date and only read new events without deleting them from the snortdb? The following is how far back I am.

New Day: 2011-03-21T14:00:00-04:00 - 2011-03-21T14:30:00-04:00
Sensor 1: 20624 events found. Processing.

[therock11]

This is the stop error I get from the process

New Day: 2011-03-21T16:00:00-04:00 - 2011-03-21T16:30:00-04:00
Sensor 1: 14031 events found. Processing.

Processing Batch 1 of 2...
Sensor 1: - fetching sensor metrics
Sensor 1: - building proto counts
Sensor 1: - fetch_event_count
Sensor 1: - fetching tcp count
Sensor 1: - fetching udp count
Sensor 1: - fetching icmp count
Sensor 1: - fetching severity metrics
Sensor 1: - fetching src ip metrics
undefined method `ip_src' for nil:NilClass
=> nil

[cventers]

Have a look at bug #102. I had the same problem you did until I started running vineyard's SQL query from a cron job. The dashboard SensorCache job still crashes on me from time to time, so I had to come up with a cron job and shell script to restart it periodically.

#!/bin/sh

. /usr/local/rvm/environments/default

exec $GEM_HOME/bin/rails r 'Snorby::Jobs.start_all!'

I had to add this to lib/snorby/jobs.rb to get that to work:

    def self.start_all!
      Snorby::Jobs.sensor_cache.destroy! if Snorby::Jobs.sensor_cache?
      Delayed::Job.enqueue(Snorby::Jobs::SensorCacheJob.new(false), :priority => 1)
      Snorby::Jobs.daily_cache.destroy! if Snorby::Jobs.daily_cache?
      Delayed::Job.enqueue(Snorby::Jobs::DailyCacheJob.new(false), :priority => 1, :run_at => Time.now.tomorrow.beginning_of_day)
      Snorby::Jobs.geoip_update.destroy! if Snorby::Jobs.geoip_update?
      Delayed::Job.enqueue(Snorby::Jobs::GeoipUpdatedbJob.new(false), :priority => 1, :run_at => 1.week.from_now.beginning_of_day)
    end

I eagerly await a real bugfix!

[ermalm]

@therock11 I've had that problem a lot. Here is my solution:

First, log on to mysql:

mysql -p

Type in your database password, then type (in this case, my database is "snorby"):

mysql> use snorby;

After you've done that, type in the following, line by line:

delete from iphdr where ip_src = 0 or ip_dst = 0;
delete from caches where (sid, cid) not in (select iphdr.sid as sid, iphdr.cid as cid from iphdr);
delete from daily_caches where (sid, cid) not in (select iphdr.sid as sid, iphdr.cid as cid from iphdr);    
delete from data where (sid, cid) not in (select iphdr.sid as sid, iphdr.cid as cid from iphdr);
delete from event where (sid, cid) not in (select iphdr.sid as sid, iphdr.cid as cid from iphdr);
delete from favorites where (sid, cid) not in (select iphdr.sid as sid, iphdr.cid as cid from iphdr);
delete from notes where (sid, cid) not in (select iphdr.sid as sid, iphdr.cid as cid from iphdr);
delete from opt where (sid, cid) not in (select iphdr.sid as sid, iphdr.cid as cid from iphdr);
delete from icmphdr where (sid, cid) not in (select iphdr.sid as sid, iphdr.cid as cid from iphdr);
delete from tcphdr where (sid, cid) not in (select iphdr.sid as sid, iphdr.cid as cid from iphdr);
delete from udphdr where (sid, cid) not in (select iphdr.sid as sid, iphdr.cid as cid from iphdr);

Then just exit the mysql prompt.

Now it's time to go in to the Rails prompt:

rails c

Then type in the following:

> Snorby::Jobs::SensorCacheJob.new(true).perform 

After it's done, type in this:

> Snorby::Jobs::DailyCacheJob.new(true).perform 

If you didn't get any errors, your dashboard should now be working with graphs and everything.

[djcas9]

@therock11 @cventers @ermalm This issue should be resolved in Snorby 2.3.12 - we now ignore nil columns in the cache jobs. Let me know if this fixes the problem.