Status: ✅

My house is cold. I want to start playing with simple electronics before starting Operation Field, so have created Operation Cold House.

This is just simply sticking a temperature sensor onto an Arduino, linking that up to my little home “server”, and uploading that to my website. I’ll display some nifty graphs, too, and link it to Pachube. Stay tuned.

Update: Complete! The proof is in the pudding. I now have a personal website from home (sorry, not public) that displays the daily and weekly temperature at home. The process is basically like this: my little Arduino gathers the temperature, and is polled every minute with a python script via cron. This script then sticks the time and temperature into MySQL. It also exports the temperature to Pachube. Every 30m I have a script that queries MySQL and uses Google’s Chart’s API to graph the temperature. Looks great, I’ll post a graph soon.

After restoring databases from one server to another I sometimes get this error on Ubuntu or Debian:

error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

This makes a lot of sense, and the solution is pretty simple. If you look in:

cat /etc/mysql/debian.cnf

You’ll see the defaults for your system. Copy the password listed there, and open a connection to MySQL as root (or some other user). Next, enter this (lets say your password specified in debian.cnf was ‘abracadabra’:

mysql> select PASSWORD('abracadabra');
+-------------------------------------------+
| PASSWORD('abracadabra')                   |
+-------------------------------------------+
| *38794E19D534EBA4F0F78903FA00F1DA2989DCA2 | 
+-------------------------------------------+
1 row in set (0.00 sec)

Next, since we already have the prompt open, do this command:

mysql> USE mysql;
mysql> UPDATE user SET password='*38794E19D534EBA4F0F78903FA00F1DA2989DCA2' where user='debian-sys-maint';
mysql> FLUSH privileges;

Restart MySQL, and the error should have gone away.

I’ve been side-tracked on another little project, and keep coming back to NetFlow. For this project I’ll need to access NetFlow data with Django, but this is a bit tricky. First, I’m sort of lazy when it comes to my own project; maybe not lazy, I just like taking the most direct route. The most up-to-date NetFlow collector I noticed was flow-tools, and there is even a switch to export the information into MySQL. Sweet! However, I wanted to insert the flows into MySQL automatically, or at least on a regular basis. I first started writing a python script that would do the job, but after a few minutes noticed flow-capture had a rotate_program switch, and started investigating. Since I somehow couldn’t find anywhere instructions how to insert the data automatically, here’s what I came up with:

1. Download flow-tools; make sure to configure with –with-mysql (and you’ll have to make sure you have the needed libraries).
2. Create a new database, I called mine ’netflow'.
3. Create a table that can contain all the netflow fields, a sample is below. I added a “flow_id” field that I used as a primary key, but you don’t necessarily need this.

CREATE TABLE `flows` (
`FLOW_ID` int(32) NOT NULL AUTO_INCREMENT,
`UNIX_SECS` int(32) unsigned NOT NULL default '0',
`UNIX_NSECS` int(32) unsigned NOT NULL default '0',
`SYSUPTIME` int(20) NOT NULL,
`EXADDR` varchar(16) NOT NULL,
`DPKTS` int(32) unsigned NOT NULL default '0',
`DOCTETS` int(32) unsigned NOT NULL default '0',
`FIRST` int(32) unsigned NOT NULL default '0',
`LAST` int(32) unsigned NOT NULL default '0',
`ENGINE_TYPE` int(10) NOT NULL,
`ENGINE_ID` int(15) NOT NULL,
`SRCADDR` varchar(16) NOT NULL default '0',
`DSTADDR` varchar(16) NOT NULL default '0',
`NEXTHOP` varchar(16) NOT NULL default '0',
`INPUT` int(16) unsigned NOT NULL default '0',
`OUTPUT` int(16) unsigned NOT NULL default '0',
`SRCPORT` int(16) unsigned NOT NULL default '0',
`DSTPORT` int(16) unsigned NOT NULL default '0',
`PROT` int(8) unsigned NOT NULL default '0',
`TOS` int(2) NOT NULL,
`TCP_FLAGS` int(8) unsigned NOT NULL default '0',
`SRC_MASK` int(8) unsigned NOT NULL default '0',
`DST_MASK` int(8) unsigned NOT NULL default '0',
`SRC_AS` int(16) unsigned NOT NULL default '0',
`DST_AS` int(16) unsigned NOT NULL default '0',
PRIMARY KEY (FLOW_ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

4. Setup your router so it sends netflow packets to your linux box (see README/INSTALL)
5. Create a “rotate program” that will actually enter in the information into mysql.

kelvin@monitor:/usr/bin$ cat flow-mysql-export 
#!/bin/bash

flow-export -f3 -u "username:password:localhost:3306:netflow:flows" < /flows/router/$1

6. Create the /flows/router directory
7. Start flow-capture (9801 is the port netflow traffic is being directed to); all done.

flow-capture -w /flows/router -E5G 0/0/9801 -R /usr/bin/flow-mysql-export

I rank OSSEC as one of my favorite pieces of open source software, and finally decided to play around with it more in my own free time. (Yup, I do this sort of stuff for fun). My goal was quite simple: send syslog packets from my Cisco to my “proxy” server, running OSSEC. I found that, although OSSEC supports Cisco IOS logging, it didn’t really work. In fact, I couldn’t find any examples or articles of anybody actually getting it to work.

I initially tried to get it to work “correctly,” and soon settled to “just getting it to work.” I implemented some rules in the local_rules.xml file, which worked, but I’m pretty stubborn, and wanted to do it “correctly.” With a couple pots of tea I became much, much more familiar with OSSEC. The key (and a lot of credit) goes to Jeremy Melanson for hinting at some of the updates to the decoder.xml file that need to take place.

The first step is to read the OSSEC + Cisco IOS wiki page. Everything on that page is pretty straight forward. I then added three explicit drop rules at the end of my Cisco’s ACL:

...

access-list 101 deny tcp any host 220.244.xxx.xxx log
access-list 101 deny ip any host 220.244.xxx.xxx log
access-list 101 deny udp any host 220.244.xxx.xxx log

(220.244.xxx.xxx is my WAN IP, and I’m sure you could figure out xxx.xxx pretty darn easily, but I’ll x them out anyways).

To reiterate, OSSEC needs to be told to listen for syslog traffic, which you should be set on the Cisco. If you haven’t done this, go re-read the wiki above.

<remote>
<connection>syslog</connection>
<allowed-ips>192.168.0.1</allowed-ips>
</remote>

On or around line 1550 in /var/ossec/etc/decoder.xml I needed to update the regex that was used to detect the syslog stream.

...

<decoder name="cisco-ios">
<!--<prematch>^%\w+-\d-\w+: </prematch>-->
<prematch>^%\w+-\d-\w+: |^: %\w+-\d-\w+:</prematch>
</decoder>
 
<decoder name="cisco-ios">
<program_name>
<!--<prematch>^%\w+-\d-\w+: </prematch>-->
<prematch>^%\w+-\d-\w+: |^: %\w+-\d-\w+: </prematch>
</program_name></decoder>
 
<decoder name="cisco-ios-acl">
<parent>cisco-ios</parent>
<type>firewall</type>
<prematch>^%SEC-6-IPACCESSLOGP: |^: %SEC-6-IPACCESSLOGP: </prematch>
<regex offset="after_prematch">^list \d+ (\w+) (\w+) </regex>
<regex>(\S+)\((\d+)\) -> (\S+)\((\d+)\),</regex>
<order>action, protocol, srcip, srcport, dstip, dstport</order>
</decoder>


...

In the general OSSEC configuration file, re-order the list of rules. I had to do this because syslog_rules.xml includes a search for “denied”, and that triggers an alarm.

...
<include>telnetd_rules.xml</include>
<include>cisco-ios_rules.xml</include>
<include>syslog_rules.xml</include>
<include>arpwatch_rules.xml</include>
...

Remember that these dropped events will go into /var/ossec/logs/firewall/firewall.log. Because this is my home connection, and I don’t have any active_responses configured (yet!), I tightened the firewall_rules.xml file (lowering the frequency, raising the timeframe).

And in the end, I get a pretty email when somebody tries to port scan me.

Pretty email

OSSEC HIDS Notification.
2008 Nov 15 23:19:36
 
Received From: proxy->xxx.xxx.xxx.xxx
Rule: 4151 fired (level 10) -> "Multiple Firewall drop events from same source."
Portion of the log(s):
 
: %SEC-6-IPACCESSLOGP: list 101 denied tcp 4.79.142.206(36183) -> 220.244.xxx.xxx(244), 1 packet
: %SEC-6-IPACCESSLOGP: list 101 denied tcp 4.79.142.206(36183) -> 220.244.xxx.xxx(253), 1 packet
: %SEC-6-IPACCESSLOGP: list 101 denied tcp 4.79.142.206(36183) -> 220.244.xxx.xxx(243), 1 packet
: %SEC-6-IPACCESSLOGP: list 101 denied tcp 4.79.142.206(36183) -> 220.244.xxx.xxx(254), 1 packet
 
 
 
--END OF NOTIFICATION

Historically I’ve used the ever-so-popular AutoMySQLBackup script.  While it seems to work just fine, I’ve decided to give another solution a whirl.  This solution, provided through Zmanda, seems to be less hackery and more enterprise.  The instructions are very clear, and the backup test went as planned.  Looks  like this is another gem for the toolchest.

I’ve run into this error quite a few times, might as well toss blog entry about it:
ERROR 1064 at line 17: You have an error in your SQL syntax near ‘ENGINE=MyISAM DEFAULT CHARSET=latin1’ at line 7
One likely reason this comes about is because the data being imported/exported is not compatible with the database version. For instance, at home you export the information from a mysql5 database. Then you try to import it on a mysql3.23 database somewhere else – and it fails on you. Bummer.
The solution is quite simple:

 mysqldump --compatible=mysql323 -u root -p database > exportName.sql