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

[aarango]

@tomfisk

@aarango

A add trigger deleting old trigger, I think that its works ,but I discovered a new thing:

If I do from myqsl console set inet_ntoa(NEW.ip_src) → I get real IP

But MK is adding the number without decipher.

Any idea?

Thanks

So if you do this in MySQL:

use snorby;
Select * from block_queue;

What do you see in the que_ip_adr column? Should be real IP address. If it is still encoded IP address then trigger is not executing correctly as trigger should insert record with real IP address.

If you don’t see real IP address then do this in MySQL:

show triggers;

And check to make sure that trigger code is correct. If not, you might still have the old trigger executing.

Hi, finally its working fine. I dropped old tables and add it again and its showing real IP on Mikrotik.

A last question, I see a lot of rules on emerging-, how could I test one? I see some rules with an address.
Example:alert http $HOME_NET any → $EXTERNAL_NET any (msg:“ET MALWARE Win32/Hadsruda!bit Adware/PUA Installation Activity”; flow:to_server,established; content:“GET”; http_method; content:“?alpha=”; http_uri; content:“User-Agent|3a 20|NSIS_Inetc”; http_header; fast_pattern; pcre:"/?alpha=(?:[A-Za-z0-9/+]{4})(?:[A-Za-z0-9/+]{2}==|[A-Za-z0-9/+]{3}=|[A-Za-z0-9/+]{4})/U"; reference:md5,6b58b3eb9bbb0f7297a2e36e615506d3; classtype:trojan-activity; sid:2022850; rev:2;)

I want to access to check how MK ban that traffic. I tested with ransomware’s IP and its ok, MK ban automatically, but I can’t test it with a URL.

Thanks you for your job and support.

[tomfisk]

@aarango

@tomfisk

@aarango

A add trigger deleting old trigger, I think that its works ,but I discovered a new thing:

If I do from myqsl console set inet_ntoa(NEW.ip_src) → I get real IP

But MK is adding the number without decipher.

Any idea?

Thanks

So if you do this in MySQL:

use snorby;
Select * from block_queue;

What do you see in the que_ip_adr column? Should be real IP address. If it is still encoded IP address then trigger is not executing correctly as trigger should insert record with real IP address.

If you don’t see real IP address then do this in MySQL:

show triggers;

And check to make sure that trigger code is correct. If not, you might still have the old trigger executing.

Hi, finally its working fine. I dropped old tables and add it again and its showing real IP on Mikrotik.

A last question, I see a lot of rules on emerging-, how could I test one? I see some rules with an address.
Example:alert http $HOME_NET any → $EXTERNAL_NET any (msg:“ET MALWARE Win32/Hadsruda!bit Adware/PUA Installation Activity”; flow:to_server,established; content:“GET”; http_method; content:“?alpha=”; http_uri; content:“User-Agent|3a 20|NSIS_Inetc”; http_header; fast_pattern; pcre:"/?alpha=(?:[A-Za-z0-9/+]{4})(?:[A-Za-z0-9/+]{2}==|[A-Za-z0-9/+]{3}=|[A-Za-z0-9/+]{4})/U"; reference:md5,6b58b3eb9bbb0f7297a2e36e615506d3; classtype:trojan-activity; sid:2022850; rev:2;)

I want to access to check how MK ban that traffic. I tested with ransomware’s IP and its ok, MK ban automatically, but I can’t test it with a URL.

Thanks you for your job and support.

I don’t know that there is a way to test all of the signatures, at least not easily. If you go to section 1.5 of this document you can see how they test some of the signatures:

https://web.nsrc.org/workshops/2015/pacnog17-ws/raw-attachment/wiki/Track2Agenda/ex-suricata-config-test.htm

I would suspect that there is a way to generate the signatures on the interface, but that is beyond my level at this time.

[aarango]

@tomfisk

@aarango

@tomfisk

@aarango

A add trigger deleting old trigger, I think that its works ,but I discovered a new thing:

If I do from myqsl console set inet_ntoa(NEW.ip_src) → I get real IP

But MK is adding the number without decipher.

Any idea?

Thanks

So if you do this in MySQL:

use snorby;
Select * from block_queue;

What do you see in the que_ip_adr column? Should be real IP address. If it is still encoded IP address then trigger is not executing correctly as trigger should insert record with real IP address.

If you don’t see real IP address then do this in MySQL:

show triggers;

And check to make sure that trigger code is correct. If not, you might still have the old trigger executing.

Hi, finally its working fine. I dropped old tables and add it again and its showing real IP on Mikrotik.

A last question, I see a lot of rules on emerging-, how could I test one? I see some rules with an address.
Example:alert http $HOME_NET any → $EXTERNAL_NET any (msg:“ET MALWARE Win32/Hadsruda!bit Adware/PUA Installation Activity”; flow:to_server,established; content:“GET”; http_method; content:“?alpha=”; http_uri; content:“User-Agent|3a 20|NSIS_Inetc”; http_header; fast_pattern; pcre:"/?alpha=(?:[A-Za-z0-9/+]{4})(?:[A-Za-z0-9/+]{2}==|[A-Za-z0-9/+]{3}=|[A-Za-z0-9/+]{4})/U"; reference:md5,6b58b3eb9bbb0f7297a2e36e615506d3; classtype:trojan-activity; sid:2022850; rev:2;)

I want to access to check how MK ban that traffic. I tested with ransomware’s IP and its ok, MK ban automatically, but I can’t test it with a URL.

Thanks you for your job and support.

I don’t know that there is a way to test all of the signatures, at least not easily. If you go to section 1.5 of this document you can see how they test some of the signatures:

https://web.nsrc.org/workshops/2015/pacnog17-ws/raw-attachment/wiki/Track2Agenda/ex-suricata-config-test.htm

I would suspect that there is a way to generate the signatures on the interface, but that is beyond my level at this time.

I will look to test it.

My Mikrotik has banned my own mail IP, are there way to add a whitelist in suricata or script or Mikrotik?
Could I create a rule in first place to allow traffic to my mail address?

Thanks again.

[tomfisk]

@aarango

I will look to test it.

My Mikrotik has banned my own mail IP, are there way to add a whitelist in suricata or script or Mikrotik?
Could I create a rule in first place to allow traffic to my mail address?

Thanks again.

A couple of different ways to do this in suricata.

First of all in /etc/suricata/suricata.yaml right at the top of the file is a place to define you home network:

vars:
  # more specifc is better for alert accuracy and performance
  address-groups:
    HOME_NET: "[192.168.0.0/16,10.0.0.0/8]"
    #HOME_NET: "[192.168.0.0/16]"
    #HOME_NET: "[10.0.0.0/8]"
    #HOME_NET: "[172.16.0.0/12]"
    #HOME_NET: "any"

If that doesn’t seem to do the trick, then in /etc/suricata/threshold.config you can suppress the firing of specific signatures:

suppress gen_id 1, sig_id 2010066, track by_src, ip 192.168.100.2

Just see what signature id caused your email server to be block and add it into the threshold.config file.

[aarango]

@tomfisk

@aarango

I will look to test it.

My Mikrotik has banned my own mail IP, are there way to add a whitelist in suricata or script or Mikrotik?
Could I create a rule in first place to allow traffic to my mail address?

Thanks again.

A couple of different ways to do this in suricata.

First of all in /etc/suricata/suricata.yaml right at the top of the file is a place to define you home network:

vars:
  # more specifc is better for alert accuracy and performance
  address-groups:
    HOME_NET: "[192.168.0.0/16,10.0.0.0/8]"
    #HOME_NET: "[192.168.0.0/16]"
    #HOME_NET: "[10.0.0.0/8]"
    #HOME_NET: "[172.16.0.0/12]"
    #HOME_NET: "any"

If that doesn’t seem to do the trick, then in /etc/suricata/threshold.config you can suppress the firing of specific signatures:

suppress gen_id 1, sig_id 2010066, track by_src, ip 192.168.100.2

Just see what signature id caused your email server to be block and add it into the threshold.config file.

Hi,

I added my HOME_NET to suricata.yaml:
HOME_NET: “[192.168.0.0/16,10.0.0.0/8,192.168.5.0/24]”

And I too added a line in threshold.config to avoid that alert from my IP. Could you say me if its right?
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.100
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.1

suricata/fast.log.1:04/03/2017-09:57:09.934246 [] [1:2006380:12] ET POLICY Outgoing Basic Auth Base64 HTTP Password detected unencrypted [] [Classification: Potential Corporate Privacy Violation] [Priority: 1] {TCP} 192.168.5.1:37764 → 192.168.5.100:8088

192.168.5.100 its my own mail IP.
192.168.5.1 its my own router.

And if from my home_net someone try to upload/download/access/execute any file or bad website, will be detected by suricata or if I have my net home_net is it invisible for it?
I want to avoid attacks from external net and any possible attack from any laptop infected or access prohibited.

Thanks.

[tomfisk]

@aarango

Hi,

I added my HOME_NET to suricata.yaml:
HOME_NET: “[192.168.0.0/16,10.0.0.0/8,192.168.5.0/24]”

And I too added a line in threshold.config to avoid that alert from my IP. Could you say me if its right?
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.100
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.1

suricata/fast.log.1:04/03/2017-09:57:09.934246 [] [1:2006380:12] ET POLICY Outgoing Basic Auth Base64 HTTP Password detected unencrypted [] [Classification: Potential Corporate Privacy Violation] [Priority: 1] {TCP} 192.168.5.1:37764 → 192.168.5.100:8088

192.168.5.100 its my own mail IP.
192.168.5.1 its my own router.

And if from my home_net someone try to upload/download/access/execute any file or bad website, will be detected by suricata or if I have my net home_net is it invisible for it?
I want to avoid attacks from external net and any possible attack from any laptop infected or access prohibited.

Thanks.

Yes, your definitions look right.

Suricata will not prevent all bad things from happening. It will detect network based threats that match it’s signature database. If you have an external facing website or any other service, it will see that traffic as valid and not prohibit access.

Suricata is just one layer of security that you can implement to protect your assets from network based threats. End-point protection, ransomware protection, and host intrusion detection (e.g. OSSEC) adds more layers of security in order to try to alleviate threats. But just like physical security, if someone wants to work hard enough to get into your house, there is really not a way to stop them. The more layers of security you have, the more likely it is that the attacker will move on and look for softer targets.

[aarango]

@tomfisk

@aarango

Hi,

I added my HOME_NET to suricata.yaml:
HOME_NET: “[192.168.0.0/16,10.0.0.0/8,192.168.5.0/24]”

And I too added a line in threshold.config to avoid that alert from my IP. Could you say me if its right?
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.100
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.1

suricata/fast.log.1:04/03/2017-09:57:09.934246 [] [1:2006380:12] ET POLICY Outgoing Basic Auth Base64 HTTP Password detected unencrypted [] [Classification: Potential Corporate Privacy Violation] [Priority: 1] {TCP} 192.168.5.1:37764 → 192.168.5.100:8088

192.168.5.100 its my own mail IP.
192.168.5.1 its my own router.

And if from my home_net someone try to upload/download/access/execute any file or bad website, will be detected by suricata or if I have my net home_net is it invisible for it?
I want to avoid attacks from external net and any possible attack from any laptop infected or access prohibited.

Thanks.

Yes, your definitions look right.

Suricata will not prevent all bad things from happening. It will detect network based threats that match it’s signature database. If you have an external facing website or any other service, it will see that traffic as valid and not prohibit access.

Suricata is just one layer of security that you can implement to protect your assets from network based threats. End-point protection, ransomware protection, and host intrusion detection (e.g. OSSEC) adds more layers of security in order to try to alleviate threats. But just like physical security, if someone wants to work hard enough to get into your house, there is really not a way to stop them. The more layers of security you have, the more likely it is that the attacker will move on and look for softer targets.

A lot of thanks for your fast reply. I am checking this script and I am finding with a trouble.

Script is running good:

# ps wuaxf|grep suri
root      7789 45.3 24.0 1392900 755588 ?      Sl   07:17  54:48 /usr/bin/suricata -c /etc/suricata/suricata-debian.yaml -r -
root      7862  0.3  2.4 177752 77560 ?        Ss   07:18   0:28 barnyard2 -c /etc/suricata/barnyard2.conf -d /var/log/suricata -f unified2.alert -w /var/log/suricata/suricata.waldo -D
root     11335  0.0  0.0   8824   752 pts/0    S+   09:18   0:00      \_ grep --color=auto suri
root     11325  0.2  0.3 191204 10532 pts/0    S    09:18   0:00 php -f /opt/mikrotik-ips-suricata/suricata_block.php

And if I try to access a ransomware website (for example) automatically Mikrotik receive a new line from server with that IP, its okay and running fine.

Now, I leave from that shell, and I again enter a new ransomware’s IP (which it should be banned too because php process is running) but not, when I access this new IP, script doesn’t appear running and IP’s aren’t banned.

I enabled logs php:
error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT
log_errors = On
error_log = /var/log/php_errors.log
But log is empty.

Any idea please?

Thanks.

[tomfisk]

@aarango

@tomfisk

@aarango

Hi,

I added my HOME_NET to suricata.yaml:
HOME_NET: “[192.168.0.0/16,10.0.0.0/8,192.168.5.0/24]”

And I too added a line in threshold.config to avoid that alert from my IP. Could you say me if its right?
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.100
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.1

suricata/fast.log.1:04/03/2017-09:57:09.934246 [] [1:2006380:12] ET POLICY Outgoing Basic Auth Base64 HTTP Password detected unencrypted [] [Classification: Potential Corporate Privacy Violation] [Priority: 1] {TCP} 192.168.5.1:37764 → 192.168.5.100:8088

192.168.5.100 its my own mail IP.
192.168.5.1 its my own router.

And if from my home_net someone try to upload/download/access/execute any file or bad website, will be detected by suricata or if I have my net home_net is it invisible for it?
I want to avoid attacks from external net and any possible attack from any laptop infected or access prohibited.

Thanks.

Yes, your definitions look right.

Suricata will not prevent all bad things from happening. It will detect network based threats that match it’s signature database. If you have an external facing website or any other service, it will see that traffic as valid and not prohibit access.

Suricata is just one layer of security that you can implement to protect your assets from network based threats. End-point protection, ransomware protection, and host intrusion detection (e.g. OSSEC) adds more layers of security in order to try to alleviate threats. But just like physical security, if someone wants to work hard enough to get into your house, there is really not a way to stop them. The more layers of security you have, the more likely it is that the attacker will move on and look for softer targets.

A lot of thanks for your fast reply. I am checking this script and I am finding with a trouble.

Script is running good:

# ps wuaxf|grep suri
root      7789 45.3 24.0 1392900 755588 ?      Sl   07:17  54:48 /usr/bin/suricata -c /etc/suricata/suricata-debian.yaml -r -
root      7862  0.3  2.4 177752 77560 ?        Ss   07:18   0:28 barnyard2 -c /etc/suricata/barnyard2.conf -d /var/log/suricata -f unified2.alert -w /var/log/suricata/suricata.waldo -D
root     11335  0.0  0.0   8824   752 pts/0    S+   09:18   0:00      \_ grep --color=auto suri
root     11325  0.2  0.3 191204 10532 pts/0    S    09:18   0:00 php -f /opt/mikrotik-ips-suricata/suricata_block.php

And if I try to access a ransomware website (for example) automatically Mikrotik receive a new line from server with that IP, its okay and running fine.

Now, I leave from that shell, and I again enter a new ransomware’s IP (which it should be banned too because php process is running) but not, when I access this new IP, script doesn’t appear running and IP’s aren’t banned.

I enabled logs php:
error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT
log_errors = On
error_log = /var/log/php_errors.log
But log is empty.

Any idea please?

Thanks.

Is there anything in fast.log? Perhaps suricata didn’t flag any signatures.

[aarango]

@tomfisk

@aarango

@tomfisk

@aarango

Hi,

I added my HOME_NET to suricata.yaml:
HOME_NET: “[192.168.0.0/16,10.0.0.0/8,192.168.5.0/24]”

And I too added a line in threshold.config to avoid that alert from my IP. Could you say me if its right?
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.100
suppress gen_id 1, sig_id 2006380, track by_src, ip 192.168.5.1

suricata/fast.log.1:04/03/2017-09:57:09.934246 [] [1:2006380:12] ET POLICY Outgoing Basic Auth Base64 HTTP Password detected unencrypted [] [Classification: Potential Corporate Privacy Violation] [Priority: 1] {TCP} 192.168.5.1:37764 → 192.168.5.100:8088

192.168.5.100 its my own mail IP.
192.168.5.1 its my own router.

And if from my home_net someone try to upload/download/access/execute any file or bad website, will be detected by suricata or if I have my net home_net is it invisible for it?
I want to avoid attacks from external net and any possible attack from any laptop infected or access prohibited.

Thanks.

Yes, your definitions look right.

Suricata will not prevent all bad things from happening. It will detect network based threats that match it’s signature database. If you have an external facing website or any other service, it will see that traffic as valid and not prohibit access.

Suricata is just one layer of security that you can implement to protect your assets from network based threats. End-point protection, ransomware protection, and host intrusion detection (e.g. OSSEC) adds more layers of security in order to try to alleviate threats. But just like physical security, if someone wants to work hard enough to get into your house, there is really not a way to stop them. The more layers of security you have, the more likely it is that the attacker will move on and look for softer targets.

A lot of thanks for your fast reply. I am checking this script and I am finding with a trouble.

Script is running good:

# ps wuaxf|grep suri
root      7789 45.3 24.0 1392900 755588 ?      Sl   07:17  54:48 /usr/bin/suricata -c /etc/suricata/suricata-debian.yaml -r -
root      7862  0.3  2.4 177752 77560 ?        Ss   07:18   0:28 barnyard2 -c /etc/suricata/barnyard2.conf -d /var/log/suricata -f unified2.alert -w /var/log/suricata/suricata.waldo -D
root     11335  0.0  0.0   8824   752 pts/0    S+   09:18   0:00      \_ grep --color=auto suri
root     11325  0.2  0.3 191204 10532 pts/0    S    09:18   0:00 php -f /opt/mikrotik-ips-suricata/suricata_block.php

And if I try to access a ransomware website (for example) automatically Mikrotik receive a new line from server with that IP, its okay and running fine.

Now, I leave from that shell, and I again enter a new ransomware’s IP (which it should be banned too because php process is running) but not, when I access this new IP, script doesn’t appear running and IP’s aren’t banned.

I enabled logs php:
error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT
log_errors = On
error_log = /var/log/php_errors.log
But log is empty.

Any idea please?

Thanks.

Is there anything in fast.log? Perhaps suricata didn’t flag any signatures.

Yes, suricata is getting rules because I can see it on Snorby.
When I start script again, Mikrotik receive all rules which Suricata/Barnyard2/Snorby had captured. I have to re-start it again because script stops when I leave shell and access a new bad IP. (note: While none IP has been detected by suricata, script continue running but if I access any ransomware’s IP (or any other), script stop run and doesn’t send notifications to Mikrotik)

Here my tests in fast.log

04/10/2017-09:47:00.315253  [**] [1:2404571:4579] ET CNC Ransomware Tracker Reported CnC Server group 172 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:51052 -> 91.226.92.140:80
04/10/2017-09:47:00.315303  [**] [1:2404571:4579] ET CNC Ransomware Tracker Reported CnC Server group 172 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:51052 -> 91.226.92.140:80
04/10/2017-09:48:02.390455  [**] [1:2404404:4579] ET CNC Ransomware Tracker Reported CnC Server group 5 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:52704 -> 109.195.175.176:80
04/10/2017-09:48:02.390541  [**] [1:2404404:4579] ET CNC Ransomware Tracker Reported CnC Server group 5 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:52704 -> 109.195.175.176:80
04/10/2017-09:53:01.815003  [**] [1:2404510:4579] ET CNC Ransomware Tracker Reported CnC Server group 111 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:33050 -> 46.172.212.32:80
04/10/2017-09:53:01.815198  [**] [1:2404510:4579] ET CNC Ransomware Tracker Reported CnC Server group 111 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:33050 -> 46.172.212.32:80

[tomfisk]

@aarango

Yes, when I run script again Mikrotik receive all rules which Suricata/Barnyard2/Snorby had captured. I have to re-run it again because script is stopped when I leave shell.

Here my tests in fast.log

04/10/2017-09:47:00.315253  [**] [1:2404571:4579] ET CNC Ransomware Tracker Reported CnC Server group 172 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:51052 -> 91.226.92.140:80
04/10/2017-09:47:00.315303  [**] [1:2404571:4579] ET CNC Ransomware Tracker Reported CnC Server group 172 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:51052 -> 91.226.92.140:80
04/10/2017-09:48:02.390455  [**] [1:2404404:4579] ET CNC Ransomware Tracker Reported CnC Server group 5 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:52704 -> 109.195.175.176:80
04/10/2017-09:48:02.390541  [**] [1:2404404:4579] ET CNC Ransomware Tracker Reported CnC Server group 5 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:52704 -> 109.195.175.176:80
04/10/2017-09:53:01.815003  [**] [1:2404510:4579] ET CNC Ransomware Tracker Reported CnC Server group 111 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:33050 -> 46.172.212.32:80
04/10/2017-09:53:01.815198  [**] [1:2404510:4579] ET CNC Ransomware Tracker Reported CnC Server group 111 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:33050 -> 46.172.212.32:80

Have you added this signature string

Ransomware Tracker Reported CnC Server

in the sigs_to_block table? By default, only signatures in the sigs_to_block table are actually blocked. I use Anvil to check which signatures I’m seeing and if I see new signatures that I want to block, I add them to that table.

[aarango]

@tomfisk

@aarango

Yes, when I run script again Mikrotik receive all rules which Suricata/Barnyard2/Snorby had captured. I have to re-run it again because script is stopped when I leave shell.

Here my tests in fast.log

04/10/2017-09:47:00.315253  [**] [1:2404571:4579] ET CNC Ransomware Tracker Reported CnC Server group 172 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:51052 -> 91.226.92.140:80
04/10/2017-09:47:00.315303  [**] [1:2404571:4579] ET CNC Ransomware Tracker Reported CnC Server group 172 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:51052 -> 91.226.92.140:80
04/10/2017-09:48:02.390455  [**] [1:2404404:4579] ET CNC Ransomware Tracker Reported CnC Server group 5 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:52704 -> 109.195.175.176:80
04/10/2017-09:48:02.390541  [**] [1:2404404:4579] ET CNC Ransomware Tracker Reported CnC Server group 5 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:52704 -> 109.195.175.176:80
04/10/2017-09:53:01.815003  [**] [1:2404510:4579] ET CNC Ransomware Tracker Reported CnC Server group 111 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.6.2:33050 -> 46.172.212.32:80
04/10/2017-09:53:01.815198  [**] [1:2404510:4579] ET CNC Ransomware Tracker Reported CnC Server group 111 [**] [Classification: A Network Trojan was detected] [Priority: 1] {TCP} 192.168.5.73:33050 -> 46.172.212.32:80

Have you added this signature string

Ransomware Tracker Reported CnC Server

in the sigs_to_block table? By default, only signatures in the sigs_to_block table are actually blocked. I use Anvil to check which signatures I’m seeing and if I see new signatures that I want to block, I add them to that table.

Yes, I edited my own message to be more clear:

Yes, suricata is getting rules because I can see it on Snorby.
When I start script again, Mikrotik receive all rules which Suricata/Barnyard2/Snorby had captured. I have to re-start it again because script stops when I leave shell and access a new bad IP. (note: While none IP has been detected by suricata, script continue running but if I access any ransomware’s IP (or any other), script stop run and doesn’t send notifications to Mikrotik).

Maybe could I debug php in any log to check because script stops?

Thanks.

[tomfisk]

@aarango

Yes, I edited my own message to be more clear:

Yes, suricata is getting rules because I can see it on Snorby.
When I start script again, Mikrotik receive all rules which Suricata/Barnyard2/Snorby had captured. I have to re-start it again because script stops when I leave shell and access a new bad IP. (note: While none IP has been detected by suricata, script continue running but if I access any ransomware’s IP (or any other), script stop run and doesn’t send notifications to Mikrotik).

Maybe could I debug php in any log to check because script stops?

Thanks.

This is my php script, or script from max?

[aarango]

@tomfisk

@aarango

Yes, I edited my own message to be more clear:

Yes, suricata is getting rules because I can see it on Snorby.
When I start script again, Mikrotik receive all rules which Suricata/Barnyard2/Snorby had captured. I have to re-start it again because script stops when I leave shell and access a new bad IP. (note: While none IP has been detected by suricata, script continue running but if I access any ransomware’s IP (or any other), script stop run and doesn’t send notifications to Mikrotik).

Maybe could I debug php in any log to check because script stops?

Thanks.

This is my php script, or script from max?

It is your script. Its weird because now script sent 2 rules to Mikrotik but in third test, script has stopped. Some is killing that script which I can’t see what neither why.

Here output file php (which its always showing but doesn’t matter because with that error if I don’t leave shell, Mikrotik receive alerts) →
Notice: Array to string conversion in /opt/mikrotik-ips-suricata/suricata_block.php on line 41
Array
Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /opt/mikrotik-ips-suricata/suricata_block.php on line 139

Here script:

# cat /opt/mikrotik-ips-suricata/suricata_block.php 
<?php
require('routeros_api.class.php');

/* Set your specific configuration below */
$user_name = "snorby";
$password = "XXXXXX";
$database = "snorby";
$server = "localhost";
$mikrotik_addr = "192.168.X.X";
$mikrotik_user = "admin";
$mikrotik_pwd = "XXXXX";
$local_ip_prefix = "192.168.";
$email_to = "MYEMAIL";
$email_from = "MYEMAIL";

header('Content-Type: text/plain');

$API = new RouterosAPI();

function UptimeInSeconds($uptime) { 
  $mark1=strpos($uptime, "d"); 
  $days=substr($uptime, 0, $mark1); 
  if ($mark1) $uptime=substr($uptime, $mark1 + 1); 
  $mark1=strpos($uptime, "h"); 
  $hours=substr($uptime, 0, $mark1); 
  if ($mark1) $uptime=substr($uptime, $mark1 + 1); 
  $mark1=strpos($uptime, "m"); 
  $minutes=substr($uptime, 0, $mark1); 
  if ($mark1) $uptime=substr($uptime, $mark1 + 1); 
  $mark1=strpos($uptime, "s"); 
  $seconds=substr($uptime, 0, $mark1); 
  if ($mark1) $uptime=substr($uptime, $mark1 + 1); 
  $total=($days * 86400) + ($hours * 3600) + ($minutes * 60) + $seconds; 
  return $total; 
}

function AddToFirewall($thisrow) {
  echo $thisrow;
  global $local_ip_prefix, $API, $mikrotik_addr, $mikrotik_user, $mikrotik_pwd, $email_to, $email_from;

  if (strpos($thisrow['que_ip_adr'], $local_ip_prefix) !== true) {
    /* Does not match local address... */
    /* See if the address is already in the firewall list, if so delete it so we can readd it with a new timeout */
    try {
        $API->connect($mikrotik_addr, $mikrotik_user, $mikrotik_pwd);
    } catch (Exception $e) {
        die('Unable to connect to RouterOS. Error:' . $e);
    }
    $ARRAY = $API->comm("/ip/firewall/address-list/print", array(
       ".proplist"=> ".id",
       "?address" => $thisrow['que_ip_adr'],));
    foreach ($ARRAY as $a) {
      foreach ($a as $name => $value) {
        $API->write("/ip/firewall/address-list/remove",false);
        $API->write("=.id=$value",true);
        $API->read();
      }
    }
    if (array_key_exists('que_remaining', $thisrow))
      { $timeremaining = $thisrow['que_remaining']; }
    else
      { $timeremaining = $thisrow['que_timeout']; } 
    $API->comm("/ip/firewall/address-list/add", array(
      "list" => "Blocked",
      "address" => $thisrow['que_ip_adr'],
      "timeout" => $timeremaining,
      "comment" => "From suricata, " . $thisrow['que_sig_name'] . " => " . $thisrow['que_sig_gid'] . ":" . $thisrow['que_sig_sid'] .
         " => event timestamp: " . $thisrow['que_event_timestamp'],));
    $API->disconnect();
  } else {
    /* Send email indicating bad block attempt*/
    $to      = $email_to;
    $subject = 'Suricata on ' . gethostname() . ': attempted block on local address';
    $message = 'A record in the block_queue indicated a block on a local IP Address (' . $row['que_ip_adr'] . ")\r\n";
    $message = $message . "\r\n";
    $message = $message . "The signature ID is " . $row['que_sig_id'] . " named: " . $row['que_sig_name'] . "\r\n";
    $message = $message . "    with a que_id of " . $row['que_id'] . "\r\n\r\n";
    $message = $message . "Check the src_or_dst field in events_to_block for the signature to make sure it is correct (src/dst).\r\n\r\n";
    $message = $message . "The record was not processed but marked as completed.\r\n";
    $headers = 'From: ' . $email_from . "\r\n" .
      'Reply-To: ' . $email_from . "\r\n" .
      'X-Mailer: PHP/' . phpversion();
    mail($to, $subject, $message, $headers);
  }
  return true;
}

/* Connect to database, if unsuccessful keep trying for 100 seconds */
    $i = 0;
    while ( $i < 100 ) {
      $db = new mysqli($server, $user_name, $password, $database);
      if ($db->connect_errno > 0) {
        print('Unable to connect to database [' . $db->connect_error . ']');
        sleep(10);
        $i = $i + 10;
      }
      else {
        $i = 100;
      }
    }

/* Check to see how long the mikrotik has been up.  If less than 5 minutes then rebuild firewall list */
    try {
        $API->connect($mikrotik_addr, $mikrotik_user, $mikrotik_pwd);
    } catch (Exception $e) {
        die('Unable to connect to RouterOS. Error:' . $e);
    }
    $ARRAY = $API->comm("/system/resource/print", false);
    $upsecs = UptimeInSeconds($ARRAY[0]['uptime']);  
    $API->disconnect();
    if ($upsecs < 300) {
      /* This SELECT statement will ignore any entries that had 2 minutes or less remaining */
      $SQL = "SELECT *,DATE_FORMAT(TIMEDIFF(ADDTIME(que_added,que_timeout), CURRENT_TIMESTAMP), '%H:%i:%s') as que_remaining " . 
          "FROM block_queue where ADDTIME(que_added, que_timeout) > TIMESTAMPADD(MINUTE, 2, CURRENT_TIMESTAMP) order by que_remaining;";
      if (!$result = $db->query($SQL)) {
          die('There was an error running the query [' . $db->error . ']');
      }
      while ($row = $result->fetch_assoc()) {
        $x = AddToFirewall($row);
      }
      $rslt = mysqli_free_result($result);
    }

/* Main program loop */
    while ( 1 == 1 ) {
      $SQL = "SELECT * FROM block_queue WHERE que_processed = 0;";
      if(!$result = $db->query($SQL)) {
        die('There was an error running the query [' . $db->error . ']');
      }
      while($row = $result->fetch_assoc()) {
        $x = AddToFirewall($row);
        $SQL2 = "UPDATE block_queue set que_processed = 1 WHERE que_id = " . $row['que_id'] . ";";
        if (!$result2 = $db->query($SQL2)) {
          die('There was an error running the query [' . $db->error . ']');
        }
        $rslt = mysqli_free_result($result2);
      }
      $rslt = mysqli_free_result($result);
      sleep(5); /* Sleep 5 seconds then do again */
      mysqli_ping($db);
    }
  $db->close();
?>

Sorry for so many post converting the post (a great job and an excellent tool) in my troubleshooting. Thanks.

[tomfisk]

@aarango

It is your script. Its weird because now script sent 2 rules to Mikrotik but in third test, script has stopped. Some is killing that script which I can’t see what neither why.

Here output file php (which its always showing but doesn’t matter because with that error if I don’t leave shell, Mikrotik receive alerts) →
Notice: Array to string conversion in /opt/mikrotik-ips-suricata/suricata_block.php on line 41
Array
Warning: mysqli_free_result() expects parameter 1 to be mysqli_result, boolean given in /opt/mikrotik-ips-suricata/suricata_block.php on line 139

Here script:

# cat /opt/mikrotik-ips-suricata/suricata_block.php 
<?php
require('routeros_api.class.php');

/* Set your specific configuration below */
$user_name = "snorby";
$password = "XXXXXX";
$database = "snorby";
$server = "localhost";
$mikrotik_addr = "192.168.X.X";
$mikrotik_user = "admin";
$mikrotik_pwd = "XXXXX";
$local_ip_prefix = "192.168.";
$email_to = "MYEMAIL";
$email_from = "MYEMAIL";

header('Content-Type: text/plain');

$API = new RouterosAPI();

function UptimeInSeconds($uptime) { 
  $mark1=strpos($uptime, "d"); 
  $days=substr($uptime, 0, $mark1); 
  if ($mark1) $uptime=substr($uptime, $mark1 + 1); 
  $mark1=strpos($uptime, "h"); 
  $hours=substr($uptime, 0, $mark1); 
  if ($mark1) $uptime=substr($uptime, $mark1 + 1); 
  $mark1=strpos($uptime, "m"); 
  $minutes=substr($uptime, 0, $mark1); 
  if ($mark1) $uptime=substr($uptime, $mark1 + 1); 
  $mark1=strpos($uptime, "s"); 
  $seconds=substr($uptime, 0, $mark1); 
  if ($mark1) $uptime=substr($uptime, $mark1 + 1); 
  $total=($days * 86400) + ($hours * 3600) + ($minutes * 60) + $seconds; 
  return $total; 
}

function AddToFirewall($thisrow) {
  echo $thisrow;
  global $local_ip_prefix, $API, $mikrotik_addr, $mikrotik_user, $mikrotik_pwd, $email_to, $email_from;

  if (strpos($thisrow['que_ip_adr'], $local_ip_prefix) !== true) {
    /* Does not match local address... */
    /* See if the address is already in the firewall list, if so delete it so we can readd it with a new timeout */
    try {
        $API->connect($mikrotik_addr, $mikrotik_user, $mikrotik_pwd);
    } catch (Exception $e) {
        die('Unable to connect to RouterOS. Error:' . $e);
    }
    $ARRAY = $API->comm("/ip/firewall/address-list/print", array(
       ".proplist"=> ".id",
       "?address" => $thisrow['que_ip_adr'],));
    foreach ($ARRAY as $a) {
      foreach ($a as $name => $value) {
        $API->write("/ip/firewall/address-list/remove",false);
        $API->write("=.id=$value",true);
        $API->read();
      }
    }
    if (array_key_exists('que_remaining', $thisrow))
      { $timeremaining = $thisrow['que_remaining']; }
    else
      { $timeremaining = $thisrow['que_timeout']; } 
    $API->comm("/ip/firewall/address-list/add", array(
      "list" => "Blocked",
      "address" => $thisrow['que_ip_adr'],
      "timeout" => $timeremaining,
      "comment" => "From suricata, " . $thisrow['que_sig_name'] . " => " . $thisrow['que_sig_gid'] . ":" . $thisrow['que_sig_sid'] .
         " => event timestamp: " . $thisrow['que_event_timestamp'],));
    $API->disconnect();
  } else {
    /* Send email indicating bad block attempt*/
    $to      = $email_to;
    $subject = 'Suricata on ' . gethostname() . ': attempted block on local address';
    $message = 'A record in the block_queue indicated a block on a local IP Address (' . $row['que_ip_adr'] . ")\r\n";
    $message = $message . "\r\n";
    $message = $message . "The signature ID is " . $row['que_sig_id'] . " named: " . $row['que_sig_name'] . "\r\n";
    $message = $message . "    with a que_id of " . $row['que_id'] . "\r\n\r\n";
    $message = $message . "Check the src_or_dst field in events_to_block for the signature to make sure it is correct (src/dst).\r\n\r\n";
    $message = $message . "The record was not processed but marked as completed.\r\n";
    $headers = 'From: ' . $email_from . "\r\n" .
      'Reply-To: ' . $email_from . "\r\n" .
      'X-Mailer: PHP/' . phpversion();
    mail($to, $subject, $message, $headers);
  }
  return true;
}

/* Connect to database, if unsuccessful keep trying for 100 seconds */
    $i = 0;
    while ( $i < 100 ) {
      $db = new mysqli($server, $user_name, $password, $database);
      if ($db->connect_errno > 0) {
        print('Unable to connect to database [' . $db->connect_error . ']');
        sleep(10);
        $i = $i + 10;
      }
      else {
        $i = 100;
      }
    }

/* Check to see how long the mikrotik has been up.  If less than 5 minutes then rebuild firewall list */
    try {
        $API->connect($mikrotik_addr, $mikrotik_user, $mikrotik_pwd);
    } catch (Exception $e) {
        die('Unable to connect to RouterOS. Error:' . $e);
    }
    $ARRAY = $API->comm("/system/resource/print", false);
    $upsecs = UptimeInSeconds($ARRAY[0]['uptime']);  
    $API->disconnect();
    if ($upsecs < 300) {
      /* This SELECT statement will ignore any entries that had 2 minutes or less remaining */
      $SQL = "SELECT *,DATE_FORMAT(TIMEDIFF(ADDTIME(que_added,que_timeout), CURRENT_TIMESTAMP), '%H:%i:%s') as que_remaining " . 
          "FROM block_queue where ADDTIME(que_added, que_timeout) > TIMESTAMPADD(MINUTE, 2, CURRENT_TIMESTAMP) order by que_remaining;";
      if (!$result = $db->query($SQL)) {
          die('There was an error running the query [' . $db->error . ']');
      }
      while ($row = $result->fetch_assoc()) {
        $x = AddToFirewall($row);
      }
      $rslt = mysqli_free_result($result);
    }

/* Main program loop */
    while ( 1 == 1 ) {
      $SQL = "SELECT * FROM block_queue WHERE que_processed = 0;";
      if(!$result = $db->query($SQL)) {
        die('There was an error running the query [' . $db->error . ']');
      }
      while($row = $result->fetch_assoc()) {
        $x = AddToFirewall($row);
        $SQL2 = "UPDATE block_queue set que_processed = 1 WHERE que_id = " . $row['que_id'] . ";";
        if (!$result2 = $db->query($SQL2)) {
          die('There was an error running the query [' . $db->error . ']');
        }
        $rslt = mysqli_free_result($result2);
      }
      $rslt = mysqli_free_result($result);
      sleep(5); /* Sleep 5 seconds then do again */
      mysqli_ping($db);
    }
  $db->close();
?>

Sorry for so many post converting the post (a great job and an excellent tool) in my troubleshooting. Thanks.

Hmmmm…can you post the definition of your block_queue table?

mysql> show table create block_queue;

[aarango]

@tomfisk

Hmmmm…can you post the definition of your block_queue table?

mysql> show table create block_queue;

Here:

mysql> SHOW CREATE TABLE block_queue\G;
*************************** 1. row ***************************
       Table: block_queue
Create Table: CREATE TABLE `block_queue` (
  `que_id` int(11) NOT NULL AUTO_INCREMENT,
  `que_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the block was added',
  `que_ip_adr` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The IP address to block',
  `que_timeout` varchar(12) COLLATE utf8_unicode_ci NOT NULL COMMENT 'How long to block for',
  `que_sig_name` varchar(256) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The name of the signature that caused the block',
  `que_sig_gid` int(10) NOT NULL COMMENT 'The signature group ID',
  `que_sig_sid` int(10) NOT NULL COMMENT 'The signature ID',
  `que_event_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'When the event was triggered',
  `que_processed` int(11) NOT NULL DEFAULT '0' COMMENT 'If this item has been processed (0=no, <>0=yes)',
  PRIMARY KEY (`que_id`),
  KEY `que_added` (`que_added`)
) ENGINE=InnoDB AUTO_INCREMENT=644 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Queue of ip addresses to block on firewall'
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> check table block_queue;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| snorby.block_queue | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.01 sec)

Note: Now for 1h I leave/enter shell and script is adding lines on Mikrotik. However, I dont know how stable it will be this… I will keep monitoring all day, if you know how could I debug it please let me know.
I enabled logs mysql too, with this output constantly:

suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:48' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:50    49 Query     SELECT * FROM block_queue WHERE que_processed = 0
170410 13:06:53    51 Query     SELECT `id`, `priority`, `attempts`, `handler`, `run_at`, `locked_at`, `failed_at` FROM `delayed_jobs` WHERE ((`locked_at` IS NULL OR `locked_at` < '2017-04-10 09:06:53' OR `locked_by` = 'delayed_job host:suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:53' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:55    49 Query     SELECT * FROM block_queue WHERE que_processed = 0

Thanks.

[tomfisk]

@aarango

@tomfisk

Hmmmm…can you post the definition of your block_queue table?

mysql> show table create block_queue;

Here:

mysql> SHOW CREATE TABLE block_queue\G;
*************************** 1. row ***************************
       Table: block_queue
Create Table: CREATE TABLE `block_queue` (
  `que_id` int(11) NOT NULL AUTO_INCREMENT,
  `que_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the block was added',
  `que_ip_adr` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The IP address to block',
  `que_timeout` varchar(12) COLLATE utf8_unicode_ci NOT NULL COMMENT 'How long to block for',
  `que_sig_name` varchar(256) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The name of the signature that caused the block',
  `que_sig_gid` int(10) NOT NULL COMMENT 'The signature group ID',
  `que_sig_sid` int(10) NOT NULL COMMENT 'The signature ID',
  `que_event_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'When the event was triggered',
  `que_processed` int(11) NOT NULL DEFAULT '0' COMMENT 'If this item has been processed (0=no, <>0=yes)',
  PRIMARY KEY (`que_id`),
  KEY `que_added` (`que_added`)
) ENGINE=InnoDB AUTO_INCREMENT=644 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Queue of ip addresses to block on firewall'
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> check table block_queue;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| snorby.block_queue | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.01 sec)

Note: Now for 1h I leave/enter shell and script is adding lines on Mikrotik. However, I dont know how stable it will be this… I will keep monitoring all day, if you know how could I debug it please let me know.
I enabled logs mysql too, with this output constantly:

suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:48' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:50    49 Query     SELECT * FROM block_queue WHERE que_processed = 0
170410 13:06:53    51 Query     SELECT `id`, `priority`, `attempts`, `handler`, `run_at`, `locked_at`, `failed_at` FROM `delayed_jobs` WHERE ((`locked_at` IS NULL OR `locked_at` < '2017-04-10 09:06:53' OR `locked_by` = 'delayed_job host:suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:53' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:55    49 Query     SELECT * FROM block_queue WHERE que_processed = 0

Thanks.

OK, I’m thinking that perhaps there were bad records in the block_queue table from the initial trigger implementation using max’s code. If those records expired then it shouldn’t occur again. Keep me updated.

Thanks!

[aarango]

@tomfisk

@aarango

@tomfisk

Hmmmm…can you post the definition of your block_queue table?

mysql> show table create block_queue;

Here:

mysql> SHOW CREATE TABLE block_queue\G;
*************************** 1. row ***************************
       Table: block_queue
Create Table: CREATE TABLE `block_queue` (
  `que_id` int(11) NOT NULL AUTO_INCREMENT,
  `que_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the block was added',
  `que_ip_adr` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The IP address to block',
  `que_timeout` varchar(12) COLLATE utf8_unicode_ci NOT NULL COMMENT 'How long to block for',
  `que_sig_name` varchar(256) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The name of the signature that caused the block',
  `que_sig_gid` int(10) NOT NULL COMMENT 'The signature group ID',
  `que_sig_sid` int(10) NOT NULL COMMENT 'The signature ID',
  `que_event_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'When the event was triggered',
  `que_processed` int(11) NOT NULL DEFAULT '0' COMMENT 'If this item has been processed (0=no, <>0=yes)',
  PRIMARY KEY (`que_id`),
  KEY `que_added` (`que_added`)
) ENGINE=InnoDB AUTO_INCREMENT=644 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Queue of ip addresses to block on firewall'
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> check table block_queue;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| snorby.block_queue | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.01 sec)

Note: Now for 1h I leave/enter shell and script is adding lines on Mikrotik. However, I dont know how stable it will be this… I will keep monitoring all day, if you know how could I debug it please let me know.
I enabled logs mysql too, with this output constantly:

suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:48' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:50    49 Query     SELECT * FROM block_queue WHERE que_processed = 0
170410 13:06:53    51 Query     SELECT `id`, `priority`, `attempts`, `handler`, `run_at`, `locked_at`, `failed_at` FROM `delayed_jobs` WHERE ((`locked_at` IS NULL OR `locked_at` < '2017-04-10 09:06:53' OR `locked_by` = 'delayed_job host:suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:53' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:55    49 Query     SELECT * FROM block_queue WHERE que_processed = 0

Thanks.

OK, I’m thinking that perhaps there were bad records in the block_queue table from the initial trigger implementation using max’s code. If those records expired then it shouldn’t occur again. Keep me updated.

Thanks!

Hi again!

In last 12h server had been running without problems. I dont know if it was for code, for triggers or something but its running correctly. I will be monitoring it today all day to check if script go down or not.
While… I will test your new update, OSSEC, it sounds really good. Could I install server on suricata’s server and check agents, dont? I will receive all logs on suricata’s server and I will check it on his own web GUI or Mikrotik if any IP have been banned.

Thanks for all your support Tom.

[tomfisk]

@aarango

@tomfisk

@aarango

@tomfisk

Hmmmm…can you post the definition of your block_queue table?

mysql> show table create block_queue;

Here:

mysql> SHOW CREATE TABLE block_queue\G;
*************************** 1. row ***************************
       Table: block_queue
Create Table: CREATE TABLE `block_queue` (
  `que_id` int(11) NOT NULL AUTO_INCREMENT,
  `que_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When the block was added',
  `que_ip_adr` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The IP address to block',
  `que_timeout` varchar(12) COLLATE utf8_unicode_ci NOT NULL COMMENT 'How long to block for',
  `que_sig_name` varchar(256) COLLATE utf8_unicode_ci NOT NULL COMMENT 'The name of the signature that caused the block',
  `que_sig_gid` int(10) NOT NULL COMMENT 'The signature group ID',
  `que_sig_sid` int(10) NOT NULL COMMENT 'The signature ID',
  `que_event_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'When the event was triggered',
  `que_processed` int(11) NOT NULL DEFAULT '0' COMMENT 'If this item has been processed (0=no, <>0=yes)',
  PRIMARY KEY (`que_id`),
  KEY `que_added` (`que_added`)
) ENGINE=InnoDB AUTO_INCREMENT=644 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Queue of ip addresses to block on firewall'
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> check table block_queue;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| snorby.block_queue | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.01 sec)

Note: Now for 1h I leave/enter shell and script is adding lines on Mikrotik. However, I dont know how stable it will be this… I will keep monitoring all day, if you know how could I debug it please let me know.
I enabled logs mysql too, with this output constantly:

suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:48' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:50    49 Query     SELECT * FROM block_queue WHERE que_processed = 0
170410 13:06:53    51 Query     SELECT `id`, `priority`, `attempts`, `handler`, `run_at`, `locked_at`, `failed_at` FROM `delayed_jobs` WHERE ((`locked_at` IS NULL OR `locked_at` < '2017-04-10 09:06:53' OR `locked_by` = 'delayed_job host:suricata pid:19102') AND `run_at` <= '2017-04-10 13:06:53' AND `failed_at` IS NULL) ORDER BY `priority`, `run_at` LIMIT 5
170410 13:06:55    49 Query     SELECT * FROM block_queue WHERE que_processed = 0

Thanks.

OK, I’m thinking that perhaps there were bad records in the block_queue table from the initial trigger implementation using max’s code. If those records expired then it shouldn’t occur again. Keep me updated.

Thanks!

Hi again!

In last 12h server had been running without problems. I dont know if it was for code, for triggers or something but its running correctly. I will be monitoring it today all day to check if script go down or not.
While… I will test your new update, OSSEC, it sounds really good. Could I install server on suricata’s server and check agents, dont? I will receive all logs on suricata’s server and I will check it on his own web GUI or Mikrotik if any IP have been banned.

Thanks for all your support Tom.

Glad to hear it is running well. Yes, install OSSEC server on same server as suricata.

[aarango]

OSSEC installed and running fine (excelent & easy manual).
Some question, I installed too web UI for OSSEC and its running fine too, but I would like to have logs from anothers servers to check integrity, logs, etc.

Most important question. Should I install agent on servers or since everything is addressed to Mikrotik I do not need to have the agents sending logs to the server?
Could I install agents on servers to have logs on web UI?
What did you think about install ntop too to check on graphic all office’s traffic taking advantage of all mikrotik traffic goes to that server?

Thanks as always.

[tomfisk]

@aarango

OSSEC installed and running fine (excelent & easy manual).
Two question, I installed too web UI for OSSEC and its running fine too, but I would like to have logs from anothers servers to check integrity, logs, etc.

Most important question. Should I install agent on servers or since everything is addressed to Mikrotik I do not need to have the agents sending logs to the server?
Could I install agents on servers to have logs on web UI?

Thanks as always.

From what I read the Web UI is no longer being maintained. Just an FYI. I never installed it.

Yes, you can install agents on other hosts, send logs to server, and trigger active events from those hosts on server. I have multiple agents sending their logs to the server and I can trigger an active response to the Mikrotik from any agent activity.