rake snorby:setup ERROR with PostgreSQL

[hostingnuggets]

I am in the process of setting up Snorby with PostgreSQL and have added the dm-postgres-adapter to Snorby's Gemfile before running bundle install, so far so good. But the problem arises when I want to run the rake snoby:setup as you can see below:

$ bundle exec rake snorby:setup --trace RAILS_ENV=production

No time_zone specified in snorby_config.yml; detected time_zone: Europe/Paris
** Invoke snorby:setup (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute snorby:setup
** Invoke secret (first_time)
** Execute secret
327d0a6d016877075b43a3f9031e167f2599427facecb0b4131a73944d79ce9b3ec95def65be7e06addb42d8c0d314af7e800575ba17dceba30341fe71f930f8
** Invoke db:create (first_time)
** Invoke environment 
** Execute db:create
Password: 
[datamapper] Created database 'snort'
** Invoke snorby:update (first_time)
** Invoke environment 
** Execute snorby:update
** Invoke db:autoupgrade (first_time)
** Invoke environment 
** Execute db:autoupgrade
[datamapper] Finished auto_upgrade! for :default repository 'snort'
** Invoke db:seed (first_time)
** Invoke environment 
** Execute db:seed
rake aborted!
ERROR:  relation "information_schema.statistics" does not exist
LINE 2:           select * FROM information_schema.statistics 
                                ^
/usr/local/lib/ruby/gems/1.9.1/gems/dm-do-adapter-1.2.0/lib/dm-do-adapter/adapter.rb:34:in `execute_reader'
/usr/local/lib/ruby/gems/1.9.1/gems/dm-do-adapter-1.2.0/lib/dm-do-adapter/adapter.rb:34:in `block in select'
/usr/local/lib/ruby/gems/1.9.1/gems/dm-do-adapter-1.2.0/lib/dm-do-adapter/adapter.rb:276:in `with_connection'
/usr/local/lib/ruby/gems/1.9.1/gems/dm-do-adapter-1.2.0/lib/dm-do-adapter/adapter.rb:33:in `select'
/usr/local/snorby/lib/snorby/jobs/cache_helper.rb:143:in `db_select'
/usr/local/snorby/lib/snorby/jobs/cache_helper.rb:214:in `has_timestamp_index?'
/usr/local/snorby/lib/snorby/jobs/cache_helper.rb:254:in `validate_cache_indexes'
/usr/local/snorby/db/seeds.rb:107:in `<top (required)>'
/usr/local/lib/ruby/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:234:in `load'
/usr/local/lib/ruby/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:234:in `block in load'
/usr/local/lib/ruby/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:225:in `load_dependency'
/usr/local/lib/ruby/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:234:in `load'
/usr/local/lib/ruby/gems/1.9.1/gems/dm-rails-1.2.1/lib/dm-rails/railties/database.rake:55:in `block (2 levels) in <top (required)>'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:205:in `call'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:205:in `block in execute'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:200:in `each'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:200:in `execute'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:158:in `block in invoke_with_call_chain'
/usr/local/lib/ruby/1.9.1/monitor.rb:211:in `mon_synchronize'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:151:in `invoke_with_call_chain'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:144:in `invoke'
/usr/local/snorby/lib/tasks/snorby.rake:43:in `block (2 levels) in <top (required)>'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:205:in `call'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:205:in `block in execute'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:200:in `each'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:200:in `execute'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:158:in `block in invoke_with_call_chain'
/usr/local/lib/ruby/1.9.1/monitor.rb:211:in `mon_synchronize'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:151:in `invoke_with_call_chain'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:144:in `invoke'
/usr/local/snorby/lib/tasks/snorby.rake:33:in `block (2 levels) in <top (required)>'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:205:in `call'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:205:in `block in execute'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:200:in `each'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:200:in `execute'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:158:in `block in invoke_with_call_chain'
/usr/local/lib/ruby/1.9.1/monitor.rb:211:in `mon_synchronize'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:151:in `invoke_with_call_chain'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:144:in `invoke'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:112:in `invoke_task'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:90:in `block (2 levels) in top_level'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:90:in `each'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:90:in `block in top_level'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:129:in `standard_exception_handling'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:84:in `top_level'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:62:in `block in run'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:129:in `standard_exception_handling'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:59:in `run'
/usr/local/lib/ruby/gems/1.9.1/gems/rake-0.9.2/bin/rake:32:in `<top (required)>'
/usr/local/bin/rake:23:in `load'
/usr/local/bin/rake:23:in `<main>'
Tasks: TOP => db:seed

As you can see in the error message Snorby has some dependencies on MySQL's information_schema table which of course is not available in PostgreSQL.

The result is that 29 tables of 31 gets created correctly but 2 are missing. One of them missing is the aggregated_events table and as such the Snorby front-end interface does not work properly and neither does Barnyard2.

Could someone fix this? The topic of having Snorby fully compatible with PostgreSQL is now already 2-3 years old and I can't see any progress here really.

[hostingnuggets]

Hello anyone? Is this project still alive?

[drew1kun]

Hi! Could u please tell How did u add the dm-postgres-adapter to the Snorby's gemfile?

[hostingnuggets]

It is such a long time now that I can't remember how I added the dm-postgresql-adapter gem to the Gemfile but I seems that this gem is somehow already included to the Gemfile as I see the following line:

gem 'dm-postgres-adapter',         DM_VERSION

Is Snorby finally compatible with PostgreSQL ?

[drew1kun]

Seems like I've got absolutely the same issue((( And no one can tell how to make Snorby work with Postgres (((( I have no clue what's wrong with this app...

[hostingnuggets]

Yeah unfortunately since Snorby has become also commercial, they have totally ignored the issues and I suppose that they don't care much anymore about their community. Look I opened this issue 1 and a half year ago and no one as done anything. Big :-1:

[drew1kun]

yeah you are right(((( Do you actually know any postgres-compatible gui for snort and suricata?

[hostingnuggets]

unfortunately no, the thing is that snorby is nice but mysql is much to slow in comparison to postgresql with a large number of rows. it will definitely be an advantage for snorby if they support postgresql. i still have some hope that some developer is reading this...

[drew1kun]

Yeah I know( Did you try BASE?

[frconil]

I've worked on something in a fork of snorby that actually polls pg_indexes if it detects that postgresql is used as the main database (as set in the rails config).

I'll try to test this sometimes this weekend or early next week in a test vm to see if that fix the setup part.

I'll also give it a try using activerecord if that works better: http://apidock.com/rails/v3.1.0/ActiveRecord/ConnectionAdapters/SchemaStatements/index_exists%3F but that might be needlessly complex for a setup task.

Untested commit over there https://github.com/frconil/snorby/commit/f6afbf6326f08a10d9e21a1b6d411f2268cc9f80 if you want to try yourself.

[gehrhorn]

I have merge rights for Snorby. However, I don't use it any more and don't have a system to test things. I'm reluctant to merge pull requests without a way to test because I don't want to make anything worse. @miketanderson, do you still use this?

[miketanderson]

@gehrhorn I do actively use Snorby, I have a few instances available including one for testing. I have no postgressql experience or postgressql environment to test with unfortunately.

[drew1kun]

Any updates?

[frconil]

Sorry, I started looking into it and got sidetracked.

Snorby doesn't use Activerecord but DataMapper, which can set up indices: http://www.rubydoc.info/github/datamapper/dm-core/DataMapper/Property

I'll have to get my head around it and find some time to really dig into the code to see how I can make this work for postgres. It's a tad bit complex because I'm not quite sure I understand why Datamapper is used in some places and raw sql in others.

[drew1kun]

So is it still impossible to get snorby to work with postgresql?

[frconil]

Okay, I have a working fork in https://github.com/frconil/snorby (at least from the setup pov)

Can someone let me know if this also works with mysql?

[drew1kun]

@frconil If this supposed to work with Postgres?

[frconil]

yes, but I don't want my merge to break the mysql adapter.

the commit https://github.com/frconil/snorby/commit/7b9b23debcac49947ce14f230e65e36beeadf2b5 is a squash of all my changes to get it working (safely i hope) with postgres

[drew1kun]

Ok! I'll test. Sorry for being such a newbie (new to git) so should I clone your fork or do svn checkout?

[frconil]

git clone should work i could run the setup rake task without issue and it's currently running on a test vm here, just need to actually test the running instance.

[drew1kun]

I've noticed that Gemfile doesn't have a gem 'dm-postgres-adapter', DM_VERSION... Is it supposed to be like that?

[frconil]

yeah, just added that in, sorry.

[drew1kun]

Sorry for spamming(It's already late for me - and I've done stupid mistakes, lol) - everything works great with mysql - rake setup creates a database identical to the one created by original snorby version - 31 tables… But with postgres it creates database with only 29 tables(aggregated_events and events_with_join tables are missing in postgres db schema). Is it supposed to be like that?

[frconil]

I unearthed a new issue while testing my fork, turns out that setting the "id" column from inside datamapper in app/models/event.rb breaks the sensor page later on (because sensor.last returns 3 ids instead of 2)

Would appreciate some help on what the id column is for/how it is used by snorby.

[frconil]

Ok, I think I have a working fork for both mysql and postgres. If anyone wants to give it a spin with both db engines and let me know, and I'll submit a merge request.

[drew1kun]

well done some tests - still have same: 31 tables in mysql db and only 29 tables in postgres db(aggregated_events and events_with_join tables are missing in postgres db schema). Is it supposed to be like that?

[frconil]

Did you do a fresh git clone of my fork? What's the setup task trace like?

Also aggregated_events and events_with_joins are views, not tables.

[drew1kun]

Yes I did git clone of your fork.

$ bundle exec rake snorby:setup --trace
No time_zone specified in snorby_config.yml; detected time_zone: America/Vancouver
** Invoke snorby:setup (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute snorby:setup
** Invoke secret (first_time)
** Execute secret
50def74db2da712879cc84db244fb833bcfbf8eeef79e3c321393c97832686397a2433af59c957adb1d7432ef305e1355f743081d41fcebed82b509b15ba79f0
** Invoke db:create (first_time)
** Invoke environment
** Execute db:create
[datamapper] Created database 'snorby'
** Invoke snorby:update (first_time)
** Invoke environment
** Execute snorby:update
** Invoke db:autoupgrade (first_time)
** Invoke environment
** Execute db:autoupgrade
[datamapper] Finished auto_upgrade! for :default repository 'snorby'
** Invoke db:seed (first_time)
** Invoke environment
** Execute db:seed
[~] Adding `id` to the event table
[~] fixing database types for ip addresses
[~] Building aggregated_events database view
[~] Building events_with_join database view
** Invoke snorby:restart_worker (first_time)
** Invoke environment
** Execute snorby:restart_worker
* Stopping the Snorby worker process.
* Removing old jobs
* Starting the Snorby worker process.
* Adding jobs to the queue

[frconil]

Try typing "\dv" in your postgresql prompt. That should list your two missing views.

[drew1kun]

Gotcha! Thank you!!!

[Maveric79]

Hi guys!

I have same problem. I try use https://github.com/frconil/snorby but "rake snorby:setup" return error "cannot load such file -- dm-postgres-adapter". Do you have solution ?

Thank you.

[frconil]

Was there any error during the bundle install step?

[Maveric79]

No error: "Bundle complete! 66 Gemfile dependencies, 117 gems now installed. Use bundle show [gemname] to see where a bundled gem is installed"

[Maveric79]

snorby:setup output:

root@MB-Roman:/var/www/snorby# bundle exec rake snorby:setup --trace * Invoke snorby:setup (first_time) * Invoke environment (first_time) \ Execute environment rake aborted! cannot load such file -- dm-postgres-adapter /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:240:in require' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:240:inblock in require' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:223:in block in load_dependency' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:640:innew_constants_in' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:223:in load_dependency' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:240:inrequire' /var/lib/gems/1.9.1/gems/dm-core-1.2.0/lib/dm-core/adapters.rb:163:in load_adapter' /var/lib/gems/1.9.1/gems/dm-core-1.2.0/lib/dm-core/adapters.rb:133:inadapter_class' /var/lib/gems/1.9.1/gems/dm-core-1.2.0/lib/dm-core/adapters.rb:13:in new' /var/lib/gems/1.9.1/gems/dm-core-1.2.0/lib/dm-core.rb:230:insetup' /var/lib/gems/1.9.1/gems/dm-rails-1.2.1/lib/dm-rails/setup.rb:25:in setup_with_instrumentation' /var/lib/gems/1.9.1/gems/dm-rails-1.2.1/lib/dm-rails/setup.rb:12:inblock in setup' /var/lib/gems/1.9.1/gems/dm-rails-1.2.1/lib/dm-rails/setup.rb:11:in each' /var/lib/gems/1.9.1/gems/dm-rails-1.2.1/lib/dm-rails/setup.rb:11:insetup' /var/lib/gems/1.9.1/gems/dm-rails-1.2.1/lib/dm-rails/railtie.rb:90:in block in <class:Railtie>' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/lazy_load_hooks.rb:34:incall' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/lazy_load_hooks.rb:34:in execute_hook' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/lazy_load_hooks.rb:43:inblock in run_load_hooks' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/lazy_load_hooks.rb:42:in each' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/lazy_load_hooks.rb:42:inrun_load_hooks' /var/lib/gems/1.9.1/gems/railties-3.1.12/lib/rails/application/finisher.rb:56:in block in <module:Finisher>' /var/lib/gems/1.9.1/gems/railties-3.1.12/lib/rails/initializable.rb:30:ininstance_exec' /var/lib/gems/1.9.1/gems/railties-3.1.12/lib/rails/initializable.rb:30:in run' /var/lib/gems/1.9.1/gems/railties-3.1.12/lib/rails/initializable.rb:55:inblock in run_initializers' /var/lib/gems/1.9.1/gems/railties-3.1.12/lib/rails/initializable.rb:54:in each' /var/lib/gems/1.9.1/gems/railties-3.1.12/lib/rails/initializable.rb:54:inrun_initializers' /var/lib/gems/1.9.1/gems/railties-3.1.12/lib/rails/application.rb:96:in initialize!' /var/lib/gems/1.9.1/gems/railties-3.1.12/lib/rails/railtie/configurable.rb:30:inmethod_missing' /var/www/snorby/config/environment.rb:3:in <top (required)>' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:240:inrequire' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:240:in block in require' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:223:inblock in load_dependency' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:640:in new_constants_in' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:223:inload_dependency' /var/lib/gems/1.9.1/gems/activesupport-3.1.12/lib/active_support/dependencies.rb:240:in require' /var/lib/gems/1.9.1/gems/railties-3.1.12/lib/rails/application.rb:83:inrequire_environment!' /var/lib/gems/1.9.1/gems/railties-3.1.12/lib/rails/application.rb:203:in block (2 levels) in initialize_tasks' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:205:incall' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:205:in block in execute' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:200:ineach' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:200:in execute' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:158:inblock in invoke_with_call_chain' /usr/lib/ruby/1.9.1/monitor.rb:211:in mon_synchronize' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:151:ininvoke_with_call_chain' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:176:in block in invoke_prerequisites' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:174:ineach' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:174:in invoke_prerequisites' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:157:inblock in invoke_with_call_chain' /usr/lib/ruby/1.9.1/monitor.rb:211:in mon_synchronize' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:151:ininvoke_with_call_chain' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/task.rb:144:in invoke' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:112:ininvoke_task' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:90:in block (2 levels) in top_level' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:90:ineach' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:90:in block in top_level' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:129:instandard_exception_handling' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:84:in top_level' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:62:inblock in run' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:129:in standard_exception_handling' /var/lib/gems/1.9.1/gems/rake-0.9.2/lib/rake/application.rb:59:inrun' /var/lib/gems/1.9.1/gems/rake-0.9.2/bin/rake:32:in <top (required)>' /usr/local/bin/rake:23:inload' /usr/local/bin/rake:23:in `

' Tasks: TOP => snorby:setup => environment

[frconil]

Does dm-postgres-adapter show in gem list ?

[Maveric79]

Hm... dm-postgress-adapter not in gem list but i exec gem install dm-postgress-adapter: Successfully installed dm-postgres-adapter-1.2.0 1 gem installed Installing ri documentation for dm-postgres-adapter-1.2.0... Installing RDoc documentation for dm-postgres-adapter-1.2.0...

Repeat "bundle install" and... dm-postgress-adapter not in gem list again

Sorry, I new in ruby.

[Maveric79]

I add "gem 'dm-mysql-adapter', DM_VERSION" in Gemfile and this problem was solved.

[Maveric79]

Something wrong:

bundle exec rake snorby:setup:

[datamapper] Created database 'snorby' [datamapper] Finished auto_upgrade! for :default repository 'snorby' rake aborted! ERROR: relation "information_schema.statistics" does not exist LINE 2: select * FROM information_schema.statistics ^

Tasks: TOP => db:seed (See full trace by running task with --trace)

[frconil]

Are you just you cloned the last version of the fork, and that it's a clean fork?

There is no mention of information_schema in my fork: https://github.com/frconil/snorby/search?utf8=%E2%9C%93&q=information_schema

[Maveric79]

Yes. I cloned last version of the fork and its clean fork.

[Maveric79]

frconil, do you have no problems with postgresql ?

[frconil]

if you look here : https://github.com/frconil/snorby/blob/master/lib/snorby/jobs/cache_helper.rb

You can see I do not use iformation_schema.statistics at all. So I'm not sure where your problem is coming from.

[Maveric79]

Oooops.... It's my mistake You are the best!

[hostingnuggets]

@frconil I am using your fork of Snorby with PostgreSQL but I noticed that the daily reports are not working because the SQL queries for that purpose have not been modified for PostgreSQL. For example the daily reports want to run the following SQL query:

SELECT "signature" FROM "event" GROUP BY "signature" ORDER BY "timestamp" DESC LIMIT 5

which of course does not work the PostgreSQL as you can see here:

snort=> SELECT "signature" FROM "event" GROUP BY "signature" ORDER BY "timestamp" DESC LIMIT 5;
ERROR:  column "event.timestamp" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...ature" FROM "event" GROUP BY "signature" ORDER BY "timestamp... 

Could you also adapt these SQL queries for PostgreSQL?

Cheers!

[frconil]

Hi @hostingnuggets, just to let you know i'm aware of this. A bit time constrained at the moment, but i'll definitely try to have a look at this!

[gehrhorn]

If there a working copy of this @frconil? If you have something that works and doesn't break the backwards compatibility @miketanderson or I can merge a pull request.

[frconil]

I submitted a PR a few months ago, not sure if it's been accepted or not. I tried to test as much as I could but obviously some slipped through the cracks so I'd understand about not merging it (mostly worried about breaking mysql compatibility as well)

The master branch of my fork https://github.com/frconil/snorby should have the latest of my work.

I'm sorry to admit I haven't been able to work much on it since starting a new job, but it's definitely on my radar.

[gehrhorn]

OK, in that case I'll wait. If someone gets together a pull request that enables PostGres and doesn't break MySQL I'll merge it.

[frconil]

To the best of my knowledge and my tests it doesn't break mysql, but if there are a suite of tests to run I'd be more than happy to run them against a test instance if that assists.

I tried as much as possible to make my changes platform agnostic (replace 0/1 with false/true, etc), and include them in switch cases when this wasn't possible.

[hostingnuggets]

@frconil thanks for your fast response, let me know when I can test this with PostgreSQL. Right now I have disabled the reports/notifications as workaround, if I don't do that my delayed_jobs process simple core dumps.