Grafana and InfluxDB quickstart on Ubuntu

This quickstart assumes you know how to install Ubuntu and access it through a terminal (command line).

EDIT: There seems to be a bug with the latest stable version of Grafana (2.6) and running on a headless system such as the one I’m describing here (Ubuntu Server). This bug causes icons not to be displayed in the web interface. I would suggest either using Ubuntu Desktop (I have not tested this myself) or using Grafana 3 which does not have this bug (I tested this and it does work on Ubuntu Server 16.04. I’ve updated the Grafana installation instructions to include version 3 as well).

Ubuntu Configuration

I started with a new installation of Ubuntu Server 16.04 LTS 64bit. During installation, when prompted for which predefined collections of software to install, only “standard system utilities” and “OpenSSH server” were selected.

grafana_influxdb_ubuntu_install

After a clean installation, run:

sudo apt-get update

and

sudo apt-get upgrade

grafana_influxdb_ubuntu_configure

Install InfluxDB

The instructions below are based on the official documentation which can be found here: https://docs.influxdata.com/influxdb/v0.12/introduction/installation/

First, configure the package sources.

Make sure you’ve previously used sudo in the current session, so that the sudo in the command below does not prompt you for a password again. To make sure, you can just run “sudo ls” and check that it doesn’t prompt for a password.

If you copy and paste this, make sure it is three separate lines.

curl -sL https://repos.influxdata.com/influxdb.key | sudo apt-key add -
source /etc/lsb-release
echo "deb https://repos.influxdata.com/${DISTRIB_ID,,} ${DISTRIB_CODENAME} stable" | sudo tee /etc/apt/sources.list.d/influxdb.list

grafana_influxdb_ubuntu_influxdb1

This will create a file called “/etc/apt/sources.list.d/influxdb.list” if it worked.

Then install InfluxDB:

sudo apt-get update && sudo apt-get install influxdb

grafana_influxdb_ubuntu_influxdb2

Start InfluxDB. This command produces no output.

sudo service influxdb start

Connect to InfluxDB using the commandline

influx

grafana_influxdb_ubuntu_influxdb3

Create a database. For this quickstart we’ll call the database “statsdemo“. Run this command inside the InfluxDB shell.

CREATE DATABASE statsdemo

This command produces no output, but when you list the database, you should see that it was created:

SHOW DATABASES

grafana_influxdb_ubuntu_influxdb4

Select the new created database:

USE statsdemo

grafana_influxdb_ubuntu_influxdb5

Insert some test data using the following command.

INSERT cpu,host=serverA value=0.64

More information about inserting data can be found here: https://docs.influxdata.com/influxdb/v0.12/guides/writing_data/

The insert command does not produce any output, but you should see your data when you perform a query:

SELECT * from cpu

grafana_influxdb_ubuntu_influxdb6

Type “exit” to leave the InfluxDB shell and return to the Linux shell.

exit

Writing test data

To have some data to play with we need to write something to the database.

Open another terminal and run this one line script. It will continue running until you press CTRL-C and write the current load average of your Ubuntu server every 5 seconds to the database, using the HTTP API of InfluxDB.

curl -i -XPOST 'http://localhost:8086/write?db=statsdemo' --data-binary 'cpu,host=serverA value=`cat /proc/loadavg | cut -f1 -d" "`'

It will print out something every 5 seconds. Just let it run and collect some data while you do the next steps.

grafana_influxdb_ubuntu_data

You can verify if data is being sent to InfluxDB by using the influx shell and running a query. Note that if your server is not very busy, you’ll see a lot of zeros being logged for the load average!

influx
USE statsdemo
SELECT * FROM cpu

grafana_influxdb_ubuntu_data2

Install Grafana

The instructions below are based on the official documentation, available here: http://docs.grafana.org/installation/debian/

Configure package sources. You can either install the latest stable version (2.6 currently), or the Beta/Testing version (3.0 beta currently).

Due to a bug with 2.6 and Ubuntu 16.04 Server, I recommend using the beta version if you’re using Ubuntu Server in a headless configuration (No X).

For the current Stable version (2.6), use:

echo "deb https://packagecloud.io/grafana/stable/debian/ wheezy main" | sudo tee /etc/apt/sources.list.d/grafana.list
curl https://packagecloud.io/gpg.key | sudo apt-key add -

For the beta version (3.0) use:

echo "deb https://packagecloud.io/grafana/testing/debian/ wheezy main" | sudo tee /etc/apt/sources.list.d/grafana.list
curl https://packagecloud.io/gpg.key | sudo apt-key add -

grafana_influxdb_ubuntu_grafana1

Update package repositories and install Grafana:

sudo apt-get update && sudo apt-get install grafana

grafana_influxdb_ubuntu_grafana2

Start the grafana server:

sudo service grafana-server start

Configure Grafana

These instructions are for Grafana 2.6, but if you installed the beta version it is very similar.

Then use a webbrowser to connect to grafana, using the hostname or IP of your Ubuntu server and port 3000. Log in with admin/admin:

http://statsdemo:3000/

grafana_influxdb_ubuntu_grafana3

After logging in. click on Data Sources in the left menu, and then on Add New in the top menu to add a new datasource.

Choose the following options and click Add. Note: If you’re using Grafana 3.0, the Type will just be “InfluxDB”

Name: statsdemo
Type: InfluxDB 0.9.x
Url: http://localhost:8086/
Database: statsdemo
User: admin
Password: admin

After adding the datasource you will get a Test Connection button at the bottom, which you can use to verify if your settings are correct.

grafana_influxdb_ubuntu_grafana4

Create a Dashboard

Click on the Dashboards link in the left menu, then the Home menu in the top to get a list of dashboards. Click the New button at the bottom to create a new dashboard.

grafana_influxdb_ubuntu_grafana5

You will get a page with a green rectangle which expands if you hover your mouse cursor over it. If you click it it opens a submenu. Select Add Panel -> Graph from this menu.

grafana_influxdb_ubuntu_grafana6

A sample panel with a graph will appear. With Metrics selected (it should be selected by default), there is a dropdown where you can choose a datasource. Choose statsdemo. A query will be displayed. Click on ‘select measurement’ to choose a measurement from the database. In this case the only one there is the sample data we inserted, called cpu.

As soon as you select the measurement Grafana will pull the data from InfluxDB and update the graph.

grafana_influxdb_ubuntu_grafana7

Go to the General tab, and give the panel a better name, for example “CPU Load Average”, and then save the dashboard by clicking on the Save Dashboard icon next to “New dashboard”. (Note there’s a bug in my installation, and the icon does not display, however, if I hover the mouse icon there I get a tooltip saying ‘Save Dashboard).

grafana_influxdb_ubuntu_grafana8

That’s it for now. This dashboard will continue updating as new data is written to InfluxDB.

Solderless method to connect ESP-12 module to breadboard

The ESP8266 ESP-12 module has a 2mm pitch, making it difficult to use on a breadboard with a 2.54mm pitch. Usually you are required to solder the module to a breakout board first.

This is just a quick ‘hack’ to connect the module to a breadboard for some testing without soldering it to a breakout board first.

The cheap prototyping jumper wires you find on Ebay, the ones with the round pins, fit through the holes in the ESP-12 module (probably some others too). Because the holes are through-hole plated the jumper pins make contact, even if they’re a bit loose.

Now all you need is a way to secure them so they don’t all fall out. For this, a piece of foam works great! Just make sure you use non conductive foam. Some IC’s are shipped in conductive foam, so test the resistance of the foam with your multimeter first!

I find this is a quick way to test new modules before soldering them into a more permanent project.

solderless_esp12_breakout

Raspberry Pi Pinout Diagram

Pinouts for the Raspberry Pi P1 header, showing pins that can be used for general purpose IO. I used some online sources to get the naming for the pins (source list at the bottom of this post). Some of the power and ground pins were initially marked as ‘do not connect’, but it has now been confirmed that these pins won’t change so I included them below as well (Pins 4, 9, 14, 17, 20 and 25).

A few pins changed between Revision 1 and Revision 2 of the board (Pins 3, 5 and 13). The pin description in the table below shows first the Revision 1 GPIO number followed by a slash and then the Revision 2 GPIO number.

Raspberry Pi Pinout Diagram
Raspberry Pi Pinout Diagram showing P1 header orientation

 

Name Pin Pin Name
3.3 V 1 orange red 2 5 V
GPIO 0 / 2 I2C SDA 3 cyan red 4 5 V
GPIO 1 / 3 I2C SCL 5 cyan black 6 GND
GPIO 4 7 green yellow 8 GPIO 14 UART TXD
GND 9 black yellow 10 GPIO 15 UART RXD
GPIO 17 11 green green 12 GPIO 18
GPIO 21 / 27 13 green black 14 GND
GPIO 22 15 green green 16 GPIO 23
3.3 V 17 orange green 18 GPIO 24
GPIO 10 SPI MOSI 19 magenta black 20 GND
GPIO 9 SPI MISO 21 magenta green 22 GPIO 25
GPIO 11 SPI SCLK 23 magenta magenta 24 GPIO 8 SPI CE0
GND 25 black magenta 26 GPIO 7 SPI CE1

If you would like to use the graphics in the diagram you can download the source vector file in SVG format from GitHub.

Sources used to create this post:

Raspberry Pi and Arduino via GPIO UART

In an attempt to get my Raspberry Pi talking to my Arduino I’m exploring various different options. The first was to just use the USB connection, but that was too simple. So, here is how to connect the two using the UART on the GPIO pins of the Raspberry Pi.

To make testing easier I wanted to keep the Arduino’s serial connected via USB to my PC so I can print messages there and read it with the Serial Monitor. This meant using the SoftSerial library to implement a second serial port to talk to the Raspberry Pi. To protect my Raspberry Pi and to convert the 5V of the Arduino to 3.3V the Raspberry Pi needs I used a CD4050.

To show how this works the Arduino is running a small program that reads from the Raspberry Pi’s and copies this to my PC via USB.

By default the Raspberry Pi uses the UART in two ways:

  1. Console Messages (including bootup messages)
  2. A getty so you can login via serial

To use this serial port for your own uses you need to disable these two services. I decided to leave them enabled to test how well the serial connection works. However, the SoftSerial library on the Arduino doesn’t work so well at the default baudrate of 115200, so I changed this on the Raspberry Pi to 9600:

To change the console baudrate, edit /boot/cmdline.txt to look like this (this is all one line):

dwc_otg.lpm_enable=0 console=ttyAMA0,9600 kgdboc=ttyAMA0,9600 console=tty1 root=/dev/mmcblk0p2 rootfstype=ext4 rootwait

Also, edit /etc/inittab to change the baudrate of the getty (you should fine a line like this with the baudrate of 115200, change that number to 9600):

2:23:respawn:/sbin/getty -L ttyAMA0 9600 vt100

Ok, now upload the program to your Arduino:

 

/*

 Connects Arduino to Raspberry Pi
 Arduino: SoftSerial
 Raspberry Pi: GPIO UART

 This is just a simple passthrough, based on Arduino SoftSerial example

 */
#include <SoftwareSerial.h>

SoftwareSerial mySerial(2, 3); // RX, TX

void setup()  
{
 // Open serial communications to PC and wait for port to open:
  Serial.begin(57600);
   while (!Serial) {
    ; // wait for serial port to connect. Needed for Leonardo only
  }

  Serial.println("Connected to PC");

  // set the data rate for the SoftwareSerial port to Raspberry Pi
  mySerial.begin(9600);
}

void loop() // run over and over
{
  // If data is available on Raspberry Pi, print it to PC
  if (mySerial.available())
    Serial.write(mySerial.read());
  // If data is available on PC, print it to Raspberry Pi
  if (Serial.available())
    mySerial.write(Serial.read());
}

Connect the Arduino to the Raspberry Pi:

Raspberry Pi Pins used:

  • 1 : 3.3V
  • 6 : Ground
  • 8 : UART TXD
  • 10: UART RXD
CD4050 Pins used:

  • 1 : VDD (3.3V)
  • 2 : Output A
  • 3 : Input A
  • 4 : Output B
  • 5 : Input B
  • 8 : VSS (Ground)

If you then open your Serial Monitor on you PC, and set the baudrate to 57600 you should get the following message:

Connected to PC

If you then reboot your Raspberry Pi, the console messages should be echoed back to the Serial Monitor running your PC and leave you with a login prompt from the getty:

If you change ‘No Line Ending’ to ‘Newline’ you can enter your username and are then prompted for a password. Unfortunately the password doesn’t work. I still need to figure out why it’s not accepting my password. I suspect it’s to do with a line termination character or something.

Next step to connect this with a Serial library on the Raspberry Pi to talk to the Arduino. Maybe using pySerial like Dr Monk did in his Raspberry Pi and Arduino project. You should be able to use this in exactly the same way after disabling the console and getty and using ttyAMA0 as the serial port.

 

Arduino Web Enabled Aquarium Controller

This is a project I started a few months ago, but never finished. I got distracted by the Raspberry Pi :). Even though it is unfinished maybe someone could get some value out of the current state of the project.

The idea was to create a controller for an aquarium, to measure temperature, measure water level and to turn pumps, heaters and lights on/off based on measurements and times. And, to have a web enabled interface to the controller.

In it’s current state the controller works with DS18B20 temperature sensors and displays the temperature on a webpage. The web server runs on the arduino and uses a combination of generated html and static html stored on an SD card in the ethernet shield.

Scroll down to the bottom of this post to find the download link for the project.

Hardware used:

  • Arduino Mega2560
  • Arduino Ethernet Shield
  • DS18B20 temperature sensors

I also have the following hardware, but never got around to adding to the project:

  • DS1307 Real Time Clock
  • Relay modules

Arduino Aquarium Hardware

And here are a few screenshots of the program running on the arduino with 5 temperature sensors. The temperature sensors all connect to the same pin, and the ‘Scan’ button scans for probes and sets their resolution. The ‘Temperature’ button then reports the temperature for each of the probes.

And this page shows that other dynamic data can also be added. In this case it shows the current values of the Analog input pins.

To build this project you will need the Arduino Mega2560 (an Uno might work, have not tested that), Ethernet Shield, MicroSD card, one or more DS18B20 temperature sensors and a 5K resistor.

You can download the Arduino project here:

Also included in the download is the stylesheet. On your SD card (which goes into your Ethernet Shield), create a www directory and place the style.css file in there.

This project started out on the Marine Aquarium South Africa (MASA) forums, you might be able to pick up some more information in the thread over there: http://www.marineaquariumsa.com/showthread.php?t=32489

Ordering a Raspberry Pi

I’ve had a few people ask me how I ordered my Raspberry Pi and how long it took for me to get it. So here is the process I followed, including a time line.

I live in Cape Town, South Africa and the whole process took about 4 months from registration to delivery.

1 March 2012 : Registration

I registered my interest on the RS Components website ( http://uk.rs-online.com/web/generalDisplay.html?id=raspberrypi ) and received an email confirming my registration:

15 March 2012 : Update

I received an update from RS Components that they’ve had a ‘manufacturing hiccup’ but not to worry, and that invitation will still be sent out on a first-come, first served basis based on when I registered.

8 May 2012 : Update #2

I receive another update from RS Components. They say that they’ve invited the next 4000 people in the queue to order their Raspberry Pi’s and that the second batch of boards are on their way.

28 May 2012 : Time to order

I get an email from RS Components saying I can now place my order. This takes me to a webpage where I can enter my activation code included in the email and allows me to order the Raspberry Pi and pay for it.

I received an order confirmation back, with an expected dispatch of 4 weeks:

27 June 2012 : DHL Shipment Notification

I receive an email from DHL indicating that my order has shipped with DHL Express, including a tracking number and tracking URL for my package.

DHL Tracking page shows me when the package leaves from London to arrive in Cape Town:

29 June 2012 : Raspberry Package arrives

Finally, my Raspberry Pi arrives!

It was in a bubble envelope package, containing a small cardboard box:

And the cardboard box contained the Raspberri Pi inside an anti-static bag, wedged between two pieces of foam and two pamphlets:

Update (2012-07-17) : The one per customer limit has been raised, and you can now order as many as you like from this website: http://authenticate.rsdelivers.com/ when I had a look today the wait time was 11 weeks for delivery.

 Raspberry Pi is a trademark of the Raspberry Pi Foundation

Creating a dynamic image with PHP

To create a dynamic image is easy with PHP and the GD library. Its also a fun way to make your forum banner stand out by having it change each time someone loads it.

For example, I used to have the following forum signature while I was playing EverQuest II:

Forum Signature
Forum Signature

The image is created by a PHP page and the line at the bottom is randomly chosen from a list. Each time this page is reloaded, the message changes. The list of messages is actually from EverQuest I, messages that was shown in-game when you were zoning.

My example only uses text, but you can of course use graphics too. You could create a base image in a paint package and then use PHP to load that image and use it as a canvas to drop on instead of starting out with a blank background.

Here is the PHP I used to create this image:

<?php

# Andre Miller
# http://www.andremiller.net/
#

$v = array(
"Adding Randomly Mispeled Words Into Text...",
"Adding Vanilla Flavor to Ice Giants...",
"Always Frisky Kerrans <AFK>...",
"Attaching beards to dwarves...",
"Buy:LurN Tu Tok liK Da OgUr iN Ayt DaYz by OG...",
"Checking Anti-Camp Radius...",
"Creating randomly generated feature...",
"DING!...",
"Does Anyone Actually Read This?...",
"Doing things you dont wanna know about...",
"Dusting off spell books...",
"Ensuring Everything Works Perfektly...",
"Ensuring Gnomes are still short...",
"Filling Halflings With Pie...",
"Have You Hugged An Iksar Today?...",
"Have You Tried Batwing Crunchies Cereal?...",
"Hiding Catnip From Vah Shir...",
"Hitting Your Keyboard Won't Make This Faster...",
"If You Squeeze Dark Elves You Don't Get Wine...",
"Karnors..... over 40 billion trains served...",
"Loading, Don't Wait If You Don't Want To...",
"Loading useless information...",
"Loading velious textures...",
"Looking For Graphics <LFG>...",
"Looking Up Barbarian Kilts...",
"Look Out!!  Behind You...",
"Now Spawning Fippy_Darkpaw_432,326,312...",
"Oiling Clockworks...",
"Polishing erudite foreheads...",
"Preparing to Spin You Around Rapidly...",
"Refreshing Death Touch Ammunition...",
"Ruining My Own Lands...",
"Sanding Wood Elves - Now 37% smoother...",
"Searching High Elf Robes...",
"Sharpening Claws...",
"Sharpening Swords...",
"Spawning Your_Characters01...",
"Starching High Elf Robes...",
"Stringing Bows...",
"Stupidificationing Ogres...",
"Teaching Snakes to Kick...",
"Told You It Wasn't Made of Cheese...",
"Warning: Half Elves Are Now .49999 Elves...",
"Whacking Trolls With Ugly Stick...",
"You Have Gotten Better At Loading! (8)..."
);

$message = $v[rand(0, sizeof($v)-1)];
header ("Content-type: image/png");
$img_handle = ImageCreate (350, 60) or die ("Cannot Create image");
$back_color = ImageColorAllocate ($img_handle, 255, 255, 255);
imagecolortransparent ($img_handle, $back_color);
$color_1 = ImageColorAllocate ($img_handle, 255, 102, 51);
$color_2 = ImageColorAllocate ($img_handle, 1, 1, 1);
$color_3 = ImageColorAllocate ($img_handle, 51, 102, 255);
ImageLine($img_handle, 0, 1, 250, 1, $color_2);
ImageString ($img_handle, 2, 0,2, "Dalden - 70 Illusionist / 70 Provisioner", $color_1);
ImageString ($img_handle, 2, 0,12, "Leader of Aenigma (Guild level 49)", $color_1);
ImageString ($img_handle, 2, 0,22, "Euro-time guild on Crushbone", $color_1);
ImageString ($img_handle, 31, 0,35, "Loading Please Wait...", $color_2);
ImageString ($img_handle, 0, 0, 50, $message, $color_3);
ImagePng ($img_handle);
?>

To keep the image transparent, just make sure you don’t use $back_color for any of the text.

Save this php script on your server and use it as the URL of an image.

For example: <img src=”http://www.andremiller.net/projects/dynamic-php-image/dalden_banner.php”>

This will show up as an image in your browser:

Forum Signature
Forum Signature

Manual patching of EverQuest II

(Update, as pointed out in a comment below, since the new launcher, this method no longer works)

I don’t play EverQuest II anymore, but figured maybe someone else might find this useful as well.

Sometimes when using the patcher the download might get stuck on a certain file and in those cases it might be useful to manually download the file, either with your browser or with a download accellerator (such as Free Download Manager).

To download a file manually outside of the EverQuest II patcher you need the full URL of the file.

This is quite easy to find inside the update XML file kept on the SoE patch server. The patcher downloads this file each time you run it to check for updates, and you can do the same with your browser:

http://laeaberrpatch.everquest2.com:7010/patch/eq2/en/eq2-update.xml.gz

The file is compressed, on Windows you can use 7-Zip or WinRAR to open it. If you have a Linux box nearby, just use gzip.

You can open the XML file in any text editor to view its contents to locate the specific file you are interested in. Then follow the directory tree up to the top to find out what the full URL would be.

If you have a Linux system, you can also pass the XML file through xmllint to make it prettier:

xmllint --format eq2-update.xml > formatted.xml

If you open this XML in Internet Explorer you will get a nice tree view of the XML

Here is an example.

Say I want to download the file “Char_monsters.vpk”

I do a search in the file and find:

<File LocalName="Char_monsters.vpk" Compressed="false"
      TimeStamp="2009: 1:23:11:53:42" Name="Char_monsters.vpk"
      TotalSize="47130857" DownloadSize="47130857" CRC="1978781209 ">

I now go up the XML structure and find that this File tag was inside a Directory tag:

<Directory Name="paks">

So I know the file ends with paks/Char_monsters.vpk

If you go up further in the XML you find that this Directory tag is inside another Directory tag:

<Directory LocalPath="::HomeDirectory::" Name="."
           RemotePath="patch/eq2/en/patch0/main">

And that tells me where the file is on the server. The full path is now:

patch/eq2/en/patch0/main/paks/Char_monsters.vpk

Go up even further in the XML (now we are at the top) we find:

<Product Name="EverQuest 2" Server="laeaberrpatch.everquest2.com"
          Port="7010" AccessPath="patch/eq2/en/patch0"
          GzPath="/m2/http-docs/patch/stage/eq2/en" Version="568">

This tells us the server details, which we can now use to build the full URL:

http://laeaberrpatch.everquest2.com:7010/patch/eq2/en/patch0/main/paks/Char_monsters.vpk

This URL you can open in your browser to download the file manually.

Also note that some files are compressed on the server. If you find a file that ends in a .gz extension you need to use one of the compression utilities above to decompress it first before placing it in your EverQuest II directory.

Converting from Drupal 5.5 to WordPress 2.7.1

I finally decided to go ahead with the conversion from Drupal to WordPress. I don’ thave anything against Drupal, it just didn’t work for me.

Luckily for me my site wasn’t that big so I could do things like categories, tags and slugs manually. All I wanted to copy across were the actual articles and the comments.

I used parts of the SQL script that Dave Dash, D’Arcy Norman and Mike Smullin worked on and  made a few changes to suit my needs.

To convert the posts had to modify the script a bit because all my nodes in Drupal were of type story, not pages or posts, so I simply removed the where to clause.

# Use the Drupal database
use andremiller_drupal;
# Remove all current posts from WordPress
TRUNCATE TABLE andremiller_wordpress.wp_posts;
# Insert posts from Drupal into WordPress
INSERT INTO andremiller_wordpress.wp_posts (id, post_date, post_content, post_title, post_excerpt, post_name, post_modified, post_type, `post_status`)
SELECT DISTINCT
n.nid `id`,
FROM_UNIXTIME(n.created) `post_date`,
r.body `post_content`,
n.title `post_title`,
r.teaser `post_excerpt`,
IF(SUBSTR(a.dst, 11, 1) = '/', SUBSTR(a.dst, 12), a.dst) `post_name`,
FROM_UNIXTIME(n.changed) `post_modified`,
'post' `post_type`,
IF(n.status = 1, 'publish', 'private') `post_status`
FROM node n
INNER JOIN node_revisions r
USING(vid)
LEFT OUTER JOIN url_alias a
ON a.src = CONCAT('node/', n.nid)
;

That takes care of the posts. Comments are next.

On my drupal site the email address was not compulsory, so some comments did not have this field filled in. I made a small change here to generate a fake email if none was provided by concatenating the name and hostname of the poster together.

The threading option also didn’t work for me, but wasn’t too important, so I just made that a ‘0’ to indicate no threading. In Drupal I was not using the approval feature, so the status of all my comments was set to ‘0’. In WordPress that meant they all came in as unapproved. I updated the SQL so all the comments would be imported as approved.

 TRUNCATE TABLE andremiller_wordpress.wp_comments;
INSERT INTO andremiller_wordpress.wp_comments (comment_post_ID, comment_date, comment_date_gmt, comment_content, comment_parent, comment_author, comment_author_email, comment_author_url, comment_author_ip, comment_approved)
SELECT
nid,
FROM_UNIXTIME(timestamp),
FROM_UNIXTIME(timestamp),
comment,
0,
name,
IF(mail='', CONCAT(REPLACE(LOWER(name),' ', ''), '@', hostname, '.localhost'), mail),
homepage,
hostname,
1
FROM comments;
# update comments count on wp_posts table
UPDATE andremiller_wordpress.wp_posts SET comment_count = (SELECT COUNT(comment_post_id) FROM andremiller_wordpress.wp_comments WHERE andremiller_wordpress.wp_posts.id = andremiller_wordpress.wp_comments.comment_post_id);

In Drupal my urls of the posts where /content/slug_name, and I wanted to keep those the same so any links from outside would stay the same.

To do this I updated the WordPress Permalink settings:

Custom Structure : /content/%postname%

And then made sure the slugname of each post matched the url alias of the article in Drupal.

However, this change made the tags and category urls end up to be /content/tag/<tagname> which I didn’t want. Changing them to the following fixed that up:

Category base: category
Tag base: tag

Now an article can be reached by /content/slug_name, and list of articles with a particle tag by /tag/tag_name.

So far, everything seems to be working just fine.

Enabling telnet on Netgear EVA8000 Digital Entertainer

The Netgear EVA8000 Digital Entertainer has a telnet daemon installed, but by default it is disabled. During bootup the startup script for the telnet daemon examines the contents of /etc/utelnetd.conf and starts the telnet daemon if the contents is set to “ENABLE 1”

Once telnet is enabled you can log into the system with the username “EVA8000” and the password “Netgear”.

There are two ways to do this depending on which firmware version you are running.

If you’re still using the 1.2.x series of firmware you can use the built-in menu options that allows saving and restoring settings to modify this file.

The steps to accomplish this is documented by flusk on http://mpcclub.com/modules.php?name=Forums&file=viewtopic&t=13503

  1. Create a trial settings backup, using the Supervisor->Advanced Setup->Backup settings option
  2. Confirm that a file was created in the location you’ve set for the EVA to store its library on. The file will be in the backup directory
  3. Create a new file, called utelnetd.conf, in a directory called etc in a temporary location
  4. Change the contents of this file to contain a single line “ENABLE 1” (without the quotes)
  5. Create a tar archive of this file including the etc directory
  6. Copy the tar file to the EVA Backup directory
  7. On the EVA8000, restore the settings from this file.
  8. Once the EVA has restarted telnet will be enabled and you can log in with username “EVA8000” password “Netgear”

If, however, you are using one of the later beta firmware versions then the Backup settings option has been removed and you can no longer use this method to enable telnet.

To enable telnet on the newer firmware versions involve editing the firmware image to make the same change as above, changing ENABLE 0 to ENABLE 1 in the utelnetd.conf file.

I posted this same method on the Netgear Beta forums and it has been confirmed to work by others. (http://forum1.netgear.com/showthread.php?t=20991&page=2&p=95783)

  1. Split the FW image into three parts. A 32 byte md5 header, the bootloader+kernel and finally the jffs2 image. If you’re using the same FW as me (V2.1.16IS.IMG):
  2. FW=EVA8000_V2.1.16IS.IMG
    dd if=$FW of=crc bs=1 count=32
    dd if=$FW of=bootkernel bs=1 skip=32 count=$((0x210000))
    dd if=$FW of=jffsroot bs=1 skip=$((0x210020))
  3. Edit the jffs2 image with bvi and change the ‘ENABLE 0’ to ‘ENABLE 1’ inside the inode for dirent utelnetd.conf (Near offset 0x5cfd8 of the jffs2 image).
  4. Re-run jffs2dump -c on the new image. This will complain and say that the block now has an invalid CRC.
  5. Luckily it also prints out what the expected CRC should be.. so, make a note of this and update the crc (77 C7 E9 3E should be changed to 36 F6 F2 27 at offset 0x5CFD0 in the jffs2 image).
  6. Re-run jffs2dump again to make sure its consistent
  7. Combine the extracted bootloader+kernel with the new image and calculate the new md5sum:
  8. cat bootkernel jffsroot_mod > tempimage
    md5sum tempimage
  9. Create a new file with the ASCII portion of the md5sum in it, making sure it is exactly 32 bytes long (no newline)
  10. Stitch everything back together:
  11. cat newcrc bootkernel jffsroot_mod > telnet_enabled.img

Note that some of these steps can be skipped because the CRC of the JFFS2 inode will usually be the same across image versions because the contents doesn’t change. The steps are only provided for completeness. In reality all you would have to do is edit the original image, change ENABLE 0 to ENABLE 1, change the JFFS2 CRC (which will be the same values as above), strip off the first 32 bytes of the new images, re-calculate the md5sum and insert it at the start of the file.

Andre Miller's technology and projects blog