blog-image

OSSEC - SQL Analyze and GRAFANA - COUNTERMEASURES

  • dHENRY
  • 01/05/2019
  • (Reading time : 6 mn)

(**) Translated with www.DeepL.com/Translator

You have installed OSSEC on your infrastructure, the system works well, alerts are stored in a MariaDB/Mysql database. Every day you see alerts arriving by email, fortunately their number is limited, this limit is determined by the OSSEC configuration.

    <code>
    <alerts>
    <log_alert_level>1</log_alert_level>
    <email_alert_level>7</email_alert_level>
    <alerts/>>
    </code>

With this configuration, each alert whose level >= 1 is stored in the Mysql database, only alerts whose level >= 7 are sent by email. The visualization of emails does not allow to evaluate the number of alerts assigned to a single IP address. We know that OSSEC is working well but it would be interesting to obtain some statistics on these intrusion attempts, and why not consider a federation system to identify suspicious IP addresses…

If you only receive a few emails, OSSEC does a lot of work in the background.
After 3 months of exposure on the Internet, you will be surprised to see the number of records registered in the “alerts” table of the “ossec” database.
After 6 months of use, its size exceeds 3 million records, not including alerts generated on the local network.
To use this data, connect to the database, with “mysql” (console), phpmyadmin, etc… and we will analyze some points using basic SQL queries.

Preparation

The purpose of this post is to work on alerts from incidents reported on Internet connections. To discard all alerts from local networks, you must determine the list of local networks to which the servers are connected. The following examples will use local networks: 10.40.0.0.0/24 and 192.168.0.0.0/24.

**WARNING - I voluntarily limit the scope of the analyses to that of the Internet, but do not forget that the analyses must also be done on the “Local Networks” side. Real life is not like the film “Die Hard 4”. Threats come more from inside than outside.

Detection of the most frequent IP sources

Selection of all sources from the Internet, display of the source, the number of times encountered and ranked in descending order on the number of times encountered (sources).

SELECT src_ip as a ,count(src_ip) as c FROM alert WHERE ( src_ip <> '(null)' AND src_ip <> '::' AND src_ip <> '::1' AND src_ip <>' 127.0.0.1' AND src_ip NOT LIKE' 10.40.0.%' AND src_ip NOT LIKE' 192.168.0.%') GROUP BY src_ip ORDER BY c DESC

The list can be very impressive. Let us look at cases where the “number of times encountered” is more than 1000.

SELECT src_ip as a ,count(src_ip) as c FROM alert WHERE ( src_ip <> '(null)' AND src_ip <> '::' AND src_ip <> '::1' AND src_ip <>' 127.0.0.1' AND src_ip NOT LIKE' 10.40.0.%' AND src_ip NOT LIKE' 192.168.0.%') GROUP BY src_ip HAVING c > 1000 ORDER BY c DESC

I get a list of addresses with frightening numbers. Connected to the Internet, the numbers quickly become disproportionate, because this type of attack is orchestrated by robots.

We see addresses from the Chinese segment “218.92.0.xxx” appear, we will be interested in one of the other addresses. I will look in the database what types of attacks are used by these IP addresses:

SELECT FROM_UNIXTIME(timestamp),level,full_log FROM alert WHERE (src_ip ='XXX.XXX.XXX.XXX.XXX.XXX') ORDER BY timestamp DESC

All attacks are of the same type for this host. About the frequency, by default, OSSEC blocks the IP address for 600 seconds from a detected “level 6” (600 seconds = 10 minutes). The frequency of the attack is higher than the frequency of the OSSEC blockage, it is very likely that the bot will adapt to the countermeasure.

Countermeasures

The main countermeasure is managed by OSSEC and it is clear from its traces that it “does the job”.
You can also contact the service provider of this IP address.
Each Datacenter has an email address of the type “abuse@domainsupplier”. You may write very often. To find information about the IP address, you can use the whois command: **_whois _**XXX.XXX.XXX.XXX.XXX.XXX

inetnum: XXX.XXX.XXX.XXX.XXX.0 - XXX.XXX.XXX.XXX.255  
netname: PROVIDER  
country: FR  
[..]
remarks: --------------------------------------------------------------------  
remarks: Abuse notifications to: **Abuse@**PROVIDE  
remarks: -----------------------------------------------------------------------

You have installed OSSEC on your infrastructure, the system works well, alerts are stored in a MariaDB/Mysql database. Every day you see alerts arriving by email, fortunately their number is limited, this limit is determined by the OSSEC configuration.
Warning, OSSEC uses this file as soon as it starts, by creating a copy of the original, which it puts back in place, when it stops.
If you modify this file with the OSSEC process active, you will lose these changes each time OSSEC is stopped/restarted.
Good practice is:

systemctl stop ossec
# Add IP address to /etc/hosts.deny file  
systemctl start ossec

Important note on this countermeasure

In this demonstration I use the principle “hosts.deny”. Note that this mechanism only works on services that support the “libwrap.a” library (TCP Wrappers). If the service to be protected does not integrate this library, the countermeasure will be ineffective. To find out about the service supports this library, use the “ldd” command applied to the service executable. Example for ssh :

ldd /sbin/sshd | grep libwrap

gives:

libwrap.so.0 => /lib64/libwrap.so.0 (0x00007f1fea58000000)

Try with “apache2”:

ldd /usr/sbin/apache2 | grep libwrap

no results.

Therefore, the TCP Wrappers mechanism has no action on the “apache2” service, you will have to use the Firewall.

source : https://www.thegeekdiary.com/understanding-tcp-wrappers-in-linux/

Adding an IP address or segment: /etc/hosts.deny

The best solution is to read the man: man hosts.deny

For a single IP address: process name or Port or Wildcard]:[IP address] Ex : ALL:192.168.0.1
For a list of IP addresses: process name or Port or Wildcard]:[IP address],[IP address], … Ex: ALL:192.168.0.1,192.168.168.0.2
For a complete segment:[name of the daemon (process) or Port or Wildcard]: Ex: ALL:192.168.168.0. for all processes, and for the network 192.168.0.0.0/24 (192.168.0.0.0/255.255.255.255.0)

You can specify the Wilcard ALL for all processes, otherwise specify the name of the process of your choice (e. g. in.fingerd or ALL EXCEPT in.fingerd, ….).

In my case, I indicate: ALL:XXX.XXX.XXX.XXX.XXX.XXX
I prefer the notation “one ip per line” rather than an address list, everyone does as they wish.

No action is required, the modification is immediately taken into account by the Kernel.

GRAFANA and OSSEC

This implementation will only be possible if you have set up OSSEC to store alerts in a “MariaDB/Mysql” database. The GRAFANA tool has a Mysql connector, so it will be possible to easily connect it to the “ossec” database and start building a dashboard, according to your needs.

Example of a dashboard

N.B.: Knowledge of Grafana and SQL is required.

GO FURTHER

  • Create a centralized list of IPs to blacklist for Internet front-ends.
  • Create a bot that updates this list and installs it on all front-end servers.

Document licence : Creative Commons (CC BY-NC-ND 4.0)

THIS DOCUMENTATION IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND AND DISTRIBUTED FOR EDUCATIONAL PURPOSES ONLY. THE AUTHOR, CONTRIBUTORS TO THIS DOCUMENTATION OR ©MYTINYDC.COM SHALL IN NO EVENT BE LIABLE FOR ANY DIRECT OR INDIRECT DAMAGE THAT MAY RESULT FROM THE APPLICATION OF THE PROCEDURES IMPLEMENTED IN THIS DOCUMENTATION, OR FROM THE INCORRECT INTERPRETATION OF THIS DOCUMENT.

(**) Translated with www.DeepL.com/Translator