I would like to parse log files from Cisco WRVS4400N router to a specific database for detecting a DoS Attack. Any idea please?
So first it would be good to consider what kind of database you'd like to use, and then secondly what kind of adapter you can use with it.
Regarding databases and log storage, the main factor is about storage - the storage model defines what it will be like, and these models you can categorize like this:
- Unstructured text files (smallest size, fastest ingest)
- Structured data (medium size, medium fast ingest)
- Structured data with indexes (big size, slow ingest)
- Structured data with indexes and full text search (very big size, slowest ingest)
So, based on that you can do the following:
- Use BigData storage with Hadoop like processing. This is over-kill for the task.
- SQL tables without indexes, MongoDB without indexes
- SQL tables with indexes, MongoDB with indexes
- SQL tables with index and with full text search, MongoDB with indexes and full text search, Elastic Search
SQL and MongoDB are both most user friendly due to availability of various GUIs and SDKs which are easy to use (as of 2016). There are also other engines like Cassandra, Splunk.
So now, from my experience, the best usability / scalability / easy to use is to use MongoDB table with primary index and eventually some optional when it's needed. This is because MongoDB with it's newest WiredTiger engine it compressed data so typical log entry from Apache takes approx 90 bytes of space on disk (with indexes). Compared to uncompressed SQL it is very good result, and secondly it allows you to define called collections (tables), which can be limited to e.g. 4GB which gives you 50M log entries. So then, after hitting 50M entries, it will be overwriting old ones. Another advantage of MongoDB is that you can ingest JSON Document without need to define table structure, it just takes any JSON document.
Comparing e.g. other SQL engines, there's problem with removing old entries and sometimes the indexes has to be rebuilt (there's no way without at least primary index). This makes it lot more complex in a way that this data needs to be inserted into new table every day, so implementation of it is a lot harder.
Regarding Search Engines, the search functionality is really not needed. This is very good marketing but very little benefit for a lot of hassle when it comes to log parsing. In fact, searching is for manual operation and once you perform search of two it happens to be not needed any more, yet all data storage and ingest is designed for searching.
What is the best usage of such data is to e.g. run Nagios check (as good example), and storage the values in RRD table and generate graph with pnp4nagios. So each check run every 5 minutes checks last 5 minutes worth of logs and based on that take data point and ingest into Nagios.
For Elastic Search for example, there's front-end for viewing data but it's not reporting tool, for reporting on e.g. httpd logs you can use Sawmill instead.
Finally, you need adapter which can do one of the following: - Accept SNMP traps - Accept Syslog messages - Accept JSON formatted documents over HTTP/HTTPS (usually it is using keep alives)
There are few options to do it like using Syslog with Database plugin, so basically logs are passed to Syslog and Syslog is loading it to database. But then, you need to also construct SNMP trap or any kind of sink separately. There are few other commercial sinks for these tasks optimized for Splunk, there's something with Greylog2 as well.
However what seems to be easy to implement is Spring XD, so basically you can establish Syslog source and JDBC (SQL) / JSON/ MongoDB output. It is very easy to do and once running it should be OK. You do not need any extra code to have it up and running. You can then use Syslog as source from servers and from router as well. You need to find out which Syslog format is good, and you can start many Syslog listeners with many Syslog formats. So this should work fine for router.
Now, as SQL for example would seem to be easy option, when there's more entries like HTTP etc, MongoDB performs better, and also if Syslog seems easy as well, it may not be good for large number of requests. Therefore for medium-size installations (like if you are not Facebook or any big website), MongoDB is good because it's easy with GUI, API, capped collections and small storage / RAM requirements.
Then it comes how do you ingest HTTPD logfiles into MongoDB. For the integration sake it's good to use Adapter as well and some kind of universal one, for example HTTPS JSON -> MongoDB or SQL, and then make server on which HTTPD is running to tail the logs and ingest send it to the Adaptor as JSON documents so there's field name and field value. So when you change log format then it's no pain to change the database schema or even queries.
So this can be achieved with daemon which tails the httpd log and sends HTTPS / JSON documents to Adaptor. For example many people are comfortable with Perl but most recently with Python. This is not really difficult think to do. The Apache log format is stored in it's config, so from this you can work-out the log format and it's columns. Sometimes there's log field description in the header. The daemon is good option because it doesn;'t interfere with anything else, but making Apache writing to Syslog also works, however you need structured documents or SQL table data and not single line.
The above methods are highly reliable and scalable and you are not in any vendor lock-in. For example, Nagios is good monitoring tool and since it doesn't realy on any database itself it's very hard to break it while it's very easy to install / revert any changes as you can have them in Git. And it can do both rendering and alerting like email notifications. Plus, you have various Front-ends like Thruk which is nicely packaged for many distributions.
In overall, the above gives you the lowest cost, smallest infrastructure requirements and good flexibility. Going with any search engine is huge over-kill and it will not make it any easier even if the start looks easy. Being familiar with database APIs other than SQL is apparently needed, some bit of scripting and the Nagios. These are standards and this can scale to a lot of more devices. It's easy to make Nagios monitoring bandwidth and so on. It's not by default, Nagios plugins do miss big time plenty of things but it's easy to make it happen with a bit of SNMP experience.
Search engines are not good for being used by Nagios. They are useful for used by people. So if you want alerting it's better to use something else than Search Engines. It is just huge over-kill for the task and you might find yourself working on something which in the end has little benefit even if you have believed in it.
What you really need is:
- Proper reporting like Sawmill, and Search Engines with their front-ends are nowhere close to it today
- Proper alerting with Nagios (it's not as good or not at all with other alternatives)
- Stable, mature database with scale-out options which works
- RRD based drawings, the round-robin in MongoDB and RRD in Nagios is what it is all about
- Proper front-end to Nagios like Thruk
- Good database client like Mongo3T, SQLYog for MySQL etc
- Good scripting language with good environment (definitely not tons of Regex).