Suricata IDS/IPS integration with Mikrotik (now with OSSEC)

[mlpaul]

@rapiertg

@tomfisk

@mlpaul

Hey guys, i am stuck on the whole Database Trigger part. Whenever i try to add the trigger, I get a syntax error. I tried to add it to the triggers using phpMyAdmin, but still i got the same error.

I am really eager to try this as it seems fairly interesting,

Thanks!
error.JPG

Does my reply from Mon Feb 27, 2017 10:44 am help?

Try with changing delimiter:

DELIMITER ;;
CREATE TRIGGER `after_iphdr_insert` AFTER INSERT ON `iphdr`
  FOR EACH ROW BEGIN
  DECLARE this_event INT(11) default 0;
  DECLARE this_event_signature INT(10) default 0;
  DECLARE this_event_timestamp TIMESTAMP;
  DECLARE this_sig INT(10) default 0;
  DECLARE this_sig_name VARCHAR(256) default "";
  DECLARE this_sig_gid INT(10) default 0;
  DECLARE timeout VARCHAR(12) default "";
  DECLARE interested INT default 0;
  DECLARE direction VARCHAR(3) default "";
  DECLARE ip_src VARCHAR(64) default "";
  DECLARE ip_dst VARCHAR(64) default "";
  SELECT event.id, event.signature, event.timestamp
  INTO this_event, this_event_signature, this_event_timestamp
  FROM event
  WHERE event.sid = NEW.sid and event.cid = NEW.cid;  
  SELECT signature.sig_sid, signature.sig_gid, signature.sig_name 
  INTO this_sig, this_sig_gid, this_sig_name
  FROM signature
  WHERE signature.sig_id = this_event_signature;
  SELECT count(*), sigs_to_block.src_or_dst, sigs_to_block.timeout
  INTO interested, direction, timeout
  FROM sigs_to_block
  WHERE this_sig_name LIKE CONCAT(sigs_to_block.sig_name, '%');
  IF (interested > 0) THEN
   IF (direction = "src") THEN
      INSERT INTO block_queue
   SET que_ip_adr = inet_ntoa(NEW.ip_src),
          que_timeout = timeout,
          que_sig_name = this_sig_name,
          que_sig_gid = this_sig_gid,
          que_sig_sid = this_sig,
          que_event_timestamp = this_event_timestamp;
    ELSE
      INSERT INTO block_queue
   SET que_ip_adr = inet_ntoa(NEW.ip_dst),
          que_timeout = timeout,
          que_sig_name = this_sig_name,
          que_sig_gid = this_sig_gid,
          que_sig_sid = this_sig,
          que_event_timestamp = this_event_timestamp;
    END IF;
  END IF;
END;;
DELIMITER ;

Thanks for the reply! I tried this and got a new error,

ERROR 1054 (42S22) at line 2: Unknown column 'sid' in 'NEW'

Also tomfisk, i tried adding it straight to the triggers tab in phpMyAdmin, but i get the same error that is presented on the command line. I think it may have something to do with the iphdr table. Is there anything specific i have to do with that to get it to work?

Thanks guys!