Installation docs should probably cover that the MariaDB default of binlog_format=statement won't allow the archive prune to run. I changed mine to binlog_format=row and that seems to have worked.

For posterity and google searches, the error received is something like this:
MariaDB [ETS_echofish]> CALL eproc_rotate_archive();
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

There are settings that define how long to keep archived logs around, however, it doesn't seem to actually clean up the old data. I'd like to look into this further, but don't see where the cleanup is actually supposed to happen. Where is this supposed to occur (or is it not yet implemented)?

Upon pulling master now Echofish wouldn't work before I installed php-mbstring. Might be worth adding a PHP dependencies section since mbstring isn't part of at least the Ubuntu default php install.

First of all, i really like the concept of echofish. Hard to find anything similar that simple and lightweight.

I set up a Docker container using debian:8.10, mariadb 10.0.32, syslog-ng 3.5.6, nginx/php-fpm using supervisord and everything works fine except the Abuser Triggers.

• mariadb event_scheduler is ON,
• the Status for all events is ENABLED,
• Whitelisting works,
• Archives get truncated

the syslog-ng destination definition is

destination d_mysql {
        sql(
                type(mysql)
                host("127.0.0.1")
                username("echofish")
                password("***")
                database("ETS_echofish")
                table("archive_bh") 
                columns("host", "facility", "priority", "level", "program", "pid", "tag", "msg", "received_ts" )
                values("$HOST", "$FACILITY_NUM", "$PRIORITY","$LEVEL_NUM", "$PROGRAM", "$PID", "$TAG", "$MSG", "$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC" )
        );
};

Tried all kinds of trigger variants, even set msg to %, and pattern to /.+/, nothing gets triggered.

I am a bit lost now on how and where i should continue to test for mistakes/bugs/errors.

Besides that:

• In the "Create AbuserTrigger"-Form, Facility and Severity field-description mention wildcards but the form blocks that with a "must be a number"-Error.
• On the Logs-Page (Home/Syslog/Logs) if chosen to display "All" and then switching to Archive, especially with 7 days of Archive, the page hangs. I think Results display on the Archive page should not be able to display All, or at least reset from the Logs-Page.

Hello,

I created a user who does not have administrative rights.

When I connect with this user, I can change other accounts and settings.

How to prevent this?

Thanks

Dear All,

I have a a 650 mo archive_bh table. Apparently the table has been created with innodb storage engine instead of blackhole.

This engine does not seem to be compiled in mysql server shipped with CentOS / RHEL 6.5

Can i truncate it safely ?

Best regards

Dear Pantelis next time, think twice before allowing me to abuse issue tracker ;)

On another echofish loghost server i get the following error in /var/log/messages:
telling
"Jun 18 07:40:01 LOGOHST rsyslogd: db error (1062): Duplicate entry 'XXXXXXXX' for key 'PRIMARY'"

I think i get it because i am manually and regularly purging my archive table :
show create event evt_truncate_archive_table \G
*************************** 1. row ***************************
Event: evt_truncate_archive_table
sql_mode:
< .....>
Create Event: CREATE EVENT evt_truncate_archive_table ON SCHEDULE EVERY 1 WEEK STARTS '2014-05-26 09:21:08' ON COMPLETION NOT PRESERVE ENABLE DO truncate table archive
<....>

Any pointers to solve this issue ?

Best regards

Hello,
I use a rsyslog server and i have multiple source log.
Manege Host don't work correctly for me.
Resolve Host do nothing.
When a new log server comes up, in the manage host i have 0.0.0.0 value for ipoctet.
Host Ip in Syslog Tabs use ipoctet values.
If i would modify ipoctet with 192.168.0.100 it's impossible because limited to 10 char.
I change the lenght field to 16 in database and php files.
I wish change triggers in archive_bh table for new host to write in ip.host table but it's an int value and they have a problem with IP format.

Geckeon.

2017/02/07 16:10:53 [error] [system.db.CDbCommand] CDbCommand::execute() failed: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'crashplan-crashplan.phrenos-\x0A\x00\x03\xD0' for key 'host_details'. The SQL statement executed was: UPDATE host SET id=:yp0, ip=:yp1, fqdn=:yp2, short=:yp3, description=:yp4 WHERE host.id='22'.

A container I'm logging changed ip addresses due to redeployment which created a new entry in the Hosts table. Unfortunately attempting to resolve the new entry results in a generic error message.

The new resolved entry in question after I deleted the old Hosts entry:
2 10.0.3.208 crashplan.phrenos crashplan Get Host by Address entry auto-generated by model at 1486476730

For the specific issue, a better error message would be appreciated, something along the lines of "Conflict when processing host ID 22"

The larger issue is trating ip address as the hosts identifying key. I'd say the fqdn should be the unique id of a host and the ip address ephemeral. The fqdn can be populated with the ip until it's resolved. Now the old entries for the host are disconnected from the new entries, just because the ip address changed.

I tried to test Truncate Archive but when I use it, rsyslog can't write in database
db error (1062): Duplicate entry '202' for key 'PRIMARY' (log error).
To resolve the problem, I had to truncate syslog and archive_unparse table.
Truncate remove auto-increment and it's a probleme in the other table, i think it's prefereble to use 'DELETE FROM archive;' to purge this table.
Geckeon.

Hello

When I click " Resolve Hosts" in the "Manage Hosts" menu.

A 500 error occurs
"WebUser and its behaviors do not have a method or closure named "addFlash".

What can I do?

Thanks

On the Logs-Page (Home/Syslog/Logs) if chosen to display "All" and then switching to Archive, especially with 7 days of Archive, the page hangs. I think Results display on the Archive page should not be able to display All, or at least reset from the Logs-Page.

Copied from issue #89 as reported by @bitroost

I see syslog suggested error 1305 FUNCTION ETS_echofish.inet6_ntoa does not exist. The SQL statement was was: SELECT t. *, Inet6_ntoa (host.ip) as hostip FROM syslog t LE Will this inet6_ntoa function how come

Background:
I have an LXC container that runs an rsyslog receiver and pushes to SQL for echolog. All hosts logging into it up til now have been containers on the same host node (10.0.3.0/24) or the hostnode itself. Now I'm adding however other physical devices that log to the same loghost such as network gear. These hosts live on 10.0.10.0/24 and are routed by their default gw (10.0.10.1) to the 10.0.3.0/24 network where the loghost lives.

What's wrong:
Any messages coming outside the loghosts network segment get attributed for the router passing on the message, i.e. in my example to 10.0.10.1. Since echofish examples show using fromhost-ip, from the perspective of rsyslog this is Working as Intended, quoting from their property reference:

hostname | hostname from the message
source | alias for HOSTNAME
fromhost | hostname of the system the message was received from (in a relay chain, this is the system immediately in front of us and not necessarily the original sender). This is a DNS-resolved name, except if that is not possible or DNS resolution has been disabled.
fromhost-ip | The same as fromhost, but alsways as an IP address. Local inputs (like imklog) use 127.0.0.1 in this property.

Am I SOL since Echofish seems to need fromhost-ip instead of hostname? I could give my loghost a second ip address on the physical device's network and do DNS trickery to make sure they're accessing non-routed, but are there any other options?

I see #17 touches on this issue somewhat but what ever was implemented there doesn't mitigate this one.

Dear all,

I just noticed the following settings:

archive_activated yes
archive_delete_days 7
archive_delete_limit 0
archive_delete_use_mem no
archive_rotate yes
whitelist_archived no

I have already played with the first. I have some clues about the second, but the other requires some clarifications.

Best regards

After upgrade, filters are visible on interface, but not taken into account.

select count(*) from whitelist : 65

select count(*) from whitelist_mem : 1

When troubleshooting an issue it would be nice to be able to set a filter and watch the messages come in (near) real time. i.e. have an open socket and get pushed new messages matching the filter as they come.

I get this error when trying to resolve all hosts (/index.php?r=settings/host/resolve_all)

Complete message is "
Error 500
CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'PRIMARY'"

select * from echofish.host:
+------------+---------------+---------+-------------+
| ip | fqdn | short | description |
+------------+---------------+---------+-------------+
| 0 | 192.168.10.2 | chemine | chemine |
| 2130706433 | 127.0.0.1 | chemine | chemine |
| 3232238082 | 192.168.10.2 | NULL | NULL |
| 3232238177 | 192.168.10.97 | NULL | NULL |
| 3232238179 | 192.168.10.99 | NULL | NULL |
+------------+---------------+---------+-------------+

Any idea ?

Best regards

Continuation of #46 since this is very much php7 specific.

Now resolving a single host works fine even on php7 but it seems there's some similar call left that php7 doesn't like. Trying to resolve all hosts results in the same error as before:
Error 500
Only variables should be passed by reference

For the life of me I can't seem to get a stack trace out of yii so I'll leave the details to the experts.

This is on:

• mysql Ver 15.1 Distrib 10.0.29-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
• PHP 7.0.13-0ubuntu0.16.04.1
• Ubuntu 16.04

I want to create a new host with the ip eg. "123.34.56.789". But it is not possible. The entry will cut at "123.34.56." How must write the ip?

When viewing the Logs or the Archive, you can click on a field to filter by the value of that field. However, if you want to filter further by another field, you cannot simply click on it, as it will replace the previous filter instead of adding to it. When doing this type of filtering, it makes more sense to have them be cumulative, with each click of a field adding a filter condition instead of replacing the current filter.

Hello

here come your favorite bug reporter

Got this error when trying to resolve my hosts

Best regards

Hello,
There could be made faster to open Archive ?
Because I have several millions of lines and it makes time.
It's possible to improve this or maybe create a task to delete log upper than 7 days, because truncate is steep.
There could be made possible to choose by default the numbers of online display in Syslog or Archive perhaps by using users setting. And in this setting i don't understand what is "Salt".
Thx for help.
Geckeon.

Hello, just set up a fresh install on CentOS 6, nginx, php-fpm. I'm just attempting to label my hosts at this point, however on the Manage Hosts page, when I click Resolve Hosts and confirm I want to replace all entries with their DNS values, it does not actually update any of the hosts. If I go into each host individually and use resolve host on it, it does work as expected. I'm not getting any errors in the nginx or php-fpm logs when running the bulk resolve, nor am I getting anything in the access log, suggesting no request is even being sent to the server.

The abuser parser function is using INET6_ATON to convert IPs for storage. This is all well and good, however, the database schema for abuser_incident table has the ip field as an int(10) unsigned (originally), which is not compatible with the output of INET6_ATON, which returns VARBINARY(4) or VARBINARY(16) depending on the address type. There needs to be a schema conversion for anyone upgrading.

https://mariadb.com/kb/en/mariadb/inet6_aton/

I was able to fix this by converting the ip column to VARBINARY(16) and then converting any preexisting values by running them through INET6_ATON(INET_NTOA(ip))

(Sorry for all the edits, as I was fixing this on my own install, I realized my original report wasn't entirely accurate)

When archive is desactivate in Sysconfig, the Whitelist don't work.
All log are send to syslog table.
And when i acknowledge a log, this last is drop. It's possible to pull them in archive table.

Best Regards.

As proposed by @geckeon on issue #24 it makes sense, when archive is disabled, that acknowledged logs will be moved to the archive. Perhaps introducing a configuration flag.

I had to reinstall frontend files. rsyslog setup and sql data was not altered at it was already functional.

but : whitelisting does not work. I keep having messages which should be filtered out.

i tried to delete and recreatte whitelist rule : no luck
i export rules, truncat tables whitelist and whitelist_mem and reimport rules : no luck

any pointer ?

Hello,

i had to tweak rsyslog logFormat template to fixed this message appearing in log. "updated_at" is explicitly set in template:

$template dbFormat,"INSERT INTO archive_bh (host, facility, priority, level, received_ts, program, msg,pid,tag,updated_at) VALUES ( '%fromhost-ip%', '%syslogfacility%', '%syslogpriority%','%syslogseverity%', '%timereported:::date-mysql%', TRIM('%programname%'), TRIM('%msg%'),'', '%syslogtag%', '%timereported:::date-mysql%' );\n",sql

fresh install of echofish 0.4 on RHEL6.5

Unfortunately, the mysqludf_preg library causes MariaDB v10+ to crash, and there has been no maintenance done on that library in years, suggesting it will not be fixed. Building the library is also becoming more difficult no newer systems due to lack of documentation. The good news is MariaDB 10 has decent built-in PCRE support out of the box (https://mariadb.com/kb/en/mariadb/pcre/), and it seems to be significantly more efficient than the udf_preg library. It would be nice if you could ship a version of the functions which made use of MariaDB's built-in support for those that prefer to use it.

I've already adapted the IP address matching regex, as that one was quite easy. Just needed to change the two relevant lines to this.

SET ipaddr=(SELECT REGEXP_SUBSTR( msg, '/(([0-9]+)(?:\.[0-9]+){3})/' ));

I'm not sure how to approach the abuser regexes though, as they rely on udf_preg's ability to specify a capture-group which is not present in MariaDB's functionality as far as I can tell.

Steps to reproduce with current HEAD:

1. enter Syslog/Logs Msg filter that will match something, for example 'eth0'
2. observe results
3. press "ack based on filt" checkmark
4. observe results

Expected results:
entries with 'eth0' are ACK'd and not visible anymore
Actual results:
entries that matched the filter have not been ACK'd and are still visible

Doing a search, clicking select all and doing a mass acknowledge works fine.

2017/04/10 13:11:52 [error] [php] include(SimpleXMLElement.php): failed to open stream: No such file or directory (/var/www/yii/framework/yiilite.php:247)
Stack trace:
#0 /var/www/html/protected/modules/lists/controllers/WhiteController.php(282): spl_autoload_call()
#1 /var/www/yii/framework/yiilite.php(4139): WhiteController->actionExport()
#2 /var/www/yii/framework/yiilite.php(3652): CInlineAction->runWithParams()
#3 /var/www/yii/framework/yiilite.php(6895): WhiteController->runAction()
#4 /var/www/yii/framework/yiilite.php(6904): CFilterChain->run()
#5 /var/www/yii/framework/yiilite.php(4030): CAccessControlFilter->filter()
#6 /var/www/yii/framework/yiilite.php(6937): WhiteController->filterAccessControl()
#7 /var/www/yii/framework/yiilite.php(6892): CInlineFilter->filter()
#8 /var/www/yii/framework/yiilite.php(3642): CFilterChain->run()
#9 /var/www/yii/framework/yiilite.php(3627): WhiteController->runActionWithFilters()
#10 /var/www/yii/framework/yiilite.php(1761): WhiteController->run()
#11 /var/www/yii/framework/yiilite.php(1681): CWebApplication->runController()
#12 /var/www/yii/framework/yiilite.php(1202): CWebApplication->processRequest()
#13 /var/www/html/index.php(13): CWebApplication->run()
REQUEST_URI=/index.php?r=lists/white/export

The file SimpleXMLElement.php is nowhere on my system, forgotten dependency?

Clicking Host column link results in URL examples:
index.php?r=syslog/archive/admin&Syslog%5Bhostip%5D=%3D127.0.0.1
index.php?r=syslog/archive/admin&Syslog%5Bhostip%5D=%3D10.0.3.77

Which decodes to something like:
Syslog[hostip]==127.0.0.1

Perhaps with the recent changes hostip is not a valid column anymore?

I get this error after validating changes to a host.

modifications to host are ok despite the error.

The abuser help pages and forms need to be updated to reflect how the regular expressions work. Also update the examples since they still mention the old format from our lib_mysqludf_preg days.

In relation to issue #89

Hello,

Is it possible to add in "Home/syslog /logs" a column named "Hostname" ?
How do I do it?
I have attached an example file.

Thanks

I'm trying to create triggers for the abusers module. Doesn't seem to work. Don't know if I do something wrong.

Example string I'm trying to catch:
Failed password for xxxxx from a.b.c.d port 12345 ssh2

Msg:
Failed password for % from %

Pattern:
/from (.*?) port.*/

All others fields, I used default value.

Abusers are not reported in the incidents tab.

In the "Create AbuserTrigger"-Form, Facility and Severity field-description mention wildcards but the form blocks that with a "must be a number"-Error.

Copied from issue #89 as reported by @bitroost

I would love to have deepdive links from Grafana pointing towards Echofish. Grafana will generate a URL similar to this:
index.php?from=1486289218479&to=1486302980904&r=syslog/archive/admin

What would be needed from Echofish is supporting from & to to match a time range.

This issue will serve in communicating some of the things we plan for the future of Echofish. Feel free to comment with your ideas about features you would like to see in Echofish.

• Convert into Yii v2.0
• Make Echofish work with composer for dependency tracking (depends on previous)
• Make lists management more manageable and sharable (abuser trigger lists, white lists)
• Allow counters and trigger presentation for each abuser into a "unified" fashion (Single ip representation for multiple triggers for this IP)
• Allow abuser triggers to decide where to act (on archived or syslog table)
• Introduce methods to import "rules" from other tools (eg logsentry, which has exactly the same principles as echofish)
• Introduce trigger categories (security, system, etc)
• Introduce email contacts management for notifications (must be able to connect email address with more than one trigger category)
• Make visible time differences between first/last occurrence on abuser incidents
• Make sure we keep a timestamp for the last time we reseted an abuser incident counter
• Introduce DNSBL checking through MySQL with the use of UDF plugins (will help in automating some abuser operations)
• Introduce OpenBGPd add feature to MySQL with the use of UDF plugins
• Separate documentation from web pages and place them on github WiKi (This doesnt mean that we will remove them, just that we will keep them in sync)
• Add tags/labels on whitelists
• Add last access field on lists (abuser, whitelists etc). This will help in detecting outdated rules.
• Introduce statistics counter about top triggers
• Introduce view for top abusers above threshold
• Introduce json api for extraction of information from echofish (eg get list of abusers). This depends on Yii v2.0.
• Look into introducing a statsd module (UDF and Yii)
• Look into ssdeep for log message generalizations (UDF and UI management) (http://ssdeep.sourceforge.net/)
• Introduce and follow Semantic Versioning
• Introduce and maintain a proper Changelog
• Update documentation
• Introduce syslog relay sender/receiver configuration snips

Received this one via e-mail, opening this issue to better communicate and keep track of the resolution.

Hello,

I installed Echofish on an Ubuntu 16.04 server with Apache, MySQL and PHP 7.1. The install
ran smoothly and I can access the web interface without problem, no error message.

The issue is that I do not see any log entry anywhere in the application.  I checked the 
"Manage Syslog Messages" page, enabled / disabled the "Toggle live feed" button but it 
doesn't make any difference, nothing shows.

Any advice?

Thank you.

I get this error on creation of a new host.

host is created ok though with a different from the specified one.

Hello,
I think i found a bug, when i search Host IP in Syslog/Logs page and i wants to use massack nothing append. All other research Facility, Level, Program and Msg works with massack.
A question, is it possible to display short name for host ip to have is name when it use resolve host.

Geckeon.

I setuped echofish that way. My servers are forwarding logs to a remote syslog server. On that remote server, I installed echofish and this is here I add the logs into the mysql database.

On some machines, I have containers sharing a single ipv4 address (NAT). Because echofish use ip address as identifier, the logs from the host and all the containers running on one host get merged in echofish. I can't identify where a specific log is coming from.

Because of that, I think the server's hostname should be used as identifier instead of the ip address.

Echofish doesn't handle IPv6 properly at the moment. When IPv6 addresses are used, the resulting host entry has NULL for the ip, and the IPv6 address gets put into the fqdn and short fields. Since v4 is dying a long slow death, it would be nice to have v6 support.

If no filters are applied one can click on "Acknowledge filtered" accidentally and clear the entire queue with no way to revert.

I believe it would be a reasonable fail-safe to either disallow filter based ACK if no filters are defined, or to ask for confirmation "Are you sure you want to ACK everything?"

If I shift-click select multiple filters and then select filter based acknowledge, no lines are ACKd. Having only one filter allows the ACK to succeed. For testing I used Facility and Host. ACK both with only Facility or Host works fine.

I just spent several hours scratching my head why I'm not getting any more log entries. Turns out a MariaDB upgrade (Ubuntu 17.04-beta2) now got me to a version where ha_blackhole.so needs to be specifically loaded. Easy to fix once you know what the issue is! :-)

It would be nice if this error condition could be somehow surfaced in the UI so that it's clear your current setup won't function.

I just installed echofish following instructions in the INSTALL.md file (skipped the mail configuration as this is not needed for now) and upon starting it, mariadb started flooding logs with warnings.

mysqld: 150104  0:22:03 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. Statement: INSERT INTO archive_bh (host, facility, priority, level, received_ts, program, msg,pid,tag) VALUES ( '127.0.0.1', '5', '6','6', '20150104002203', TRIM('rsyslogd'), TRIM(' [origin software="rsyslogd" swVersion="8.6.0" x-pid="8021" x-info="http://www.rsyslog.com"] start'),'', 'rsyslogd:' )

mysqld: 150104  0:22:03 [Note] Suppressing warnings of type 'Statement is unsafe because it uses a system function that may return a different value on the slave.' for up to 300 seconds because of flooding
mysqld: 150104  0:22:03 [Note] Suppressing warnings of type 'Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them.' for up to 300 seconds because of flooding
mysqld: 150104  0:22:03 [Note] Suppressing warnings of type 'Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.' for up to 300 seconds because of flooding

I suddenly got this error when going to /index.php?r=syslog/logs/admin

This issue was apparently caused by mysql event "archive_parse_unparsed" being disabled (for unknown reason).

Reenabling event (ALTER EVENT e_archive_parse_unparsed ENABLE;) solved the issue.