Posts Tagged ‘mysql’

PHP: script using php and gd to generate or create a histogram

Saturday, December 26th, 2009

Here is a simple script to create a histogram using mysql, php and gd. The key point to remember here is to create a “SELECT” only account for extracting the records. Do not provide any more privileges than you have to provide. Please comment if you have a different way to perform the same task.

Here is the final image followed by the code:
Histogram

Here are the contents of the pbimages_histogram.php file

#Let us get our database from a mysql database
#First we need to connect to the database or die trying
# I would create an account with read-only or select only access
$lottery_con = mysql_connect('localhost','select_lottery','1234try');
if (!$lottery_con){
	 die ('Could not connect to lottery');
   }
mysql_select_db("lottery") or die (mysql_error());

#Let us create our first query
$wb1_match_pb = mysql_query("SELECT count(wb1) FROM lottery
 where wb1 = pb");
$wb2_match_pb = mysql_query("SELECT count(wb2) FROM lottery
 where wb2 = pb");
$wb3_match_pb = mysql_query("SELECT count(wb3) FROM lottery
 where wb3 = pb");
$wb4_match_pb = mysql_query("SELECT count(wb4) FROM lottery
 where wb4 = pb");
$wb5_match_pb = mysql_query("SELECT count(wb5) FROM lottery
 where wb5 = pb");
$tot_record = mysql_query("SELECT count(pb) FROM lottery");
#As soon as you connect make sure you create the close
 connection
$cntwb1 = mysql_fetch_array($wb1_match_pb);
$cntwb2 = mysql_fetch_array($wb2_match_pb);
$cntwb3 = mysql_fetch_array($wb3_match_pb);
$cntwb4 = mysql_fetch_array($wb4_match_pb);
$cntwb5 = mysql_fetch_array($wb5_match_pb);
$cntwbT = $cntwb1[0] + $cntwb2[0] + $cntwb3[0] + $cntwb4[0]
 + $cntwb5[0];
$cnttr = mysql_fetch_array($tot_record);
mysql_close($lottery_con);
#Let us create the histogram
$pb_histogram_image = imagecreate(250,215)
      or die('Cannot Initialize new GD image stream');
$pb_text = imagecolorallocate($pb_histogram_image,0,0,0);
$blue = imagecolorallocate($pb_histogram_image, 0, 0, 255);
$black = imagecolorallocate($pb_histogram_image,0,0,0);
$red = imagecolorallocate($pb_histogram_image, 255, 0, 0);
$green = imagecolorallocate($pb_histogram_image,0,255,0);
$gray = imagecolorallocate($pb_histogram_image,150,150,150);
$orange = imagecolorallocate($pb_histogram_image,255,127,0);
$coral = imagecolorallocate($pb_histogram_image,240,128,128);
$white = imagecolorallocate($pb_histogram_image, 255, 255, 255);
imagefilltoborder($pb_histogram_image,0,0,$black,$white);
imageline($pb_histogram_image,0,0,0,250,$black);
imageline($pb_histogram_image,0,0,250,0,$black);
imageline($pb_histogram_image,249,0,249,213,$black);
imageline($pb_histogram_image,0,214,250,214,$black);
#Let us add the counts
imagestring($pb_histogram_image,2,15,200,"wb1",$black);
imagestring($pb_histogram_image,2,60,200,"wb2",$black);
imagestring($pb_histogram_image,2,95,200 ,"wb3",$black);
imagestring($pb_histogram_image,2,140,200,"wb4",$black);
imagestring($pb_histogram_image,2,180,200 ,"wb5",$black);
imagestring($pb_histogram_image,2,213,200,"wb Tot",$black);
imagestring($pb_histogram_image,3,10,20,"    Histogram of
 white balls",$black);
imagestring($pb_histogram_image,3,10,35,"       matching
powerball",$black);
imagestring($pb_histogram_image,3,10,50,"         Records:
 " . $cnttr[0],$black);
imagestring($pb_histogram_image,2,15,200 - $cntwb1[0] - 20,
$cntwb1[0],$black);
imagestring($pb_histogram_image,2,60,200 - $cntwb2[0] - 20,
$cntwb2[0],$black);
imagestring($pb_histogram_image,2,95,200 - $cntwb3[0] - 20,
$cntwb3[0],$black);
imagestring($pb_histogram_image,2,140,200 - $cntwb4[0] - 20,
$cntwb4[0],$black);
imagestring($pb_histogram_image,2,180,200 - $cntwb5[0] - 20,
$cntwb5[0],$black);
imagestring($pb_histogram_image,2,220,200 - $cntwbT - 20,
$cntwbT,$black);
#Let us make the bars
imagefilledrectangle($pb_histogram_image,5,200,40,200 -
$cntwb1[0],$blue);
imagefilledrectangle($pb_histogram_image,45,200,80,200 -
$cntwb2[0],$red);
imagefilledrectangle($pb_histogram_image,85,200,120,200 -
$cntwb3[0],$green);
imagefilledrectangle($pb_histogram_image,125,200,155,200 -
$cntwb4[0],$gray);
imagefilledrectangle($pb_histogram_image,165,200,205,200 -
$cntwb5[0],$orange);
imagefilledrectangle($pb_histogram_image,210,200,245,200 -
$cntwbT,$coral);

header ('Content-type: image/png');
imagepng($pb_histogram_image);
imagedestroy($pb_histogram_image);

Let me know what you think and as always please check out the offerings of my sponsors.

Nagios: ndoutils giving problem with remote database connection

Thursday, December 3rd, 2009

I am in the middle of building a green Nagios environment. That means I am deploying virtual servers to do the work. That platform is SLES 10 SP3 x64 on an IBM x3550, set for full virtulization in the BIOS. Our environment is so large, 2000+ hosts and 3000+ services that we broke Nagios into two sections. The two sections are: retail and corporate.

Breaking the areas down required two virtual hosts. A third virtual host was introduced to run the databases. When I said databases, I created two nagios databases to separate the data from both areas. The problem I encounted was with ndoutils. It just loved not to work properly at first, then I took some steps and had the data writing to a remote database.

First I compiled ndoutils-1.4b7. I gave the following arguments:

sudo ./configure \
--prefix=/opt/nagios \
--with-mysql-lib=/usr/lib64/mysql \
--with-mysql-inc=/usr/include/mysql \ #You will have to install mysql-devel

After that finished I performed a sudo make, then copied the files to their respective directories as indicated in the README file.

ndo2db.cfg

  1. I created the var directory under my Nagios install and changed ownership to nagios:nagcmd.
  2. I changed the paths for the various sockets to my Nagios installation path.
  3. Changed the db_name to whatever the database is called.
  4. Changed the db_host to the FQDN of the virtual database server.
  5. Set the db_user and db_password up for the nagios user.
  6. Saved my changes and closed the file.

ndomod.cfg

  1. I just changed the path of the files to my nagios installation path.
  2. Saved the changes and closed the file.

nagios.cfg

  1. Since I am using Nagios 3, all I had to do was uncomment the broker agent.
  2. I saved the changes and closed the file.

Here is where the fun comes in to play. I was experiencing a problem with ndomod being unable to establish a data sync. So I went to the database host and performed the following functions:

  1. I untarred the ndoutils package on the database server.
  2. I logged into mysql and created the databases.
  3. I modified the mysql.sql file in the ndoutils package to reflect the database name.
  4. Then I ran sudo mysql < mysql.sql. That created the tables within each database.
  5. I then set up the nagios account in mysql using mysql_setpermissions. I gave nagios access to all the databases from anywhere and set the password I established in the ndo2db.cfg file. That is very lax on my part, but the only one using the databases will be the nagios user.

I then started nagios and ndomod on both the virtual hosts. Then performed a netstat -a and was able to see the sockets connecting to my database server. I then performed a mysql query on the database server with a select * from nagios_objects; and saw the tables being populated on both databases.

I hope this helps someone else, because I wrapped my head around this for two days before stumbling on this solution.