Thursday, April 5, 2012

Analyze your social networks with ThinkUp

... on a server in the sky

I had two things on my agenda, test out HP Cloud and ThinkUp. HP Cloud is still in private beta, but I was lucky to get an invitation after registration. They aim to compete with Amazon Web Services (AWS) and so far I think they have a good chance. It is rather easy to just order and set up a new server in the cloud. A simple and easy interface with few buttons. It is tempting to include a few screen shots here, but I'm not sure if the agreement I just signed allows it. If they expand the interface with more features I hope they keep this simple version for impatient people who just need a server right now. I've had great fun with AWS also, but it is crowded with features and things to figure out. For my need of testing a web application the HP Cloud seems just perfect.

ThinkUp is a tool for you to analyze how you stand in your different social networks. It also has the added benefit that it actually does a backup of all your posts to your own private spot. Just go to their web site and check out the features. It does require a web server with php 5.3 and MySQL; a LAMP server in other words.

In short this is what you have to do:

  • Create a compute server with CentOS 6
  • Create the ssh-keys and test connection with ssh
  • Install packages for MySQL, Apache and PHP
  • Configure database, web server and php
  • Download ThinkUp and unpack it in the webserver's public html directory
  • Access ThinkUp and start setting up connections to your social networks
  • Enjoy the reports on your digital social life.

I created the smallest version of a compute server (standard.xsmall) at HP Cloud with CentOS 6.2 Server as image. It comes with 1GB of memory and two partitions of 10GB and 30GB disk space. When the server is ready with status 'running' CentOS is already installed with a root partition of 10GB. You don't need more to run ThinkUp, but you have another 30GB partition available if you want to format and mount. First time you need to generate a key pair to be used with SSH when you want to connect to the server. This is the same mechanism that AWS uses and works perfectly on Unix/Linux and on Windows. Once generated you store the private key in a text file, not to be lost unless you want to scratch the servers that depends on them. Since I also have a Windows laptop, I had to make a version of this key that works with Putty. The tool PuTTYgen can be downloaded from the same place as Putty To convert to a key that Putty can use you select Conversions -> Import key, select the file with the key from HP Cloud and save the converted private key in a new file (do not overwrite the original key). I used SSH-2 RSA which seems to be the default.

Before I could connect to the server I had to attach a public IP. This is of course done from the management console at
To connect with Putty I entered the assigned public IP address, loaded the key file under Connection -> SSH -> Auth. Login as root and the connection is established without a password since you are using a private ssh key.

From my MacBook I connected with:

ssh -i original_key_file ip-address

The same works on Linux or most other mature OS.

Before ThinkUp can be installed you need an http server that supports php 5.3 or higher, that is, a LAMP server. I used this guide: It contains a link to a guide on how to set your hostname and timezone. (If you change the timezone, remember to repeat it after running 'yum update', since it resets the link /etc/localtime).

Download the ThinkUp software with

cd /tmp

This downloads the file zip-file, in my case Unpack it under the public_html directory, using as hostname hereafter:

cd /srv/www/
unzip /tmp/

With web server, PHP and MySQL up you are ready to configure ThinkUp. Open up a browser and go to You probably will see this:

Just do what it says, as root:

chown -R apache /srv/www/

Then hit F5 to reload page. Next screen will look like:

Hit the link in the page to configure ThinkUp for the first time. Various requirements are checked and in my case I was missing GD and PDO; whatever that is.

Hit the links to continue (See? You get everything almost free of efforts here). OK, maybe it was not obvious what to do get GD installed (hint: click Installation on next page if you want to know how). To install GD for CentOS I did:

yum install php-gd

PDO for PHP and MySQL is installed and configured with:

yum install php-pdo
yum install php-mysql

Restart web server and MySQL after this, then reload page, the configuration continues to next page when all requirements are in place. On next page you create your ThinkUp account and configures the connection to the MySQL database. Before you can do that, create the database for ThinkUp and a user. Connect to MySQL as root:

mysql -u root -p

Create database and user with:

create database thinkup;
create user 'thinkup'@'localhost' identified by 'strongpassword' ;
grant all on thinkup.* to 'thinkup';

Then enter respective information on webpage. On the next page you will be presented by an error message saying that the apache user cannot write to As usual you are told what to do. Execute this as the root:

sudo touch /srv/www/
sudo chown apache /srv/www/

This is enough, you don't actually have to copy and paste the script presented in the window below.

Hit next and you are finished with the installation.

After you have verified your ThinkUp account (check your mail) you can login to your ThinkUp web application and start adding accounts for your social networks. Happy analyzing, remember it is about the network.

When playing with this I once again understood that Twitter is the most important social network for me, I'm following people who shares a lot of useful information, usually quick quotes and links to good stuff. If you have attention deficit, Twitter is perfect, 140 chars and then time to think. It was through a few tweets I discovered that HP had opened their beta program. In another about ThinkUp as an alternative to Klout. Facebook is less important to me and I often forget to check it, it is just a collection of faces... Google+ is used by some of my Oracle peers, but it is just much easier to check Twitter when I'm on the run. Links to good stuff is either checked out immediately or sent to Read It Later.

I've met quite a few that are frustrated by the tardiness of many IT departments, getting a new server takes too much time. Having the possibility to create one in the cloud, in minutes, from a easy to use web interface such as what HP Cloud offers; that is something many will consider.

Sunday, April 1, 2012

Extract SQL from trace file with Perl

Perl is included with the Oracle database software (SE and EE), even on Windows. I wrote this to extract the SQL statements from a trace file (generated with sql_trace=true or setting the 10046 event). A simple indentation - one tab for each level - is used to show recursive statements.

while (<>) {
($level)=$_=~/\s+dep=(\d+)\s+/ ;
while ($line!~/END OF STMT/){
for($i=0;$i<$level;$i++) {
print "\t" ;
print $line;
print "\n" ;

You usually have to expand your PATH on Windows to find Perl; you'll find Perl.exe somewhere below %ORACLE_HOME%. The code will of course work on Linux and other OS where you have Perl. Store the code above in a file called and call it with:

perl your_trace_file.trc | more

There is an option (record=filename.sql) in tkprof to extract the SQL, but it does not include recursive statements. I guess the difference is that this is something to build on when you have a more complex task.