White Hot Chocolate, Mathematics and Computer Science

Alienvault and its database

2019-04-05T10:34:00.000+02:00

A few weeks ago, I discovered the hard way that Alienvault 5.7.1 does not clean old events from the database. And like many others, I found out that the patch to fix that, 5.7.2, would not install if there were more than 200 million events in the database.

Ensued a week-long marathon of cleaning the database, erasing files, removing the old events, splitting the queries into several sub queries as the database fought back. Or more precisely, there were so many things to delete that the DELETE queries failed due to the LOCK table being full.

Then I installed 5.7.2 and I thought my ordeal was over. It is not.

A couple of days ago, I got the dreaded email "disk space CRITICAL." Less than 10% disk space is available, which meant I had only a few hours before the system would start running into issues. So, clean-up time it was. Again.

Several people have had the same issue, but I found no information on how to solve the issue for a longer period of time, so I looked into the MySQL database to see if something could be done to avoid the system from crashing every couple of weeks.

Of all the databases, the one that takes the most space is alienvault_siem, and specifically three tables: acid_event, extra_data and po_acid_event. Each weighted more than 70GB on the disk, with extra_data occupying 174GB for about 196 million rows. My limit is set to 80 million and 14 days of retention in Alienvault which we now know is not correctly enforced.

DELETE-ing from a table does not reduce its size: this frees slots for new rows, but the size of the backing file will stay the same. To shrink the file to the minimum, the command OPTIMIZE TABLE is required: due to InnoDB being used, in-place operation is not possible: a temporary table is created, then it replaces the original table. This means that a fair amount of free space is required on the disk as one needs up to twice the size of the table to optimize. It also means that this may run into all kinds of fun problems if the system is really active.

But there was still the issue of locking too many rows at once and having the whole query fails. Time for some SQL.

By experimenting, I found that deleting about 5 million rows at once was fine, so I started by issuing commands such as DELETE FROM extra_data ORDER BY event_id LIMIT 2500000 and repeat it a certain number of times. This had drawbacks, for example I needed to be present to submit more queries once the set of current ones was exhausted. I could have created a source file for that tough, but that was not very dynamic and still required me to get a count of the rows: new rows are added all the time to this table, making 80 million a moving target.

Instead, I opted to use a SQL stored procedure for two reasons: I could set the target in a WHILE condition and, more importantly, I could run it automatically on a schedule to prevent the database to fill up the disk in the first place, and do it consistently without me needing to manually intervene.

Two tables need clean up: extra_data and acid_event. I saw that periodic queries are run that clean po_acid_event, though the way it is done may lead to the query failing to complete if you have more than 10 million rows added over the course of one day. Unfortunately, the WHERE clause is a simple timestamp < ......, and if it fails once, it may start failing consistently, leading again to the disk filling up.

The first procedure removes rows from extra_data until there are less than 85 million rows in the table by blocks of 2.5 million rows. Interestingly, it is faster to delete 2.5 million rows than it is to get the count of all the rows in the table.

DELIMITER $$
DROP PROCEDURE IF EXISTS clean_extra_data$$
CREATE PROCEDURE clean_extra_data()
BEGIN
DECLARE x INT;
DECLARE xtarget INT;
SET x = 0;
SELECT count(event_id) - 85000000 INTO xtarget FROM extra_data;
WHILE xtarget > x DO
DELETE FROM extra_data ORDER BY event_id LIMIT 2500000;
SET x = x + 2500000;
END WHILE;
END $$
DELIMITER ;

The second procedure removes the entries from acid_event by blocks of 2.5 million rows as long as the time the procedure is started minus 16 days is greater than the minimum timestamp present in the database.

DELIMITER $$
DROP PROCEDURE IF EXISTS clean_acid_event$$
CREATE PROCEDURE clean_acid_event()
BEGIN
DECLARE xlimit DATETIME;
DECLARE xmin DATETIME;
SELECT NOW() - INTERVAL 16 DAY INTO xlimit;
SELECT MIN(timestamp) INTO xmin FROM acid_event;
WHILE xlimit > xmin DO
DELETE FROM acid_event WHERE timestamp < xlimit LIMIT 2500000;
SELECT MIN(timestamp) INTO xmin FROM acid_event;
END WHILE;
END $$
DELIMITER ;

Initially, I thought adding the OPTIMIZE TABLE statement to shrink the files. After consideration, this is a potential source of problems: for example I have had MySQL shutting down under very low disk space, and as the OPTIMIZE TABLE query requires loads of additional space, this was just begging for more troubles.

Now, let's schedule these. It should run often enough so the execution of each does not take too long and the backing file does not grow out of control, but not too often to avoid impacting MySQL. I have settled to every Saturday around 10pm: my instance collects a maximum of 5 million events on a normal day, which totals to a maximum of 35 million rows to delete from each table every week. On my system, deleting 2.5 million rows from extra_data takes about 2 minutes, and counting the number of rows in the table around 15 minutes. For 35 million rows, that should run in under an hour. Removing entries from acid_event takes more time, around 10 minutes per block of 2.5 million rows: deleting 35 million rows will take around three hours, still doable.

DELIMITER $$
DROP PROCEDURE IF EXISTS clean_old_data$$
CREATE PROCEDURE clean_old_data()
BEGIN
CALL clean_acid_event()
CALL clean_extra_data()
END $$
DELIMITER ;
CREATE EVENT alienvault_is_worse_than_a_teen
ON SCHEDULE EVERY 1 WEEK
STARTS '2019-04-06 22:00:00'
ENDS '2019-06-30 00:00:00'
DO
CALL clean_old_data()

Lastly, the words of caution: this works for me as a temporary fix until Alienvault proposes a real solution to this problem. I am pretty certain this band-aid may create a lot of other issues down the road such as orphaned entries or events without data. There is no guarantee that this won't break something or won't render your system unusable, you use at your own risks.

Where does XP lurk?

2018-12-31T06:00:00.000+01:00

Recently at a station waiting for a train, a screen showed something different than the usual. Here is the picture.

Good old Windows XP, lurking in the shadows. While this is not likely to be a critical system, this raises the question of how many of these old OS are still out there, running important services such as healthcare, government or financial applications.

The Computer As Crucible

2018-09-10T06:00:00.000+02:00

The Computer as Crucible: An Introduction to Experimental Mathematics by Jonathan M. Borwein
My rating: 4 of 5 stars

Mathematics is that domain of science that is usually thought to be purely of the mind, but increasingly computers are helping mathematicians by identifying sequences, providing insights into complex systems, assisting with proofs and much more. This book gives a small peek into how the machines are, nowadays, been used and how.

Written in a clear style with numerous examples, the book reads easily and quickly and makes frequent references to other publications, often written by one of or both the authors. In addition, each chapter ends with a few exercises, some of which I have found to be rather challenging.

While this is probably not a book you will read to get a good grasp on "computer assisted mathematics", it is an interesting introduction to it.

View all my reviews

Spammer can ruin my life! Or can he now?

2018-05-01T06:00:00.000+02:00

Recently, I got a message from a spammer (order (at) creativegrowers.com) who claimed he or she could ruin my life. Here is the message for your enjoyment.

Good day...

Do not regard on my English, Im from Japan.I installed the virus onto your device.At present I thiefted all privy background from your device. Furthermore I got some more evidence.The most important evidence that I received- its a videotape with your self-abusing.I put virus on a porn web site and after you downloaded it. As soon as you picked the video and clicked on a play, my malware at once set up on your system.
After adjusting, your web camera made the video with you wanking, additionally software captured exactly the video you chose. In next few days my deleterious soft found all your social media and email contacts.

If you need to delete the records- pay me 540 usd in BTC(cryptocurrency).

I provide you my Bitcoin wallet address - 1Ph5bArH1nN2HVKLLnKE3UB4ZstoEb8Gfc

You have 24 h. to go after reading. As soon as I receive transfer I will destroy the compromising forever. Other way I will send the video to all your colleagues and friends.

The source of the message is 167.99.44.161, which is on Digital Ocean. Furthermore, the message headers tend to indicate that the site "creativegrowers[.]com" got compromised and is spewing spam.

Blockchain.info shows that at least one payment was made to that address, for a total of 0.03 BTC on 2018-04-30, which represents a bit short of $300. Has someone fell for that scam? Is this something else? Anyway, I will keep an eye on the transaction to and from that address.

Alienvault and Squid-Access logs

2018-04-12T06:00:00.000+02:00

While playing with OSSIM and Squid, I found that the logs were not processed: though they were correctly received by the sensor and they appeared in /var/log/alienvault/agent/agent.log, I did not see any event being created or appearing in the server.

Whenever ossim-agent was restarted on the sensor, a message appeared in /var/log/alienvault/agent/agent.log at the first event received. This message ended with "Plugin sid not a number". In the line above that one, which contained the event as parsed by ossim-agent, indeed the plugin sid value was "TCP_TUNNEL".

In /etc/ossim/agent/plugins/squid.cfg, there is a translation table between the status (TCP_HIT, TCP_MISS, ...) and a numerical value. This translation does not exist for TCP_TUNNEL.

After adding it with the next available value to the translation table in squid.cfg and restarting the agent, the TCP_TUNNEL events generated by Squid appear as "Generic event" in Alienvault OSSIM. The rest of the data (source IP, destination IP, hostname et al.)

The same happened with the message TAG_NONE. Adding it to the corresponding plugin fixed the issue.

Landscape

2018-01-18T19:06:00.001+01:00

Brand Luther (book)

2018-01-18T12:04:00.001+01:00

Here is a review by Jean: https://www.goodreads.com/review/show/2137840523

Web Application Hacker's Handbook

2017-05-14T11:49:00.001+02:00

Here is a review by Jean: https://www.goodreads.com/review/show/1896919107

Machine Learning, Donald Trump and Reddit

2017-03-27T06:00:00.000+02:00

An excellent article by Trevor Martin on using Latent Semantic Analysis on the Reddit r/The_Donald comments.

(Source: Data Elixir)

My review of "Le Dé d'Einstein et le Chat de Schrödinger: Quand Deux Génies s'Affrontent"

2017-01-31T06:30:00.000+01:00

Le dé d'Einstein et le chat de Schrödinger : Quand deux génies s'affrontent by Paul Halpern
My rating: 4 of 5 stars

Einstein is known as the father of Relativity, as well as of the famous formula that links mass to energy. Schrödinger is known for his cat "thought experiment" and for his wave equation. But did you know that Einstein was also one of the fathers of quantum mechanics? And that both men looked at finding an equation to unify gravity and electromagnetism?

This book is not a biography of either men, though aspects of their lives are presented. It presents how both collaborated, how their friendship fell and how they resumed their collaboration, as well as how their beliefs fitted the evolving physics.

View all my reviews

My first Kaggle submission - Titanic Survival Rate

2017-01-16T06:30:00.000+01:00

If you don't not it yet, Kaggle is a platform that organizes data-oriented competition. There are small and large sets of data, and there are competitions for fun, for swag or for money. If you are interested in data-science, wanting to learn or to improve, or simply looking for programming challenges, this is a good place to start.

The competitions can originate from a specific requester, but can also be more general: for example there is currently a competition to improve lung cancer detection. There are also introductory competitions meant for beginners.

When I registered on Kaggle, five years ago, this was to access some large datasets to play with in R, but I have not tried any competition.

I made my first submission in the Titanic: Machine Learning from Disaster competition. This is one of the competitions for learning (no ranking, no swag, no money). In it, you are presented with a training dataset that contains several features as well as if the person survived or not. The goal is to build a model that will predict in the testing set whether the person survived or died, based on the same feature.

My notebook is publicly available here. Ultimately I opted for Python instead of R as I wanted to play with pandas, numpy and scikit-learn. Pandas is a library providing data frames to Python (among other), numpy an octave-like interface, structures and functions, and scikit-learn various algorithms revolving around machine learning, be them machine learning (kNN, SVM ...), preprocessing or encoders.

I am quite pleased as my refined model has an accuracy of around 80%. Some of the missing data are imputed using a random process, so the repeated accuracy can change.

Feel free to comment on it!

What did I learn?

A lot! But more specifically ...

Exploration and visualization are important

This was actually my biggest mistake: I started the competition without really looking at the data, neither exploring it nor visualizing it. As a result, my first models' accuracy were below 75%. Looking at various examples and blogs, I saw people splitting the data and making it tell as much as possible, revealing in the process which features were important and which ones had less significance.

Get to know the context

For example, in 1912, during sea accidents, the principle of "women and children first" was still in place, which reflects in the data with a survival rate higher for women and children than for adult male.

Another example is looking at the ship's plans: the second and third classes were far from the level with the lifeboat, which may also account for the lower survival rate of these populations. This could also have been provided by the cabin number, which started with the deck letter. Unfortunately that information was seldom provided in the dataset.

Understand your tools, and shun away from "easy" simplicity

Initially, I used a LabelEncoder from sklearn.preprocessing, which translates categorical variable into an integer per category, for example "Black","White","Green","Black","Green" would become 0,1,2,0,2. This has the advantage of being quick, but unfortunately, this also makes possible things such as computing the average of "Black" and "Green" as "White", which makes no sense. I switched to a simple dummy encoding, i.e. converting all the factors to a 0/1 variable, which improved the accuracy.

Linux, GPU, games and Google Chrome

2016-12-26T07:00:00.000+01:00

Recently, I have been having a few issues with a few games: once in a while, I like to play a bit of FPS to de-stress, and my frame rate was just abysmally low, what used to be a good 60 FPS went down to 20-30, leaving me with barely playable games, although these were great under Linux Mint 17.

After a bit of searching, I have found something interesting: if Google Chrome is running, the frame rate will be bad. If it is not, my games are back to normal. As Google Chrome uses the GPU for various tasks, I guess this was either due to a conflict (the two applications fighting for GPU resources) or Google Chrome setting some parameters that are detrimental to the games. Looking at Google Chrome's GPU status shows that a few features are either disabled or not available.

Upgrade from Mint 18 to Mint 18.1

2016-12-20T07:00:00.000+01:00

A few days ago, there were news that Mint 18.1 was out and ready for install. This morning, my update manager prompted me to install the MintUpdate package, a sign that the new version is ready for prime time.

The release notes do not show anything that would affect me, and so far, so good.

The overall feeling is that nothing really changed: visually this is still the same, the system does not seem to be faster or slower.

A good point: the upgrade did not remove my PPA and other additional depots. It was one of the many little things that made me cringe during the update from 17 to 18. It is not very hard to put back (all the information is in ~/Upgrade-Backup/APT/sources.list.d) though.

Comment if you have had any issue.

Pairi Daiza

2016-09-26T10:15:00.002+02:00

Here are some pictures from my walk in Pairi Daiza.

From Linux Mint 17.3 to 18

2016-09-22T08:00:00.000+02:00

Linux Mint 18 has been out for the last few months, and I finally found the time to upgrade my computer.

The upgrade

As suggested, I started by using the Mint 18 ISO as a live USB, simply to make sure that every piece of equipment was supported. My biggest concern was my video card (NVIDIA GeForce 9600 GT, purchased eight years ago). The Live USB went without a hitch and the nouveau driver perfectly managed the card. However, I did not plan to use it: I had too much issues under Fedora and Linux Mint 17.2. Though it was good to know that, if need be, I could use that driver while shopping for a new video card.

The test with the Live USB being okay, I proceeded with the instructions provided by the Linux Mint team. Starting with the "take a backup." That step is often overlooked, but I really recommend it, especially that a TB external hard drive costs less than 100€. As a matter of fact, I have the habit of taking a weekly backup - usually on Friday evening, and whenever I import pictures from my camera.

The check phase went fine, then the download - which I let run overnight. In the morning, I only had to go for the mintupgrade upgrade command, which performs the actual upgrade.

There, things were a bit less clear cut: several errors and tracebacks appeared related to mono, but it seems okay and can be ignored. However, when the process finished, several packages were reported has not being upgraded due to errors. I reran the upgrade process and the same result appeared. Here are the packages that were not updated:

cron
cups-browsed
cups-daemon
samba
rsyslog
ubuntu-minimal
irqbalance
acpid
avahi-daemon
avahi-utils
bluez
bluetooth
cups-core-drivers
cups
printer-driver-hpcups
hplip
printer-driver-postscript-hp
bluez-cups
gnome-bluetooth
gnome-user-share
libnss-mdns:amd64
nvidia-340
nvidia-340-uvm
printer-driver-gutenprint
printer-driver-splix
pulseaudio-module-bluetooth

Looking at the messages, it was pretty much the same for each: either the service could not be/was not restarted and the dpkg --configure failed (for example bluez), or the package depended on such a package (for example bluetooth). I manually ran the corresponding service xx stop / dpkg --configure and everything went fine.

Lastly, when I restarted, I had an issue when I logged in, with the following error message, before disconnecting me.

GLib-CRITICAL: g_key_file_free: assertion 'key_file != NULL' failed

After a few searches, it seems this is a known issue, and a "sudo apt-get purge cinnamon nemo && sudo apt-get install cinnamon" at the console later, I was back in business.

The first half-day

So far, so good. After the first restart, I reapplied the intel-microcode proprietary driver, and I re-added all my repositories and PPA (Google Chrome, Julia, Sagemath and Darktable), which were lost during the upgrade. This is not a major issue, and this was quickly corrected, but a minor annoyance, especially if you have a lot of PPAs and repositories. There is also a pro in not porting over the PPAs: some of the applications may be linked against the older versions of the libraries and might not work anymore after the upgrade, possibly resulting in broken dependencies and other errors.

I had to reboot once, to apply both the microcode driver, but also a kernel update that popped up and was not taken during the upgrade process. Not too bad.

Visually, this version is very clear, and the Mint-X theme is very readable. It is pleasant and easy on the eyes, and while this is something that never struck me as an issue with the 17.3, going to 18 makes a ton of difference.

During my first use, I was surprised: the active application appears highlighted in green in the task bar, which I thought was to request the user's attention. After a minute or so, I got used to it.

From a performance standpoint, I feel it is about the same as my previous Linux Mint 17.3 install. However, my machine is about eight years old and probably not the snappiest thing on earth.

The aim of my upgrading was to be able to install some more recent applications, especially Julia and Jupyter. For the former, I opted to re-install the PPA instead. For Jupyter, unfortunately, still nothing in the official package. PIP install it is then.

Overall

Everything considered, I am very pleased: while there were a few hiccups, the upgrade went without any major issues and the few kinks I had after reboot were easily fixed. This new version is way clearer and visually easier on the eyes.

The upgrade process is still a bit too chaotic, and the hiccups along the way can be issues for people new to Linux. It is to be noted that upgrading a live system is not the way recommended by the Mint team, which favors using a fresh install.

MIT Technology Review : Cyber-Espionage Nightmare

2016-08-22T06:00:00.000+02:00

A very good article by the MIT Technology Review discussing a cyber attack from one of the most active cyber attack unit in China.

Cyber Espionage Nightmare

Jean Ray, "la cité de l'indicible peur"

2016-07-04T06:45:00.001+02:00

Here is a review by Jean: http://www.goodreads.com/review/show/1685894535

Wolfram's "A New Kind of Science"

2016-07-02T19:29:00.001+02:00

My review of the book on GoodReads.

Martina's new book, Popletená Móda

2016-06-25T10:15:00.001+02:00

Something a bit different than my usual - My friend Martina has started a HitHit (the CZ Kickstarter) to publish a book on knitting. Feel free to lend a hand here.

2016-06-14T05:42:00.001+02:00

Here is a review by Jean: http://www.goodreads.com/review/show/1643442046

24 of the most mesmerizing machines

2016-03-16T07:00:00.000+01:00

Just for the fun. Some of them are really hypnotic.

TED 2005 - How juries are fooled by statistics - Peter Donnelly

2016-03-09T08:00:00.000+01:00

An oldie but still goodie - Oxford mathematician Peter Donnelly explains why some juries are making mistakes when deciding what the probability is for an event to happen.

There was also an article in the NYTimes about this subject.

BruCon 0x07 - Unified DNS View to Track Threats - Dhia Mahjoub & Thomas Mathew

2016-03-02T08:00:00.000+01:00

A very good talk on some of the techniques used by OpenDNS.

Usernix Enigma 2016 - Hacking Health: Security in Healthcare IT Systems

2016-02-24T08:00:00.000+01:00

A great talk by Avi Rubin - slightly sarcastic at times - on the IT security in the healthcare industry, a particularly challenging industry when it comes to security. While it has become very good at safety, security is still lagging behind. Avi even mentions an argument I was opposed once when doing a security assessment for a hospital: "if it delays us by 5 seconds, it will cost lives."

Usenix Enigma 2016 - NSA/TAO Chief on Disrupting Nation State Hackers

2016-02-17T08:00:00.000+01:00

This is not often that the chief of one of the biggest exploitation team from one of the biggest intelligence agency in the whole world gives a talk about his job, and more specifically about how to make his team's job harder.

Do not expect any ground shaking revelations: everything he says have been said before. But private companies and government agencies are still failing to implement the tools and steps he talks about.