Tag: HIDS

Getting MySQL logs into Alienvault

So from our previous article we have gotten Alienvault (or OSSIM) running in your own Virtualbox, and it is able to communicate with the host (your laptop). Again, the reason why we have this is for a small mini lab that you can just shoot up without worrying about doing a VPN connectivity back to the office or whereever, and just do a very basic troubleshooting or learning. It’s highly useful for us, especially we deal a lot with custom applications plugins where we need to either filter or interpret using Alienvault.

So the objective here is to first get MySQL installed and running, and then have MySQL start logging. Now, for the sake of standardisation, we are going to install MySQL Community Edition. Instead of detailing all the complexity of Windows installation, we will keep it brief: Download, click, wait, done.

The more detailed link is as below, but in all honesty, there is nothing overly difficult with clicking on the windows installation file. Locating where you downloaded it is probably a bit more difficult that the actual installation itself.

https://dev.mysql.com/doc/refman/8.0/en/windows-installation.html

Once it’s installed, (we just installed it as a service), verify that its up and running by going to the Windows services and checking for MySQL80. Or you could just run netstat – an and find the following:

TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING

Next, for the sake of laziness, you probably want to add the MySQL Bin path to your windows environmental variable path: C:\Program Files\MySQL\MySQL Shell 8.0\bin\. Just go System Properties ->Advance -> Environmental Variables.

Once that is done, you can run the command

mysql -u username -p

You should be prompted with a password and you can enter that and you should be in.

Now, we are not going to go through mysql CLI commands as this isn’t the point of the article. The point here is to create some sort of logs from MySQL and fetch those logs over to Alienvault. There are many ways to do it, and it seems for windows, the easiest would be to just dump it into the event viewer and let HIDS go and fetch it. But we don’t like to do things the easy way because we are technology sadists. So the idea here is to log MySQL to a flat file and get HIDS to grab it and get Alienvault to interpret it.

Logging and MySQL does have a long history with us. We’ve written an article a few years ago on getting MySQL community edition to log queries using the MariaDB plugin: https://www.pkfavantedge.com/it-compliance/pci-dss-logging-in-mysql-community-version-with-mariadb-plugin/. 

We are a big fan of the above plugin, as most of our clients tend to end up with MySQL Community Edition, which means some plugins like the official MySQL Enterprise Audit Plugin is not available for cheapskate like us. There is the Percona Audit plugin as well which we have not tried but it seems very much focused on Percona. There is also the McAfee plugin which we tried but after a bit of tinkering decided we were probably too stupid busy to make it work. So we were left with the MariaDB plugin which we got it to work for our client.

It’s still a good read but it has been a few years old. And we will definitely relook into it in the near future.

This time around, we are going to get MySQL Windows version to write the general query log into a flat file instead and have HIDS pick it up. This provides us with a few ideas of how HIDS/Alienvault can be configured to pick up any flat file, which gives you pretty much God-like powers in terms of being flexible in getting logs to your Alienvault. If we can get any flat file and create events from those, the possibility to integrate with any custom applications is endless.

To start you need to be aware of two things:

a) There is already a native logging capability in MySQL CE to log to a flat file which we will be using for illustrative purpose: the all powerful “General query” log. Why we say illustrative is that this isn’t probably a long term solution as it’s akin to turning on a debug on your app. There is a LOT of logs, because every query is logged. Useful for troubleshooting, not so cool if you have a busy server because it grows pretty quickly.

b) Windows doesn’t have a native way to send logs, except by way of WEF (Windows Event Forwarder) which basically just sends logs to a collector (windows system). It seems like an awfully clunky way to do log centralisation, so its probably better (still in 2021!) to use either a forwarder like NXLOG or install OSSEC (HIDS) as an agent to talk to Alienvault.

So for this article, we will start by enabling general query log on your Windows MySQL instance.

mysql> set global general_log_file='C:\MySQLLOG\db.log';
Query OK, 0 rows affected (2.39 sec)

mysql> set global log_output = 'file';
Query OK, 0 rows affected (0.00 sec)

mysql> set global general_log = on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%general%';
+------------------+----------------------------+
| Variable_name | Value |
+------------------+----------------------------+
| general_log | ON |
| general_log_file | C:/MySQLLOG/db.log |
+------------------+----------------------------+
2 rows in set (0.01 sec)

The above series of commands basically just tells MySQL to turn on general log, and then say where the the file is located and instruct it to be a file. You can verify if it is set by the last command.

For more persistence, you can go ahead and and edit C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

And include the following under the header General and Slow Logging

#General and Slow logging.
log-output=FILE
general-log=1
general_log_file="C:/MySQLLOG/db.log"

Restart your service and you should be able to see if there is a db.log file (You can name it anything).

Try to do a login with your mysql console and you should be able to see a “Connect” log and a few query logs in the flat file.

Now, the next thing is to find a way to forward these logs over to Alienvault. So you could use NXLOG (we covered that in detailed in a series here) However, that topic has been beaten to death over that series so we won’t look at that option for now.

Or we could re-explore using the HIDS (OSSEC) to capture the flat file which we started doing in this article https://www.pkfavantedge.com/pkf-avant-edge/alienvault-usm-flat-file-log-capture-part-1/ but for some reason, never finished what we started.

So for the sake of brevity, the Part 1 link should be very straightforward to follow. Install OSSEC HIDS into your laptop (which is also your MySQL server), but of course, change the OSSEC config to reflect the proper path and file of the flat file log of MySQL that you just created here.

So the conclusion of it is this:

a) You have a running MySQL server and you are able to query and log to it. I would suggest at this point to install a MySQL gui like HeidiSQL or PHPMyadmin to interact with your database. If you are fine with Workbench or CLI, then go ahead. For me, I like HeidiSQL because its clean, and very useful for simple testing and querying.

b) Your MySQL is logging into a flatfile of your choosing, every single log. Again, since this is a test, it’s fine. For a live server, be aware to have a cleanup script in place to ensure your general query log doesn’t over grow.

c) You have HIDS (OSSEC) – we use both interchangeably, so you know – installed on your laptop server, and it’s configured to pick up the logs from the flat file of MySQL that you have configured

d) On the OSSIM (Alienvault) – we use both interchangeably, so you know – on your Virtualbox, you have enabled HIDS to logall so the raw log is now dumped into archives.log (which we would recommend to remove the logall directive once we finish configuring, since this file will also grow quickly on live environment).

At this point, if you were to open up the
/var/ossec/logs/archives/archives.log of your Alienvault, you would observe that the format of the log coming in is

2021 Feb 22 09:41:42 (Host-192-168-1-111) 192.168.1.111->\MySQLLOG/db.log 2021-02-22T09:41:42.271529Z        28 Query     SHOW CREATE TABLE dbtest.persons

Compared to the log from the database file itself

 2021-02-22T09:41:42.271529Z         28 Query SHOW  
CREATE TABLE dbtest.persons

So it is actually word for word, the actual log itself. That’s great but aside from centralisation, it’s actually not doing anything (sort of like Batman in Justice League). It’s just there, with no purpose.

In our next article (which we will call Flat file log capture part 2), we will break down the structure in which logs flow into Alienvault and how ultimately we can get these logs to eventually be seen in the SIEM event.

Meantime, feel free to drop us an email for any SIEM/Alienvault matters at alienvault@pkfmalaysia.com.

Alienvault: File Integrity Monitoring on Linux Part 1

If you have been deploying or troubleshooting Alienvault long enough, you would know a few things: Alienvault is one of the most flexible SIEMs in the market. It has the most varied security features, and covers almost the entire spectrum of our PCI-DSS needs – from IDS, to SIEM, to File Integrity Monitoring, to vulnerability scaring to a partridge in a pear tree.

One of the products working under the Alienvault hood is OSSEC, which is a opensorce host based IDS. Sometimes, its interchangeable to HIDS, which is Host IDS, but really, the latter is simply the type; while the former is the actual name itself. For the sake of this article, we will interchange both terms.

OSSEC runs well with Windows, where Alienvault can do an auto deployment given the correct setup and credentials. However, it’s on Linux boxes that sometimes we get a bit concerned. Not because the product doesn’t work, but simply because the setting up of the installation. There is no auto deployment, so we need to set it up manually, and this might mean downloading the correct packages in the first place.

After this, we are going to look at a specific function of HIDS – File Integrity Monitoring or FIM for short.

Firstly, let’s get started. We have set up a simple CentOS 7 box in our lab in the same network as Alienvault, and we are going to install HIDS on this box as an AGENT. This will then talk to the Alienvault USM which is the server.

So let’s assume you have your agent system network setup (please ensure your DNS is set properly, you should be able to work this out in CentOS 7 either through the network tools or editing resolv.conf).

 yum groupinstall "Development Tools" -y

The CentOS development tools are very useful tools which is a bundle, used primarily for building and compiling software from source code. “Yum” here while making you think of going for a teh tarik is a command found in almost all red-hat based distros to run installations. It’s used for update, installations etc. In the old days before YUM, we would use RPM (which is really what YUM is using), but we would have to manually track down dependencies and it really sucks because to install an RPM package might mean to install a whole bunch of stupid libraries or updating stuff and you are basically running around the internet looking for RPMs like Where’s Wally. It looks awful now, but back in the days, RPM was heavensent. We didn’t need to do “tar”, configure, make, “make install” anymore!

Anyway, the -y argument behind simply automates the command by answering yes to the prompts. So once you run that, fingers crossed, everything runs ok and you get

Complete!

Which means everything is ok.

The next is to get the kernel-devel package.

yum install kernel-devel -y

This is a package that allows us to install a kernel driver later. It’s not the full kernel source, so it shouldn’t take too long before you see the “complete!”.

At this point you are ready to install OSSEC. If there are any issues, then troubleshooting is obviously required.

First, we need to locate the version of HIDS that can work with Alienvault. You might think heading to the latest HIDS in https://ossec.github.io/downloads.html might be the answer, but for Alienvault, we would recommend to get the 2.8.3 version. You can find it here:

https://bintray.com/ossec/ossec-hids/download_file?file_path=ossec-hids-2.8.3.tar.gz

So, go to a installation directory (optional) like /usr/src and run

curl -OL https://bintray.com/ossec/ossec-hids/download_file?file_path=ossec-hids-2.8.3.tar.gz

We used curl here because for some reason wget wasn’t installed. the -OL is supposed to handle the redirected links for that particular site and supposedly to rename it to a proper remote file name. It doesn’t do the rename though (don’t know?) and we wind up with a file called “download_file?file_path=ossec-hids-2.8.3.tar.gz”. Just rename it if you are into aesthetics to ossec-hids-2.8.3.tar.gz.

So now lets do an extraction

tar –xzvf ossec-hids-2.8.3.tar.gz

We now have a folder called ossec-hids-2.8.3. Go into this folder and then run

./install.sh

Once you run, you will be given a series of questions. Default should be fine for most, and you should just select ‘agent’ and also key in the server (Alienvault) IP address. Now if you are running a separate Alienvault setup (non-AIO), then this IP address is actually the address of your SENSOR. Not Alienvault Server. So don’t get mixed up. The Sensor is the Server. Hm.

So everything ready, fingers crossed, just go ahead and install. There will be a lot of text filling your screen but the important thing is that there is no ERROR or WARNING (well warning ain’t bad), but at the end you should have a welcome note stating

 Thanks for using the OSSEC HIDS.
 If you have any question, suggestion or if you find any bug, contact us 
at contact@ossec.net or using our public maillist at ossec-list@ossec.net 
(http://www.ossec.net/main/support/ ).

Press enter and you should be out of the installation. Congratulations!

You are not done yet. You still need to get Alienvault to talk to your box. The steps are as follows:

a) Generate an Agent Key from Alienvault

Go to your Alienvault AIO or your Server (since a standard sensor has no GUI, remember?).

Environment->Detection->Agents

Click “Add Agent”

Select the host from the list (It should be there automatically, but if it’s not just add it there through the asset list).

So now the agent has been created but you should see it as “Disconnected” from the list. Click the little Key sign that says “Extract Key”.

You should see something like

Agent key information for '2' is:

MiBIb3N0LTE5Mi0xNjgtMC01MCAxOTIuMTY4LjAuNTAgMDBmYzI0MzUyNzg4N.....etc
The garbled message is the key. So go ahead and highlight and copy it.

b) Import the key into the agent system

Go back to your agent system and head over to /var/ossec/bin and run

./manage-agents

Type in ‘I’ to import

Paste the whole key into the screen and confirm adding it.

Quit and then restart by going

/var/ossec/bin

And

./ossec-control restart

c) Restarting HIDS on the server

On the server head over to

Environment->Detection->HIDS Control

On the right side, click “Restart” the HIDS and you should be fine.

d) Check the Agent Logs

Head back to the agent system and check the logs

cd /var/ossec/logs
more ossec.log

You should (hopefully!) see

INFO: Connected to the server (192.168.0.xxx:1514).

where xxx is your server IP address.

Back in the USM server you will be able to see that now the agent is “Active”.

In the next article we will see if we can get the FIM to work.

Deploy HIDS agent in a Checkpoint Environment

avlogo

In our years of deploying and implementing SIEM, if we get 10 ringgit everytime a customer says: “Nothing wrong on our side, your SIEM is the problem” and then after hours of troubleshooting, the customer responds: “Ooops sorry, seems like our problem, we blocked your traffic” – we would all be as rich as Jack Ma by now.

The issue here is not so much that SIEMs are notoriously difficult to deploy – they are not, but its more that other devices need to talk to it. Deploying SIEM is easy, the finetuning is the problem. Because products need to get logs to the SIEM – either through the good old Syslog via UDP/TCP 514 or through agents installed on the systems, or through third party applications like Snare and NXLog – both for the naughty Windows machine where no syslog facility is found for some strange, Bill Gatesy reason.

It’s also because most Administrators guard their systems like pitbulls and any request for them to send logs over is generally greeted by a dumbfounded “Why are you so stupid” look, or “Over my dead body” look, or simply you get ignored completely. Oh, and also the “I want to go home at 5 pm today, so don’t mess with my jam” look. Whatever. The war between Administrators and Implementers has been going on longer than the blood feud between Lycans and Vampires.

We have been through deployments where countless of hours were spent just trying to convince our customer: “We are NOT getting anything on the interface. No packets. It’s being blocked.” And customer: “No, there are no firewalls between.” Five hours later, customer: “Oh yeah, there is a small firewall between and it’s blocking. Cheers”. On our SIEM, it’s easy. If our interface doesn’t see it, it ain’t there. That’s it. Do a tcpdump and grep the IP or port and boom, we know it’s not our problem.

The issue here is that “Not our problem” generally gets translated to “It’s now your problem” and we end up troubleshooting for our clients how to fix THEIR systems and devices. I’ve called my pals at Fortigate, Bluecoat, Cisco, Juniper etc so many times, asking them about issues that my client should be doing it themselves.

So anyway – we had this issue whereby we deployed a fair bit of HIDS (Host IDS, aka OSSEC) agents in a fairly large environment. It basically traverses through a firewall (Checkpoint). That Checkpoint firewall dropped UDP 1514 connection between agent and our AlienVault server. Port 1514 is the port that our HIDS uses to communicate between agent and server.

Firstly, establish whether we are getting those traffic or not in the interface. If we are not, then the problem could be on their end. When we do a tcpdump for udp 1514 on that specific host, we are able to observe some traffic from the server and vice versa. In our case even with that, the connection cannot be established. Bascially, our agent is able to reach the server but the server tries to respond but the traffic disappears. Therefore, the agent is orphaned.

For this case, after troubleshoot and checking, we found out that Checkpoint is dropping the UDP 1514 traffic responses from the alienvault server. So the communication between the HIDS agent and the server is never established. The log shows that UDP traffic is dropped with the following message:

Message_Info: Violated unidirectional connection

Great. UDP traffic is a stateless protocol. According to Checkpoint, by default, a reply to a UDP packet is not allowed. The Security Gateway can mark a connection in the Connections Table to allow traffic to pass only in one direction (hence the term ‘unidirectional’). If a UDP connection uses a bi-directional communication method, this would create a violation.

Therefore, the workaround to this is to allow Checkpoint to respond to this bi-directional communication. It’s pretty straightforward for Checkpoint actually.

You will need to add or edit a service object in Checkpoint. Again, we are Alienvault implementers but we end up mucking through Checkpoint firewalls just to get our job done!

So on a checkpoint edge box, it’s basically

Main Menu->Network->Network Services tab

You can now edit or add.

Go ahead and add a new UDP service, fill in the requirements, and then you will have an option for advanced, click OK.

In the Advanced UDP service property, ensure “Accept Replies” is clicked.

Now go ahead and use this service under the new or existing policy rule that has been set up for Agent->Server communication for HIDS.

Ta-da, done!

If you have any questions on Alienvault USM deployment, drop us a note at alienvault@pkfmalaysia.com.

© 2021 PKF AvantEdge

Up ↑